Get Argument Name back from a parameter array

[JamesMcS]

Hi all, how's it going? Got an annoying one.... (aren't they all)

I've got a routine that looks like this:

Public Function Max_Salesorg(ParamArray flds() As Variant) As Double
Dim i As Byte, MaxOrg As String, MaxVal As Double

MaxOrg = flds(1).Name
MaxVal = flds(1)

For i = LBound(flds) + 1 To UBound(flds)
    
    If Nz(flds(i), 0) > MaxVal Then
    
        MaxOrg = flds(i).Name
        MaxVal = flds(i)
    
    End If

Next

Max_Salesorg = MaxOrg

End Function

It's supposed to bring back the maximum value for a given number of fields, but it's erroring out on the maxorg=flds(i).name line, saying object required (I know .Name isn't a valid member as the argument is a variant).

What I want to do is get whatever was entered as the argument, rather than the contents of the field. Make sense? Just for instance - I call the function thusly:

Max_Salesorg(Org1, Org2, Org3)

Org1 = 100
Org2 = 200
Org3 = 300

I'd like the function to return "Org3". But how???

Thanks in advance!

 

[vbaInet]

As the name implies, ParamArray is just an array so there's not much else information you can get from it.

If you want to know what fieldname, send the field name as well as the value as the paramter.

Max_Salesorg("Org1", "Org2", "Org3", [Org1], [Org2], [Org3])

 

[JamesMcS]

Thought as much, thanks bud!

 

[vbaInet]

No problemo!

 

[JamesMcS]

Grr though.... maximum arguments = 29, I already have 29 countries to put in there - git!! Do you know if that's just a limit on paramarrays, or arguments in general? I'll have aplay tomorrow, home time now

 

[vbaInet]

Don't let that limit your abilities

Max_Salesorg("Org1;Org2;Org3", [Org1], [Org2], [Org3])

Split that using ";" at the receiving end

 

[gemma-the-husky]

i wouldn't have thought there was a limit to the number of entries? is there?

is paramarray something special in this context, or just the address of an array, effectively.

out of interest, why are you not iterating arrayindex(0)

the array is zero-based

 

[vbaInet]

@gemma-the-husky: Now that you mentioned it I don't think there is a limit but it seems he encountered a problem.
@JamesMcSCan you show us the entire code? You might be encountering a limitation elsewhere.

 

[gemma-the-husky]

james

i think maybe you need a two dimensonal array. one index holding values, and the other holing descriptions. does that work?

 

[JamesMcS]

Morning campers! YES! a 2D array might just work....

I read somewhere on t'interweb that 29 is the limit for parameter arrays. As it happens I've got 29 countries as arguments, so that was lucky I tried putting 30 test arguments in, and it comes back with something about it being too complex (I'm using it in a query, maybe that's it). That's all the code up there....

So, now I just need to read up on 2D arrays

 

[vbaInet]

I see what you mean. The maximum number of arguments you can pass in a function in a QUERY is 29. If you call the function outside the query it is limitless.

Your query is returning values, not arrays so you won't be able to pass a 2D array to the ParamArray.

Here's another workaround:

Max_Salesorg("Org1;Org2;Org3", [Org1] & Chr(0) & [Org2] & Chr(0) & [Org3])

Chr(0) represents vbNullChar.

Remove the ParamArray and create two arguments, one of type String and the other a Variant.

Perform two splits, first one using a ";" delimeter to get the field names and the other using vbNullChar or Chr(0) as the delimeter to get the values.

 

[JamesMcS]

Dude, you're a legend amongst legends.... I'll have a play with this in a bit - still got some minor annoyances elsewhere in the query to sort out!