Thanks ssanfu, this is basic use of array, my problem is related when I don't know the name of array that I need to pass to a function, all of this examples pass a array like array, I need to passa to a function a refence address of my array !
Thanks ssanfu, this is basic use of array, my problem is related when I don't know the name of array that I need to pass to a function, all of this examples pass a array like array, I need to passa to a function a refence address of my array !
Sorry to hear, when I did the code in a test db, it did pass the value to the Function.
Try this on a button on a new form, put break point on i=1 and step through it. I get y= "aL1" and z= 1. Isn't that what you want to do?
Private Sub Button1_Click()i = 1End Sub
A = "aL" & (i)
x = Function2((A), (i))
Public Function Function2(A, B)y = AEnd Function
z = B
Thanks again Bulzie, but is not my problem, I want pass a reference of an array, not passa an array !
Take a look in your code with changes that I'm saying, the parameter was sent like a variable and not like a reference of array
Private Sub Button1_Click()
aL1 = Array("01,02,03,04,05,06,07,08,09,10")
aL2 = Array("11,12,13,14,15,16,17,18,19,20")
i = 1
A = "aL" & (i)
x = Function2((A), (i))
End sub
Public Function Function2(A, B)
Debug.Print IsArray(A)
Y = A
z = B
End Function
That's not going to work either. As soon as you put A = "aL" & (I), the variable A becomes a string variable, and it stays a string variable, brackets or not. Function2 get a string passed to it.
Access cannot take a string variable, figure out that the contents of that variable represent the name of another variable, and then reference that other variable (whether it's an array or not).
Google vba reference a variable named in a string
You'll find workarounds to what you want to do - but no solution.
Not so, the parentheses has it look at the value, not the name. As I said, I ran that code from a button on a Form and it passed the values in the variable, not the variable name.
What do you mean by want to pass Reference of an array? The Array placeholder? Thought that was what i in the example was?
I modified the function2 to show the type of the variable sent, the variable does not arrive as an array, but the value assigned to it, I need to pass the array name to the function. Remembering that we have hundreds and hundreds of array to be passed, so need to dynamically assemble the array name and pass it the reference to the function, the function that receives the array name will be tested the contents of the array!
In a different direction, let me ask again, why do you need to pass the array name and evaluate it in a separate function? Once you have your array built dynamically in the code, you can manipulate it with number sets (Do loops or For Next statements) in he same function to loop through each array? What are other examples of Arrays you would need to process?
You're right Bulzie, I might be being stubborn in this sense, but will be hundreds and hundreds of arrays, are millions of interactions and so I would a code optimized and easy to maintain, if there is no other way I'll have put everything in one function, but as develop know this is horrible!
MCarval22 -
I need to pass the array name to the functionI keep telling you - Access cannot do that. It cannot reference a variable (an array is a variable) when the name of the variable is in a string.....so need to dynamically assemble the array name and pass it the reference to the function, the function that receives the array name will be tested the contents of the array!
I suggest you do an internet search to find a workaround (many that I found involve the use of 2-D arrays with "indexes" you have to manage.)
Thanks Jhon, you were the first to understand what I need, I am very grateful for your support.
When you talk about using two-dimensional array, you are suggesting to use the name of the array as an index of the first dimension and then access the second dimension?
I would make one 2-D array to hold all the data, with each column in that array representing one of the arrays you originally mentioned. For the dimensions of the data array, let the number of 1-D arrays you have be J, and the maximum size amongst those arrays be I. Your data array will be (I, J).
You could create an index to the data with a little 3-field Access table:
ArrayName e.g. "AL1"
Column_Number
Array_Size
(You could have other fields in there too describing the data if you felt ambitious!)
Then, to retrieve the data for an array "name", you would get the column number and number of members from the index table, copy that column into a working 1-D array, and there you are.
A couple of questions that might lead to some other suggestions:
- how do you populate those hundreds of arrays, and how often? Do you have to regenerate it each time you run the system?
- how many data items do those arrays have? You initially showed 10, but was that just an example?
If all or some of the data is static, you might be able to keep it in an Access table, while reducing the number of table reads. (The split function gave me some ideas.)
My code will be used to generate lottery cards, this lottery has 57 million possible combinations, while the generation of numbers the cards this will be filtered and the filters are in arrays, for example, this lottery has had 1800 raffles and these 1800 cards will go to array.
I think I'll surrender me to put all the code into a single function, it is not what I want, but I believe it is possible for the VBA