Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Indeterminate number of arguments (Word97/SR2)

    I've enjoyed the discussion on Optional Arguments, and want to go further.

    Consider writing a generic cover function for a SORT routine. The cover function does all the set-up and tear-down work, including construction of the sort key.

    Now, I might want to sort an array on columns 5, 3 and 1, or maybe on column 2 only, or perhaps on columns 7 and 8.

    My cover function wants to receive as arguments, the name of the array, perhaps two switches (case-sensitive and sequence), and then an indeterminate number of parameters, maybe none (default to sorting on the first column), maybe three, one or two as in the suggestion above.

    I don't think the syntax of VBA permits this - an argument can be missing, but there must be a declaration for it in the procedure header, albeit with an "optional" tag.


    I could program around it by requiring that the keys be nominated in a string (as in "5,3,1") and then parse items out of the string using strSplitStringAt, so I can code it.

    But have I missed a feature of VBA that would provide a more elegant solution?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Indeterminate number of arguments (Word97/SR2)

    Chris,

    How about passing an array as the argument. If for example your procedure was called SortProc, you could call it with

    Call SortProc(Array(3,5,3,1)),

    where the first 3 is the number of columns, and the following numbers are the actual columns.

    Andrew

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indeterminate number of arguments (Word97/SR2)

    You can use ParamArray in an arglist.

    Look at the Help for Sub.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indeterminate number of arguments (Word97/SR2)

    > You can use ParamArray in an arglist.

    Thank you Howard!

    I had gone ahead with the string method outlined in my earlier message today, but I'm going to convert to the more VBA-like method:


    <pre>Public Function test1(strAr() As String, ParamArray myKeys())
    If IsMissing(myKeys) Then
    MsgBox "null"
    Else
    MsgBox myKeys(0)(0)
    MsgBox myKeys(0)(1)
    End If
    End Function

    Sub test()
    Dim strAr(4, 6) As String
    Dim ArKeys(3)
    ArKeys(0) = 1
    Call test1(strAr, ArKeys)
    Call test1(strAr)
    End Sub
    </pre>


  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indeterminate number of arguments (Word97/SR2)

    > How about passing an array as the argument.
    > Call SortProc(Array(3,5,3,1)

    I think my answer is "I can't do that because I'm already passing the entire array".

    Strange as that sounds, here's what I've done: Written a generic sort routine that sorts strings case-sensitive (always) ascending sequence (always!) a two-column array, where the first column is the key to be sorted, and the second column is the index to the original entry in the source array.

    I want to use cover functions to extract the relevant columns for keys, and build a single key (first column for my generic sort) to be passed to the generic sort. It it's a case-insensitive sort I apply UCase at the time I fabricate the key. If it's descending sequence I read the sorted array from the bottom to reconstitute the original array. If it's a LONG array, I can Format("000") and sort the string data etc.


    So .... I think I want to pass the entire array so that I can refabricate it in the sorted sequence using the index pointers (second column for my generic sort). I don't want to pass all seven columns, and then pass columns 3, 5 and 1 again as the keys. I'd rather have a way of just giving the names of the keys. My string method ("3,5,1") or Howard's suggestion of ParamArray works fine

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Indeterminate number of arguments (Word97/SR2)

    If you're going to use a ParamArray, I'd suggest you check the UBound before you start referring to its elements. Is there a reason for myKeys(0)(0)? I've never run across that syntax before, and a ParamArray is a single dimension array.
    Charlotte

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indeterminate number of arguments (Word97/SR2)

    > suggest you check the UBound

    Quite so. There was a lot I left out in my sampler, which was intended more of a submission of my homework exercise to the teacher (grin!)

    I am going to ask for clarification on the (0)(0). That puzzled me too. I got it from the Locals Window.

    (sigh) Back to the homework .....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •