Having trouble with MaxofList Function in VBA Code

[MeSoCrazy]

Hello all,

I have a problem that in my code. My code creates the string I need fine, so I am going to abbreviate the code to illustrate my issue.

In my code I have run a query that gives me 1 to many rows in a recordset. I then determine between two fields which field for that row represents my PlanDate value. Then I place that value into a building string. Essentially if I had three rows in my recordset, my string would look like this:
#1/1/2009#,#1/2/2009#,#1/3/2009#

I am then trying to pass that string into the MaxofList Function written by Allen Browne. I know this funcion is working based on the following from my immediate window:

?MaxofList(#1/1/2009#,#1/2/2009#,#1/3/2009#)
1/3/2009

However, when I try to do this in VBA code it successfully moves to the MaxofList function in my Module, but for some reason it does not separate the values so the LBound and UBound both are showing as Zero when I step through the code. So it goes into the MaxofList loop and does not recognize a number or a date and returns nothing. Then when I get back to my code I am getting a Invalid use of Null Error because I am trying to place the MaxofList result value into a string or technically a forms textbox.

Below is the abbreviated code:

strPlanDateValues = "#1/1/2009#, #1/3/2009#, #1/4/2009#"
    
    
If Len(strPlanDateValues) = 0 Then     'Populate Plan Field
  KMGPlanStratMeet = Null
Else
  strResult = MaxOfList(strPlanDateValues)
  Me.KMGPlanStratMeet = strResult
End If

Also as I am stepping through the MaxofList code it shows the varValues(i) as the string:

"#1/1/2009#, #1/3/2009#, #1/4/2009#"

so it seems to me the variant is not separating on the commas and treating it as one array item. Then it is failing the IsNumeric and IsDate test and so varMax is Nothing.

Sorry here is the code for the Max of List function by Allen Browne:

Public Function MaxOfList(ParamArray varValues()) As Variant
 Dim i As Integer        'Loop controller.
 Dim varMax As Variant   'Largest value found so far.

 varMax = Null           'Initialize to null

 For i = LBound(varValues) To UBound(varValues)
  If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
    If varMax >= varValues(i) Then
     'do nothing
    Else
     varMax = varValues(i)
    End If
  End If

 Next

 MaxOfList = varMax
End Function

So, can someone tell me what I am doing wrong when trying to pass the string into the MaxofList? Unfortunately, the only example of a Date passing to this function I have seen is through a query column. I can not pass from a query in this case.


Thanks in Advance,

Troy :-D

 

[jrbarnett]

Your code uses a string but Allen's code takes a ParamArray as input.
Either convert your string to a ParamArray or adapt it to use a comma separated string.

John

 

[MeSoCrazy]

John,

Thanks for the reply. I am not that familiar with Arrays yet. I have vague knowledge of arrays at this point. I thought that my string is comma separated. below is the string I am entering inside the MaxofList():

strPlanDateValues = "#1/1/2009#, #1/3/2009#, #1/4/2009#"

so it is should be passing:

MaxofList(#1/1/2009#, #1/3/2009#, #1/4/2009#)

Let me explain that I have to put this string together one value at a time by building the string as I go through a loop while not EOF of a recordset.

Or could you give me an example of how I take my string and create a ParamArray from it and how I pass the array to MaxofList? Keep in mind that I am building my list one value at a time when looping through my recordset.

Thanks, Troy

 

[MeSoCrazy]

John,

I misread earlier and understand you meant to alter the MaxofList function to accept a string. I guess my question is based on examples from Allen's site it looks like if you hand the following line it will process.

MaxofList(#Date#, #Date2#, #Date3#) that it will return the max value.

If in my code above I have:

strResult = MaxOfList(strPlanDateValues) and strPlanDateValues defined as:
strPlanDateValues = "#1/1/2009#, #1/3/2009#, #1/4/2009#"

Then I do not understand how that is different than typing the same line by hand in the immediate window?

 

[PHV]

You have to understand that MaxofList(#Date#, #Date2#, #Date3#) is not the same as MaxofList("#Date#, #Date2#, #Date3#")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya MeSoCrazy . . .

When you enter items in a ParamArray they have to be added in an [blue]explicit[/blue] format ... that is [blue]one value for each item![/blue]

When you entered ...

[blue]MaxofList(#1/1/2009#, #1/3/2009#, #1/4/2009#)[/blue]

... in the immediate window you entered 1 item each! You could've just as well assigned three variables! ... Get the point! You can't assign a delimited string list variable to a ParamArray. This variable will be considered a single item.

Try my modification below:

[blue]   Dim i As Long, varMax As Date, Ary
   
   Ary = Split(strList, ",")
   
   For i = LBound(Ary) To UBound(Ary)
      If IsDate(Ary(i)) Then
         If Ary(i) >= varMax Then varMax = Ary(i)
      End If
   Next

    MaxOfList = varMax[/blue]

[purple]Special Note:[/purple] I'm using the [blue]Split[/blue] array function to setup the array. Since were spliting a string the items in the array will be strings ... not dates!

So when you pack your string be sure not to include the hash marks & spaces:

[blue]strPlanDateValues = "1/1/2009,1/3/2009,1/4/2009"         [green][b]'Correct![/b][/green]
strPlanDateValues = "#1/1/2009#, #1/3/2009#, #1/4/2009#" [red][b]'Wrong[/b][/red][/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MeSoCrazy]

All,

Thank you, when I get to work on Monday I will put into place what I need to fix my issue! I think all was trying to tell me the same thing, but the light bulb didn't go on until I read the last post.

Thanks much, AceMan1! I wasn't realizing that the function was seeing my whole string as one item, as I thought with the commas in the string it would see it as three separate items. I guess that is because of my lack of Array knowledge.

But now I see the light!

Again THANKS!!!!

 

[TheAceMan1]

the full function:

[blue]Function MaxOfList(strList As String) As Variant
   Dim i As Long, varMax As Date, Ary
   
   Ary = Split(strList, ",")
   
   For i = LBound(Ary) To UBound(Ary)
      If IsDate(Ary(i)) Then
         If Ary(i) >= varMax Then varMax = Ary(i)
      End If
   Next

    MaxOfList = varMax

End Function[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]