Passing Array Losing Items: Lost 'control'
Mar 9, 2007
Passing an array from a sub to another sometimes works, sometimes not. But when I call the same sub twice, it definately gets stuck at least at the second time. Seems as if I tend to lose some of my items of the array. And I cannot figure out why it happens, at what pace it happens or how I could stop this from happening.
1.Could it be that a control perishes as soon as it is set to 'ctrl.enabled=false'? 2.Or could it be that a declared array 'ary=array(tb1,cb1,frm1)' can not be handled without having had its items initialized with some lines of code, although the items visibly exist within the user form? 3. Can it be that a Sub 'remembers' the former values passed to it, if set to 'optional'?
However, I have added a cleared/minimized version of my userform as a file to this post, hoping that some of you out there might find the time to have a look at it.... I would really appreciate it. I have added the decisive parts of the macro below here, as well,
Private Sub UserForm_Initialize()
Dim elem As Variant
Dim wSht As Worksheet
Dim ctrl As Control
'set control arrays
'[...]
ctrlAllStep2 = Array(cbTimeOnly, cbIntensityOnly, cbAreaOnly) '[...]
ctrlAllStep3 = Array(tbIntensityTotal, tbIntensityBleached) '[...]
MsgBox "Two Controls exist now: 1." & ctrlAllStep2(0). name & ", 2. " & ctrlAllStep2(1).name, vbInformation, "Note: controls exist..."
'disable Controls in Step2 and in Step3....................
View 5 Replies
ADVERTISEMENT
Jan 4, 2007
I'm using a VBA UserForm (ShowModal=False) as the front-end on a spreadsheet for logging purposes. Here's the progression of the problem:
1. I have the UserForm loaded and the cursor is in any given textbox/ combobox on the form.
2. I switch to another program, then come back to the UserForm.
3. The cursor no longer appears in whatever textbox/combobox I was in when I left the UserForm. If I type, nothing happens (I have to click the field again first, then type).
However, if I TAB, it will go to the next field in the Tab Order just fine. It seems that the control has "pseudo-focus"--it knows which field to tab to next, but the control won't accept input unless you click it. The odd thing is--this UserForm has a button which launches another "child" UserForm. That "child" UserForm does not have this problem.
View 3 Replies
View Related
Aug 5, 2014
I want to pass an array to Offset in the "Height" parameter, without having to type the array.
{=MAX(SUBTOTAL(9,(OFFSET(A1,ROW(1:5),,{1,2,3,4,5}))))}
I can't seem to figure out how to build the {1,2,3,4,5}. I've tried another ROW(1:5) and have tried nesting that like N(ROW(1:5)) but nothing works.
How I can get the {1,2,3,4,5} without having to type it out (so that I can expand this to a larger list)??
View 8 Replies
View Related
Jul 16, 2009
I am thinking of creating a "summery" macro that would run at the end of my workbook and would show all the userforms needed(up to 10-20 forms) at the same time one on top of another kind of staggered.(like windows would do). However I realized that when I code userform.show then the "summery" macro would pass control to the form that was just shown, and wait for it to be unloaded before progressing to the next line of code. for example;
View 4 Replies
View Related
Jul 19, 2009
Passing Array to VBA. I have the following
View 2 Replies
View Related
Feb 17, 2009
I'm trying to pass an array of UDT from Excel to a C++ dll but I have no idea how. My UDT looks like this
Private Type Student
Age As Integer
Name As String
StudentID As String
End Type
And my dll function declaration..
Declare Function RegisterStudent Lib "SimpleDLL.dll" _
(arr() as Student) As String
.. which will return a unique Id in string
View 9 Replies
View Related
Mar 31, 2007
I'm trying to pass an array from one userform to another. When I run the script, it says: "Constants, fixed-length strings, arrays, user-defined types, and Declare statements not allowed as Public members of an object module". It explains further that you cant make an array public. How can I pass my array between forms? It suggests using an varient that contains an array, I tried doing this, heres my
Dim multiAry(15) As Range
Public vmultiAry As Variant
'... and later:
vmultiAry = multiAry
'... and on the next userform:
Dim multiAry(15) As Range
multiAry = UserForm1.vmultiAry
With this, I get an error saying "Compiler error: Cant assign to array" with "multiAry =" selected. Am I doing it totally wrong? How can I pass an array between forms?
View 5 Replies
View Related
Jul 22, 2014
Okay, I made an epic fail on a previous post that i turned to Solved, a real error on my part of not fully testing before i posted Solved, basically i was so excited that i'd solved it myself, well partially.
I have the following code in the first sub routine that collects which option button has been selected.
[Code] .....
The second sub routine is per following.
[Code] .......
Unfortunately when i tested this with only one variable myRev(1) it worked fine but when i added more myRev variables 2 thru 4 if failed, so is there a trick to passing an array variable between sub routines?
View 2 Replies
View Related
Sep 15, 2012
What I am trying to do is to automatically build a "tree" diagram representing the links in a huge model which is dynamically configured. What I have a problem with is the following:
The tree consists of layers, I start off with the top layer and for each entry in the top layer I can add all its subsidiary layers and draw links between them, this uses a function which takes as its arguments the node name and its layer number (how far down the tree it is) and the number of items in that layer so far.
So I start at the first item in Layer 1 and there are as yet no layers below it. I start at the first one and add the first item in layer 2 then I kick the function off again and that adds the first item connected to item 1 in layer 2 in the layer below (3) starting at the first one, and so on. When I reach the bottom I go up one layer and add the second item in the bottom layer and so on. When I have added all the connctions to the first item in the next to bottom layer I go up one layer and add the second item connected to the first item in that layer and then add all the items connected to it and so on and so on.
In this way I build up the network exhaustively (to make things more complex more than one item in a layer may connect to the same item in the layer below).
I can do almost all that, the issue I am struggling with is I need to keep track of how many items there are in each layer (as some layer 1 items connect to 1, 2, 3 ...8 layer 2 items and so on). my idea is to keep a running total of these in an array LevelCount(1), LevelCount(2) etc. so when I add a new item to a layer I know where to put it. However I cant workout how to do this final step.
Currently I have a function that draws the nodes below a specified node this and takes the correct value from the array LevelCount(n) by passing Levelcount(n) (where n represents how far down the tree you are) into the function and the function then updates the value of LevelCount(n) (ByRef) so that next time I use it it is correct. That is fine but what I want to do is to is to call the same function from within itself when it adds each node which would make it work automatically - it would keep calling instances of the function until it reached bottom and then go back one step at a time to the top but I cant work out how to reference the right value in the array to pass into the second (and subsequent) instances of the function.
I don't think I can simply pass (n) into the function and in the body of the function set LevelCount(n) = LevelCount(n)+1
I also dont know ahead of time how many layers the model will have, nor can I tell which layer a node sits in as it depends on the links that are dynamically configured.
Beyond this a node can also be subsidiary to nodes in more than one level so it needs to sit at the lowest level - but I suspect if I can work out how to do the first bit i can do this too.
View 7 Replies
View Related
Jul 15, 2014
I'm trying to pass a multidimensional array to a function that I have defined, but I receive an error about an object mismatch. Here is what I have in regards to the array and function. What should I change?
Code:
Dim diffArray() As Integer
Dim countArray() As Integer
Redim countArray(count,2)
diffArray = getRunningSum(countArray)
End Sub
Public Function getRunningSum(ByRef countArray() As Integer) As Integer()
'Code here......
End Function
View 9 Replies
View Related
Mar 7, 2007
I have been struggling with a problem passing a small array to be used in a form and I hope that somebody can help me. I cannot get the array into the form.
In the main module code I have an array containing information on a book reference:
Private Function PresentForm1(ByRef iID() As String, sBookRef() As String) As Boolean
Dim iCounter As Integer
For iCounter = LBound(iID) To UBound(iID)
Form1.ListBox1.AddItem iID(iCounter)
Next
Form1.ListBox1.Selected(0) = True
For iCounter = 0 To 2
MsgBox sBookRef(iCounter) ' This bit works fine
Next iCounter
Form1.Show
End Function ...
View 7 Replies
View Related
Jul 9, 2009
Passing an entire dimension of an array to a function....
View 13 Replies
View Related
Jul 25, 2006
code to pass a range say (A1:A3) as an arguement among other arguements to a custom function and then reading this as an array inside the custom funtion
View 4 Replies
View Related
Aug 30, 2006
I have a database of data that looks like this
Centre Name Training Type Expert 1 Expert 2 Expert 3 Expert 4
MyCentre MyTraining Me
MyCentre MyTraining Me
MyCentre MyTraining Me
MyCentre OtherTraining Me
NewCentre NewTraining NewPerson
I am trying to create a user form (in VBA) with a combo box that has each centre name appear only once (despite the fact that in the database each occurs multiple times). When a specific centre is selected, this brings up all the different training types associated with that centre in a separate list box. When that training type is selected, it should bring up all of the experts in that training type (for the specific centre). I should mention that the range is static (though the data is always contiguous) as the database is updated on a going forward basis.
View 2 Replies
View Related
Aug 29, 2006
I have an issue with some unwanted items in a listbox that gets its data from a pivot table, however it only happens with the one pivot field and for three of its pivot items.
I tried following a suggestion from an earlier thread to re-create the pivot table, but it did not work.
I'd like to post the file, but its too large, even in zip format.
View 9 Replies
View Related
Jun 16, 2014
I have 2 dropdown lists.
The 1st shows the portfolio list and the 2nd one should display project names based on the portfolio selection in Dropdown1.
My data resides in another sheet where Column B is the Portfolio list,Column C project list and Column D to X some data related to the project.
I have to give cell link reference to the project selected in dropdown 2 so that the other values in the dashboard changes based on a vlookup formula.
I have attached the sample sheet for reference with some dump values.
Dashboard sample.xlsx
View 3 Replies
View Related
Aug 15, 2006
I have a form that launches when a user wants to confirm an order from a customer. It loads a drop-down combo box with our current customer list. When the user selects a customer, a checkbox array is loaded with the items that a customer has ordered. The checkboxes confirm (or un-confirm) that the order has been processed. Everything works fine up to this point. Let's say the user selects a customer, and this customer has 7 orders. 7 checkboxes appear on the form, either checked or unchecked.
The problem is:
when the user selects another customer from the drop down that only has 3 orders. The 3 checkboxes appear just fine, however, the balance of the previous customer (orders 4-7) also still show on the form. How do I remove the previous checkboxes from the form and prepare for a new recordset (or new checkbox array). I have tried to run backwards through the code that creates the array using Me.Controls.Remove instead of .Add, and this is just not working.
View 9 Replies
View Related
Jul 18, 2008
I need to add items to an array and used code out of the web to create some kind of an array list.
View 12 Replies
View Related
Nov 11, 2013
I am setting an array with
Code:
Set my_array = Sheets(1).ListObjects(1).DataBodyRange
Column number 2 contains numbers, but I want to 'reset' all the numbers in the second column. How can I do that? I know I can do a loop through all the elements, but I thought it would be smarter if I could set the entire column as zero in the array?
View 6 Replies
View Related
Jan 23, 2009
I have two large spreadsheets that are arrays. Column A contains 40 items in both files. Columns B through X contain unique names. The arrays are filled with dollar amounts. Is there some formula or function in Excel whereby I can easily combine both spreadsheets, essentially adding the dollar amounts for each corresponding cell?
View 9 Replies
View Related
Aug 15, 2006
I would like to sort the items in an array.
View 2 Replies
View Related
Aug 22, 2014
I need to make a vlookup which returns values for many 1000 material numbers. The values is located several other files. My problem is that i dont want to update all vlookups every time the other files (with my values) are getting updated.
e.g.
i want to have en cell A1: FileNameVersion1 and when someone updates the file the only thing i have to do is to change the name in cell A1 to FileNameVersion2.
View 3 Replies
View Related
Nov 9, 2009
I have an array MyArr() in excel.
Its length is from 1 to i where i takes dynamic value from varibable.
Now the problem is MyArr(1 to i) has some empty values.
Like say: if i = 5 then
MyArr(1) = "a"
MyArr(2) = ""
MyArr(3) = "b"
MyArr(4) = ""
MyArr(5) = "c"
How can I get rid of those empty elements so that MyArr() becomes only three elements long and then display them in a range of three cells.
View 9 Replies
View Related
Mar 2, 2014
I've created a range B3:B12 in which each cell returns TRUE of FALSE. There corresponding values are listed in C3:C12. I have the same thing again in D3:D12 and E3:E12. Checkboxes link to the TRUE/FALSE cells. Attached is a stripped down file to show this.
I want to create a formula that lists each item in the one cell (no spaces required inbetween) for use with a search. I know a long winded way to do this involving many IFs but any array will be much more tidier, thing is I hardly use arrays and have struggled with this one.
I'm thinking it will be along the lines of =IF(B3:B12="TRUE",C3:C12,"")&IF(D3:D12="TRUE",E3:E12,"") but exactly what it should be is beyond me.
EDIT: The original file attached was corrupt for some reason, the new attachedment is the same except instead of TRUE/FALSE cells I've set them to 'x' for quickness (lost my work...!).
View 3 Replies
View Related
Mar 16, 2014
I am trying to take selected items from a listbox and put the selected items into an array. Basically I am trying to put the selected items into a variable that I can pass to other modules. I am close but something is off. I can't tell if it's the variable declaration or the code.
I get Run Time error 91. Object variable or with block variable not set
Here's what I have so far...
[Code] ......
View 2 Replies
View Related
Nov 9, 2009
I'm stuck with an array problem in excel. I have an array MyArr() in excel.
Its length is from 1 to i where i takes dynamic value from varibable.
Now the problem is MyArr(1 to i) has some empty values.
Like say: if i = 5 then
MyArr(1) = "a"
MyArr(2) = ""
MyArr(3) = "b"
MyArr(4) = ""
MyArr(5) = "c"
How can I get rid of those empty elements so that MyArr() becomes only three elements long and then display them in a range of three cells.
View 9 Replies
View Related
Feb 11, 2013
I need to query table contents where some general items are met, and item id's are not contained in an array.
Example:
Code:
SELECT LOOKUP.*, LOOKUP.Var FROM LOOKUP WHERE ((LOOKUP.Name)="test" Or (LOOKUP.Name)="test2" AND ((LOOKUP.Type)=3) AND ((LOOKUP.Var) ("array", "of", "items")))
I need to know how to add an array to a query since I can't hard-code the list each time, and the array can be a different size each time, so using array variables doesn't make sense here.
View 9 Replies
View Related
Jun 6, 2013
I have data that is formatted in an Excel 2010 table. The two columns in question are [Invoice #] and [Description]. In the description column I have descriptions of products as well as freight. The same invoice number would be tied to the product description as well as its associated freight. I need to filter out certain product types and their associated freight items.
The macro I wrote creates an array of invoice numbers that I want to filter out and leave the remaining invoices, but I can't get the filtering part to work. Here is my code:
Dim Invoices() As Variant 'array of invoice numbers
Dim Descriptions() As Variant 'array of Descriptions
Dim InvoiceFilter() As Variant 'array of invoice numbers to filter
Dim i As Integer 'counter
Dim j As Integer
j = 1
[Code]...
What happens now is that it filters out all values in the Invoice column instead of only the values in the InvoiceFilter array.
View 1 Replies
View Related
May 28, 2007
I have a col of dates that change, 9/15, 10/15, 11/05 and reside in col. I
I then have a corresonding cell in row I136, M136, Q136, U136, Y136 and AC136.
I want to find the starting at the earliest date starting in I36 , M136, Q136...
So I136 would be updated to 9/15, M136 = 10/15, Q136 = 11/05, ...
I am thinking a CSE type formula would be a possibility, but need assistance in this or in a piece of code..
View 9 Replies
View Related
Oct 2, 2012
On this is a column of Categories and a Column of sizes. I want to use these in a user form. The user will select their Category from a drop down list and the second drop down list will include only the sizes that appear next to the chosen category So for example in the attachment if the user chose 'AUD' as a category they would get the size choices of, '2x4 insert', '2x4 replica' and 'A4L' in the other drop down menu.
The master list of sizes will have to remain on a worksheet as this is what other operators will amend from time to time.
View 1 Replies
View Related