Page 1 of 3 123 LastLast
Results 1 to 15 of 42
  1. #1
    mcarval22 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Brazil
    Posts
    19

    Passing dynamic name of array to a Function

    Hi team, I need a VBA support in MS Access 2013.
    I need to pass the name of an array to a function, the problem is that the array name is dynamically created and I'm not getting through, the function takes the parameter as a string and not as an array.
    The following example:



    Public aL1, aL2, aL3, aL4, aL5 as variant






    Function f_test()


    aL1 = split("01,02,03,04,05,06,07,08,09,10",",")
    aL2 = split("11,12,13,14,15,16,17,18,19,20",",")
    aL3 = split("21,22,23,24,25,26,27,28,29,30",",")
    aL4 = split("31,32,33,34,35,36,37,38,39,40",",")
    aL5 = split("41,42,43,44,45,46,47,48,49,50",",")




    for i=1 to 5
    call f_verify_value_in_Array("aL" & i, i)
    next


    End Function


    Function f_verify_value_in_Array(pArray, pNumber)

    f_verify_value_in_Array = False

    If (UBound(Filter(pArray, pNumber)) > -1) Then
    f_verify_value_in_Array = True
    End If


    End Function

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Access doesn't need arrays from the 1970s.
    We use queries,dynasets,and collections.

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hey, Ranman - don't be so quick to dismiss the use of arrays. They still have important applications in programming - I have used them extensively in VBA. For some mathematical applications they are essential, and careful use of arrays can decrease execution time enormously. Just sayin'.....

    Mcarval22 - I don't think your approach can work. When you pass a parameter to a function, what Access does is pass the memory location of that parameter, not the name of the parameter. When you put the name of the array into a character string, Access passes to location od that string in memory, which has nothing to do where the actual array is in memory.

    There may be ways of doing what you want with some of the API functions, but I'll leave that to the real experts.

  4. #4
    mcarval22 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Brazil
    Posts
    19
    Thanks John_G and ranman256, I'm using array exactly because of performance, I'm working with a program to generate lotery numbers !
    I will wait some one help me.
    Thanks.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    "the function takes the parameter as a string"
    Not 100% positive it matters, but your function accepts the parameter as a variant because you have not declared the variable to be a string. I prefer to declare the variable type being passed to a function whenever possible:
    Function f_verify_value_in_Array(pArray as String, pNumber As Long)
    This may have no bearing on your outcome, but it's worth a try I think. Also, if you concatenate numbers and text without declaring the variable type (for the second parameter) it's likely text. I have no way of knowing if you expect to pass a number or not (your variable name suggests this) but I believe it is text. If so, I'd definitely try concatenating the parts in a separate variable, and if pNumber is text, convert it to a string, then pass that variable rather than try to concatenate the parameter in the function parameters. I'd try this even if pNumber is text. Another thought is to pass the array itself.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    mcarval22 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Brazil
    Posts
    19
    Hi Micron, thanks for your help.
    The problem is exactly it, I need to pass the array name to function f_verify_value_in_Array(), but I have a lot of arrays that a need to pass, I need to mount dynamically the name of this arrays to pass for the function, take a look it this piece of code, in red color is the name of arrays that I need mount dynamically.
    for i=1 to 5
    call f_verify_value_in_Array("aL" & i, i)
    next

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Give this a try.

    Code:
    dim j as string
    for i=1 to 5
    j = "aL" & i
    call f_verify_value_in_Array(j, i)
    next

  8. #8
    mcarval22 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Brazil
    Posts
    19
    Thanks Davegri, but using this sintaxe the function receive a string with value aL1 and I need a reference to array aL1.
    John_G told correctly, I need a reference of memory of this arrays !

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    OK, then. Let's not pass the array. Pass the string and create the array from it in verify function.

    Code:
    Function f_test()
    
    
    aL1 = "01,02,03,04,05,06,07,08,09,10"
    aL2 = "11,12,13,14,15,16,17,18,19,20"
    aL3 = "21,22,23,24,25,26,27,28,29,30"
    aL4 = "31,32,33,34,35,36,37,38,39,40"
    aL5 = "41,42,43,44,45,46,47,48,49,50"
    Dim i As Long
    
    
    For i = 1 To 5
    Select Case i
        Case 1
            Call f_verify_value_in_Array(aL1, i)
        Case 2
            Call f_verify_value_in_Array(aL2, i)
        Case 3
            Call f_verify_value_in_Array(aL3, i)
        Case 4
            Call f_verify_value_in_Array(aL4, i)
        Case 5
            Call f_verify_value_in_Array(aL5, i)
    End Select
    Next
    End Function
    
    
    
    
    Function f_verify_value_in_Array(pArray, pNumber As Long)
    Dim aString() As String
    f_verify_value_in_Array = False
    aString() = Split(pArray, ",")
    If (UBound(Filter(aString, pNumber)) > -1) Then
        f_verify_value_in_Array = True
    End If
    End Function

  10. #10
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    As I said before, when you make that function call in call f_verify_value_in_Array("aL" & i, i), all you are giving the function is a STRING which happens to contain the name of a variable, in this case an array; but Access has no way of knowing that. It cannot use that string value to create an actual reference to the array.

    Some programming languages (e.g. C++) have "pointer" types, which could probably do what you need, but VBA does not.



  11. #11
    mcarval22 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Brazil
    Posts
    19
    Thanks davegri, but this is just a piece of code as an example, I'll have infinite arrays and do not want to select for each case, the code will be very inelegant.
    John_G're right, I do not know a way to pass the memory reference of an array.
    I have many ways to do this code, but I am concerned about performance and efficient code.

  12. #12
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Do all your arrays have the same structure, e.g. for aL5, will it always be = split("41,42,43,44,45,46,47,48,49,50",",")?

    If so, you have a pattern you can use :

    Aln = split("10n-9,10n-8,10n-7,10n-6,10n-5,10n-4,10n-3,10n-2,10n-1,10n",",")

    In other words, if you know n, then you always know what the contents of the array will be.

    You could then generate a temporary array in a loop (using the above pattern), and pass that array to the function.

  13. #13
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Whether or not the array has to be passed to the function is unclear to me. The second function merely looks like a test which returns true or false, perhaps just to get the poster to the point of having successfully passed the array. I think more information as to what will eventually happen there is needed. The solution may be to loop the array values in the first and pass them to the second. There also seems to be similar requests for passing an array to a function:
    http://stackoverflow.com/questions/2...unction-in-vba
    http://www.access-programmers.co.uk/...ad.php?t=94561
    http://www.bettersolutions.com/vba/V...A316320331.htm

  14. #14
    mcarval22 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Brazil
    Posts
    19
    Thanks again John_G, but this code is just a small example, the arrays will not have a standard, they are based on a mathematical study and have very different content.

  15. #15
    mcarval22 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2016
    Location
    Brazil
    Posts
    19
    Micron, please do not stick only to the piece of code that I put in the post, it is not my real code, is only my problem which is just as dynamically pass the name of an array to a function and function destination identify the name of this array, I'll have hundreds or thousands of arrays to test and I would like make a single function to receive any array reference and not receive the array itself

Page 1 of 3 123 LastLast
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
  •  
Other Forums: Microsoft Office Forums