none
How do I pass an integer array as a parameter/argument to a subroutine in VBA?

    Question

  • I have a block of code as follows:

    Private Sub Find_Latest_Build(strNetInstPath As String, strLocInstPath As String, Src_Folder As String, strZipFl As String, ShellStr As String)
        Dim NewVerNo(3) As Integer, CurrVerNo(3) As Integer, ChkNo As Integer
        Dim StartPos As Integer, i As Integer, j As Integer
        Dim NewVerStr As String, CurrVerStr As String

        For i = 0 To 3
            CurrVerNo(i) = 0
        Next
        CurrVerStr = ""
        Call ParseBldNo(CurrVerStr, CurrVerNo(3))
    End Sub

    Now, I'm trying to pass an integer array (e.g., "CurrVerNo(3)"), but I'm not sure how to do it correctly.  I tried different versions of the ParseBldNo subroutine.  Example 1:

    Private Sub ParseBldNo(VerStr As String, VerNo As Integer)

        Dim StartPos As Integer, i As Integer
        Dim ChkChar As String, ChkMrk As Long
       
        StartPos = 1
        i = 0
        Do While (i <= 3)
            ChkMrk = InStr(1, VerStr, ".")
            If ChkMrk = 0 Then
                ChkChar = VerStr
            Else
                ChkChar = Left(VerStr, ChkMrk - 1)
            End If
            VerNo(i) = CInt(ChkChar)
            StartPos = ChkMrk + 1
            VerStr = Mid(VerStr, StartPos)
            i = i + 1
        Loop
    End Sub

    The result is a compile error (Expected Array).

    Example #2:  Changed the subroutine header to read:

    Private Sub ParseBldNo(VerStr As String, VerNo(3) As Integer)

    This instantly resulted in a compile error (syntax--Expected ')' )

    Example  #3:  Changed the subroutine header again to read:

    Private Sub ParseBldNo(VerStr As String, ParamArray VerNo() As Variant)

    Running the script this way generates a run-time error 9 message (subscript out of range) at the statement:

    VerNo(i) = CInt(ChkChar)--debugging showed it passed once, but not a second time.

    Example #4:  Revised the first few lines of the subroutine to read:

    Private Sub ParseBldNo(VerStr As String, ParamArray VerNo() As Variant)

        Dim StartPos As Integer, i As Integer
        Dim ChkChar As String, ChkMrk As Long
        ReDim VerNo(3) As Integer

    This generated another compile error (UGH!)--Can't change data types of array elements

    Since I've tried and failed several times, could someone please advise as to the proper approach to passing integer arrays as parameters to subroutines?  I greatly appreciate it as I am somewhat of a "non-expert" to VBA scripting (based on a slew of questions posted on this forum).  Thank you very much.


    John J. Bottiger (QA Engineer, Gladiator Innovations LLC)

    Wednesday, February 08, 2012 3:18 PM

Answers

  • Hi John,

    Got to admit not sure if you mean you want to pass the whole array or just an individual value from the array "CurrVerNo(3)" rso I'll cover both.

    Pass the whole array

    Private Sub Test()
        Dim arr(3) As Integer
        arr(1) = 10
        
        TheProcedure arr
    End Sub
    
    Private Sub TheProcedure(arr() As Integer)
        Dim size As Long
        size = UBound(arr, 1)
        For i = 1 To size
            Debug.Print arr(i)
        Next
    End Sub
    

    and to pass the individual value

    Private Sub Test()
        Dim arr(3) As Integer
        arr(1) = 10
        
        TheProcedure arr(1)
    End Sub
    
    Private Sub TheProcedure(value As Integer)
        Debug.Print value
    End Sub
    

    Hope that helps, any questions please let me know


    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks

    • Marked as answer by jbottiger2367 Wednesday, February 08, 2012 4:51 PM
    Wednesday, February 08, 2012 4:19 PM

All replies

  • Hi John,

    Got to admit not sure if you mean you want to pass the whole array or just an individual value from the array "CurrVerNo(3)" rso I'll cover both.

    Pass the whole array

    Private Sub Test()
        Dim arr(3) As Integer
        arr(1) = 10
        
        TheProcedure arr
    End Sub
    
    Private Sub TheProcedure(arr() As Integer)
        Dim size As Long
        size = UBound(arr, 1)
        For i = 1 To size
            Debug.Print arr(i)
        Next
    End Sub
    

    and to pass the individual value

    Private Sub Test()
        Dim arr(3) As Integer
        arr(1) = 10
        
        TheProcedure arr(1)
    End Sub
    
    Private Sub TheProcedure(value As Integer)
        Debug.Print value
    End Sub
    

    Hope that helps, any questions please let me know


    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks

    • Marked as answer by jbottiger2367 Wednesday, February 08, 2012 4:51 PM
    Wednesday, February 08, 2012 4:19 PM
  • Hi again,

    The ParamArray is a very useful key word. It allows you to pass a variable number of parameters to a sub/function. Usually the number of parameters to a sub/function are fixed but with ParamArray you can pass different numbers of parameters. Give you an example... a function that produces a formatted string.

            

    Notice how the FormatString method is passed 2 parameters in the first call, 3 parameters in the second call, and 4 in the third call. But the actual FormatString signature only takes 2 parameters. It allows a variable number of parameters. Can be quite handy.

    Private Sub Test()
        Debug.Print FormatString("{0}", 10)
        Debug.Print FormatString("{0} <> {1}", 10, "Ted")

        Debug.Print FormatString("This will output three values which are {0}, {1} and {2}", 10, 19, 35)
    End Sub


    Private Function FormatString(template As String, ParamArray values() As Variant) As String
        Dim result As String
        result = template

        Dim paramCount As Long
        paramCount = UBound(values, 1)
        For i = 0 To paramCount
            result = Replace(result, "{" & i & "}", values(i))
        Next
        FormatString = result
    End Function


    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks

    Wednesday, February 08, 2012 4:31 PM
  • Thanks for responding.  For the record, the parameter being passed was in fact a fixed array.  So ParamArray could be used in the event that the number of parameters being passed varies, even if that doesn't happen.

    Your first answer really helped.  My colleague at work suggested doing a String Compare to compare two strings, comparing each character in the same positions of the string one at a time to determine which string is higher.  For example, String1 = "6.0.0.2" and String2 = "6.0.2.1".  Going one digit at a time (l to r), String2 would be the higher of the two.

    I was going to try both approaches in my script, so I saved a duplicate of the script to see if both methods work.  I'm almost certain that both versions would work without error, so my next question is this:  in your professional opinion, which approach is better:  the approach I'm taking to convert the digit characters into integers and place an arrays to compare, or the actual string compare, digit by digit?

    Thanks again for your help.  I greatly appreciate it.


    John J. Bottiger (QA Engineer, Gladiator Innovations LLC)

    Wednesday, February 08, 2012 4:51 PM
  • Hi,

    Comparing version numbers can be a little tricky. Doing a direct string compare might not work if you consider these numbers as an example

    6.0.2.1 vs 6.0.2.10    

    all characters match up to the 1 and then what happens with the 0? difficult to say, you'd need to code for the exception, and it's not a good idea to add code for some exceptions because your bound to miss some and it bloats the code.

    Isn this case the exception is because the version numbers have different character lengths. 

    To remove the exception the version numbering should have been in this case.

    6.0.2.01 vs 6.0.2.10 

    Now it becomes easier to do the check but version numbering needs to set up this way from the start. 

    I have coded a version check algorithim before and how it worked was to turn the version number (6.0.2.1) into a integer number (6002001) making it easier to compare them. How that worked was to split the number up into it's parts, using the dots, and then to multiply the number by a value depending on where it was in the list, before then add up all the values into a total... so give you an example

    6.0.2.1

    6 * 1000

    0 * 100

    2 * 10

    1 * 1

    = 6000 + 0 + 20 + 1 = 6021

    6.0.2.10

    6 * 1000

    0 * 100

    2 * 10

    10 * 1

    = 6000 + 0 + 20 + 10 = 6030

    Using these numbers you can compare which is the greater version. 6030 is greater than 6021. That's the idea but it falls short unless your careful about the multipliers because there can be clashes. The multipliers used above are basically base 10, which are good for examples but not good in reality as you can get the following 

    6.0.2.10

    6 * 1000

    0 * 100

    2 * 10

    10 * 1

    = 6000 + 0 + 20 + 10 = 6030

    6.0.3.0

    6 * 1000

    0 * 100

    3 * 10

    0 * 1

    = 6000 + 0 + 30 + 0 = 6030

    The same number is produced. So you need to choose a different base and that's the tricky part!!!

    Can't remember the multipliers I ran with, need to look up the code, I'll post again later as I need to be somewhere.


    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks

    Thursday, February 09, 2012 9:21 AM
  • Derek,

    Thanks again for putting more perspective into this.  This is an outstanding lesson to teach when it comes to comparing software versions based on the syntax of software versions.  In fact, yesterday I tested the StrCmp method of comparing 6.0.0.2 vs. 6.0.0.10, and the script resulted in 6.0.0.2 being the latest version...which is (obviously) incorrect.  While my QA lead and my colleague (a tech support guy) both thought it would be simpler to just do a string compare rather than convert the substrings into integers, it doesn't work in this case and I explained it to them.  My lead agreed that it's counting the number of places then compares the strings .2 higher the .1 (ignored the '0').

    I also liked your thought process regarding the treatment of the version numbers without the decimals, but only with the double-digit values (x.x.x.10) treated as one position.  However, you supported the fact that it too can be flawed based on certain circumstances.

    BTW, are you a CS professor or teacher (or were you ever one)?  I think you would make an excellent instructor as your shared knowledge is very valuable.

    Thanks again for all your help.  I'm not sure how it's done, but I hope the powers that be in the MSDN forums (e.g., moderators, administrators) take an action to give you a gold achievement on your profile.

    Enjoy your day sir.  Thanks again.


    John J. Bottiger (QA Engineer, Gladiator Innovations LLC)

    Thursday, February 09, 2012 3:27 PM
  • Hi John,

    Meant to post yesterday with the multipliers I used but couldn't find the code. The multipliers that are used prevent any clashes from occuring. The flaw is still there but less likely to happen.

    The multipliers, I believe, that I used were based on the version number itself. So with our versioning we assumed that the last number in the version wouldn't ever exceed 999. Which meant that the second last number was multiplied by 1000, so that it's values would always be higher than 999. 

    Actually a better way to describe it is like this ...

    if each section maximum number is 999 then each section needs three numbers in order to be represented, 

    so the version number 0.0.0.0 is the number  000 000 000 000 (three numbers to represent each section, written out with spaces but but would be stored in code as a long value 000000000000)

    if the version number is 1.1.0.10 then it's number would be 001 001 000 010 (1001000010)

    if the version number is 1.2.4.16 then it's number would be 001 002 004 016 (1002004016)

    that's how to prevent clashes. The multiplers that are used with each section multiply the number so that it's positioned correctly. 


    If the maximum for each section is 9999 then it's four numbers per section and the multiplier numbers change. 

    Of course each section can have different numbers per section, for example the last number could be very high (the build number), while the first number (the major number) is less likely to go over 99. You can produce multipliers based on this just the same, as long as the multipliers move the numbers into the correct positions.

    11.12.299.13912  = 011 012 0299 13912

    That's how I worked it. Hope I explained that well enough, if not let me know and I'll try again.

    Thanks very much for your kind words, really do appreciate it. I've never been an teacher but I believe the best way to learn is to teach. 


    "The programmer, like the poet, works only slightly removed from pure thought-stuff. He builds his castles in the air, from air, creating by exertion of the imagination." - Fred Brooks

    Friday, February 10, 2012 8:22 AM