+ Reply to Thread
Results 1 to 17 of 17

Arrays & Ranges as Parameters

  1. #1
    Valued Forum Contributor ffffloyd's Avatar
    Join Date
    05-20-2008
    Location
    Perth, WA, Australia
    MS-Off Ver
    Office 365
    Posts
    249

    Arrays & Ranges as Parameters

    In order to be able to replicate something like the MATCH function, which accepts a String (or cell reference), an Array of Strings (or a range of cells) and then the match type as parameters, how do I declare such a function header?

    I had thought something like this would do, but it is returning #VALUE without executing any of the function. It is something to do with the Array of Strings that is causing the problem.

    Please Login or Register  to view this content.
    I don't want to declare a Range for the lookup array because I also want the function to be able to accept an array of literal strings instead of a range of cells as worksheet functions are wont to do.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259
    Hello ffffloyd,

    You can't declare a dynamic array as an argument in a Function call. You can declare it as a Variant which can hold a array.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

  3. #3
    Valued Forum Contributor ffffloyd's Avatar
    Join Date
    05-20-2008
    Location
    Perth, WA, Australia
    MS-Off Ver
    Office 365
    Posts
    249
    Thanks for that, Leith. It leads to another dumb question though. How do I then determine the size of that variant array? I seem to be able to access individual elements but if I try to use the LBound or UBound functions then my function is aborting with an error.

    The Excel Help doesn't seem to provide a lot of information about variants and I don't want to keep asking questions that I could find the answers to elsewhere. What is the best source for learning about them?

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259
    Hello ffffloyd,

    Not a dumb question at all. I thought about it after I posted my answer. You can determine what type the variant is by using the VarType function. This will return an integer value indicating the what the variant is. There are built in constants you can use to test the return value. Here is the list...

    Please Login or Register  to view this content.
    Example
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

  5. #5
    Valued Forum Contributor ffffloyd's Avatar
    Join Date
    05-20-2008
    Location
    Perth, WA, Australia
    MS-Off Ver
    Office 365
    Posts
    249
    Ok, perhaps it's time for me to post a bit of the code I'm having trouble with.

    As a background, I have a series of lines that are identified by the leftmost sequence of characters (sort of like keywords except they're not necessarily single words). I therefore want to do a MATCH on the leftmost n characters on each line, but n is not a constant; it is the length of each possible keyword.

    So I wrote the function LeftMatch which I translated into Californian in my original post for the comfort and familiarity of many of the forum members. However, to be serious for a moment (which, you'll understand, is even less familiar ground for me than VBA) the full code is as follows:

    Please Login or Register  to view this content.
    (The optional iMatchType isn't yet being considered in this prototype.)

    I call it by entering the formula =LeftMatch($P$71,$P$74:$P$78,1) in a test cell, however it is returning a #VALUE error.

    If I add the following lines at the beginning of the function then it returns expected messages for the first six MsgBoxes (including VarType=8204 (8192+12), IsArray=True) but fails to return the seventh, which is the one with the UBound (or the LBound if I put them in that order instead):

    Please Login or Register  to view this content.
    Now, I have since found a piece of sample code online that uses a Variant as a parameter for an Array of Strings (http://support.microsoft.com/kb/133135) and it looks like UBound certainly should be a valid call. It all looked fairly straightforward to me at first but it has been a while since I've messed around in VBA so I must be overlooking something obvious. Any idea what I'm missing?

    Cheers,
    Floyd.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835
    try
    1)
    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    2) change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  7. #7
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373
    Hi Leith

    You can't declare a dynamic array as an argument in a Function call. You can declare it as a Variant which can hold a array.
    When used in a procedure declaration I believe that the () following an identifier is not a declaration of a dynamic array, it specifies that the parameter is an array.

    I've used many times when I pass a typed array. It's also possible to pass it in a variant but in that case you lose the type checking, it is equivalent to passing the parameter as untyped.

    Ex:

    Please Login or Register  to view this content.
    This funtion counts the number of characters in an array of strings.

    We can test it for ex. with (using both a static and a dynamic array):

    Please Login or Register  to view this content.
    Best regards
    lecxe

  8. #8
    Valued Forum Contributor ffffloyd's Avatar
    Join Date
    05-20-2008
    Location
    Perth, WA, Australia
    MS-Off Ver
    Office 365
    Posts
    249
    Thanks, jindon. That works as far as determining that the parameter is a Range and assigning the values to <i>a</i>, though I don't understand the need to transpose them.

    Anyhow, it then fails as before when I try to use either LBound or UBound, this time when applied to <i>a</i>. (Embedded MsgBoxes confirm this.)

    Cheers,
    Floyd.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835
    Quote Originally Posted by ffffloyd
    Thanks, jindon. That works as far as determining that the parameter is a Range and assigning the values to <i>a</i>, though I don't understand the need to transpose them.

    Anyhow, it then fails as before when I try to use either LBound or UBound, this time when applied to <i>a</i>. (Embedded MsgBoxes confirm this.)

    Cheers,
    Floyd.
    1) How are you passing the parameter to the function ?
    2) Tranpose is needed to make 1d array from vertical range.

    Above changes are only based on my GUESS of how you are passing the arguments...

  10. #10
    Valued Forum Contributor ffffloyd's Avatar
    Join Date
    05-20-2008
    Location
    Perth, WA, Australia
    MS-Off Ver
    Office 365
    Posts
    249
    Thanks, lecxe. If that is the case though, what is wrong with my original bit of code? It still fails.

  11. #11
    Valued Forum Contributor ffffloyd's Avatar
    Join Date
    05-20-2008
    Location
    Perth, WA, Australia
    MS-Off Ver
    Office 365
    Posts
    249
    Hi jindon.

    The call to the function is, in this case, as shown in my rather long reply in message #5:

    I call it by entering the formula =LeftMatch($P$71,$P$74:$P$78,1) in a test cell, however it is returning a #VALUE error.
    I can live with the transpose, though if I have to transpose vertical arrays then how do I determine whether what has been passed is vertical or horizontal?

    Nevertheless, the function is still failing whenever I try to access LBound or UBound of any of the arrays.

    Cheers,
    Floyd.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835
    try
    When iMatchType = 0 - Binary compare (case sensitive), 1 for TextCompare (non case sensitive)
    Please Login or Register  to view this content.

  13. #13
    Valued Forum Contributor ffffloyd's Avatar
    Join Date
    05-20-2008
    Location
    Perth, WA, Australia
    MS-Off Ver
    Office 365
    Posts
    249
    Ok, that didn't quite work as is but, following your idea, this code does return a result:

    Please Login or Register  to view this content.
    Note that I needed to change the function result to a Variant to hold the possible CVErr() value, however this was not the reason for the earlier failures: it had never got that far.

    However, this now raises more questions:

    1. Why is the function unable to work with a Variant as a parameter, but is when the parameter is set to a Range and assigned to a Variant?

    2. What do I do when my Range is horizontal and how do I know when it is?

    3. What do I do if I want to pass a genuine array (as other Excel functions do)?

    4. Why is the code not stopping and raising an error with a messagebox instead of just silently failing and returning a #VALUE in the cell?

    Thanks to all who have contributed.

    Cheers,
    Floyd.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835
    Quote Originally Posted by ffffloyd
    Ok, that didn't quite work as is but, following your idea, this code does return a result:
    How it is not working?
    However, this now raises more questions:

    1. Why is the function unable to work with a Variant as a parameter, but is when the parameter is set to a Range and assigned to a Variant?
    What is your concern about Variant type of argument?
    2. What do I do when my Range is horizontal and how do I know when it is?
    just to more code for it.
    3. What do I do if I want to pass a genuine array (as other Excel functions do)?
    Then my first suggestion of changes.
    4. Why is the code not stopping and raising an error with a messagebox instead of just silently failing and returning a #VALUE in the cell?
    That how you made it!

    Why you ask so may "What ifs" ?
    What do you want to do?
    I can't read your brain, you know?

  15. #15
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373
    Thanks, lecxe. If that is the case though, what is wrong with my original bit of code? It still fails.
    This may no longer be relevant at this point, but since you asked:

    I had not yet read your code but now that I did I see that you are wrong: your original code works.

    It does not, however, work as you intended, you have an error in the design.

    When you specify a parameter as "saLookupArray() As String" you are saying that you are going to pass an array of strings. This means that if you pass anything else you'll get a type mismatch error.

    Do the test to see that it works as you wrote it. I didn't change anything in the body of the function and restored the "saLookupArray() As String" as parameter:

    Please Login or Register  to view this content.
    This is your original code with the parameter "saLookupArray() As String" as you intended. Notice that I didn't change the code:

    Please Login or Register  to view this content.
    So, in conclusion, your original code works but the "saLookupArray() As String" is wrongly specified, not because it's a syntax error but bacause you want to pass parameters other than arrays of strings.

    Hope this helps
    lecxe

  16. #16
    Valued Forum Contributor ffffloyd's Avatar
    Join Date
    05-20-2008
    Location
    Perth, WA, Australia
    MS-Off Ver
    Office 365
    Posts
    249
    Thanks, lecxe. I'm narrowing it down now, almost like a binary chop to find what is and what isn't in error.

    So, the questions now become:

    1. If I am passing values from a spreadsheet (as so many Excel functions do) then how should I declare them as parameters so that it doesn't matter whether the range is vertical or horizontal and doesn't even matter if I pass a physical array of strings?

    2. If the answer is to declare the parameter as a Variant then why is it failing when I try to use LBound or UBound even though the VarType function is recognising it as an Array?

    3. Why isn't VBA raising a messagebox when it strikes an error instead of just failing silently? Is this a feature that exists in a set of options somewhere that might have inadvertently been turned off?

    Sorry to be asking so many questions but I'm just trying to get my head back into the kind of space it used to be in only a few years ago.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1