Page 3 of 3 FirstFirst 123
Results 31 to 42 of 42

Passing dynamic name of array to a Function

  1. #31
    mcarval22 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Brazil
    Posts
    19

    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 !

  2. #32
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,240
    Quote Originally Posted by mcarval22 View Post
    Hi Bulzie, this is one of the first tests that I did, doesn't work !
    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 = 1
    A = "aL" & (i)
    x = Function2((A), (i))
    End Sub

    Public Function Function2(A, B)
    y = A
    z = B
    End Function

  3. #33
    mcarval22 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Brazil
    Posts
    19
    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

  4. #34
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    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.

  5. #35
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,240
    Quote Originally Posted by John_G View Post
    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.
    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?

  6. #36
    mcarval22 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Brazil
    Posts
    19
    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!

  7. #37
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,240
    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?

  8. #38
    mcarval22 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Brazil
    Posts
    19
    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!

  9. #39
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    MCarval22 -

    I need to pass the array name to the function
    ....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 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.

    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.)

  10. #40
    mcarval22 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Brazil
    Posts
    19
    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?

  11. #41
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    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.)

  12. #42
    mcarval22 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Brazil
    Posts
    19
    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

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA passing Array arguments to procedure
    By George in forum Access
    Replies: 2
    Last Post: 05-13-2015, 10:03 AM
  2. st deviation function on array
    By registoni in forum Programming
    Replies: 2
    Last Post: 09-09-2013, 04:00 AM
  3. Singly linked, dynamic array, or both?
    By kopbad in forum Database Design
    Replies: 9
    Last Post: 04-27-2012, 09:13 PM
  4. Replies: 3
    Last Post: 05-23-2011, 02:15 PM
  5. dynamic array for calendar
    By workindan in forum Programming
    Replies: 7
    Last Post: 11-12-2010, 01:20 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums