[RESOLVED] VBA Excel: Problem with WorksheetFunction.Large?

[Thralni]

Hey guys,

I'm having a problem of whgich I have no idea how it came into being. It's about this bit of code:

Sub GrootsteWaarden()

    Row = 3 'Begin bij rij 3
    RowEnd = 14 'Eindig bij rij 14
    
    MsgBox Top1

    Top1 = WorksheetFunction.Large(Range("F" & Row & ":" & "F" & RowEnd), 1)
    
    MsgBox Top1
    
    WaardenRegel1 = WorksheetFunction.Match(Top1, Range("F" & Row & ":" & "F" & RowEnd), 0) + 2
    Range("B" & WaardenRegel1 & ":" & "F" & WaardenRegel1).Interior.Color = 65535
    
    Top2 = WorksheetFunction.Large(Range("F" & Row & ":" & "F" & RowEnd), 2)
    WaardenRegel2 = WorksheetFunction.Match(Top2, Range("F" & Row & ":" & "F" & RowEnd), 0) + 2
    Range("B" & WaardenRegel2 & ":" & "F" & WaardenRegel2).Interior.Color = 65535
    
    Top3 = WorksheetFunction.Large(Range("F" & Row & ":" & "F" & RowEnd), 3)
    WaardenRegel3 = WorksheetFunction.Match(Top3, Range("F" & Row & ":" & "F" & RowEnd), 0) + 2
    Range("B" & WaardenRegel3 & ":" & "F" & WaardenRegel3).Interior.Color = 65535

End Sub

What the code doe,s is find the three highest values in a range, then get their row numbers, and finally make the corresponding row yellow. The valuyes themselves and their row numbers are stored for later use.

Note that I didn't include any of the variable definitons, but I all defined them outside of any sub, as public variables so that all subs can use them if necessary.

Now, the problem is that the WorksheetFunction.Large line is suddenly ALWAYS returning "1" instead of the highest number, which would be 0.65 or something, and I have no idea why. It worked beautifully an hour ago...

It seems the problems started when I copied the sub to another module, but deleting it form that module doesn't remedy the situation. I have checked the code time on time again, and see absolutely no reason for to happen.

I have no idea whatsoever what to do.I hope anybody can help...

PS: there are some thing in there that are there purely for my debugging purposes, like the message boxes. When all starts working again, they will be removed.

[westconn1]

Note that I didn't include any of the variable definitons, but I all defined them outside of any sub, as public variables so that all subs can use them if necessary.

while it is good practice to declare all variables, you should define them at the lowest level (within the procedure) unless you really need to reuse the same variable values in many places

also make it hard for us to see if your variable declaration could be causing a problem
what are top1 - 3 defined as? could they have been changed? do you have any duplicate definitions?

[Thralni]

while it is good practice to declare all variables, you should define them at the lowest level (within the procedure) unless you really need to reuse the same variable values in many places

also make it hard for us to see if your variable declaration could be causing a problem
what are top1 - 3 defined as? could they have been changed? do you have any duplicate definitions?

Is there a specific reasong that declaring them at the lowest level is the best option? I pretty much need to use all these variables throughout all subs, so I don't think I have much choice, do I?

Here are all variable declarations:

'Onderstaande is nodig voor workshete identificatie en useform
Public WerkbladNPO As String                'Zal de naam van het werkblad bevatten met de NPO data
Public WerkbladSA As String                 'Zal de naam van het werkblad bevatten met de Soundaware data
Public WorksheetOutput As String            'Zal de nama van het werkblad bevatten waar de rapportage moet verschijnen
Public SheetExists As Integer               'Gebruikt ind e userform: controleert het bestaan van een worksheet
Public PressedCancel As Integer             'Gebruikt voor de worksheet: controleert of de gebruiker op annuleren heeft gedrukt

'Onderstaande is nodig voor het maken van de tabellen
Public TabelType As Integer                 'Geeft de te maken tabel aan. 1 is jaar, 2 is maand, 3 is week
Public Column As Integer                    'Voor het maken van de tabel: de beginkolom
Public Row As Integer                       'Voor het maken van de tabel: de beginrij
Public ColumnEnd As Integer                 'Voor het maken van de tabel: de eindkolom
Public RowEnd As Integer                    'Voor het maken van de tabel: de eindrij

'Onderstaande is nodige voor datum optelling en plaatsing
Public Datum As String                      'De datum, nodig om te herkennen wat opgeteld moet worden
Public Dag As Integer                       'Bevat de dag waarnaar gezocht wordt
Public Maand As Integer                     'Bevat de Maand waarnaar gezocht wordt
Public Jaar As Integer                      'Bevat het jaar waarnaar gezocht wordt.
Public RowNumber As Long                    'Interne variabele: definieert rijnummer waarvandaan gelezen gaat worden.
Public StartCell As Integer                 'Definieert de eerste cel van het bereik dat opgeteld wordt
Public EndCell As Integer                   'Definieert de laatste cel van het bereik dat opgeteld wordt
Public SumResult As Long                    'Houd het resultaat vna de opsomming.
Public WorksheetName As String              'Definieert van welke worksheet gelezen moet worden.
Public ColumnAsString As String             'Definieert de kolom waarin de resultaten gezet zullen worden
Public DatumKolom As String                 'Definieert de kolom waar datum in staat (NPO = AL, SA = AP)
Public DurationKolom As String              'Definieert de kolom waar duration in staat (NPO = AK, SA = AO)

'Onderstaande is nodig om de drie grootste verschillen binnen jaar, maanden en weken te vinden.
Public Top1 As Long                         'Used to find the highest number
Public Top2 As Long                         'Used to find the second highest number
Public Top3 As Long                         'Used to find the third highest number
Public WaardenRegel1 As Long                'Used to find the row on which the highest number is located
Public WaardenRegel2 As Long                'Used to find the row on which the second highest number is located
Public WaardenRegel3 As Long                'Used to find the row on which the third highest number is located

'Onderstaande wordne gebruikt bij het maken van een jaaroverzicht grafiek
Public Grafieken As ChartObject
Public GrafiekTitel As String

Since I'm Dutch, all the comments are in Dutch also. Most of them are irrelevant to the sub I posted, so I only translated the "top1-3" and "Waardenregel1-3" variables. If you need any more I'll be happy to oblige.

I'm still not sure what is causing the error, because the code worked fine before. I tried declaring the variables necessary in the sub itself using Dim instead of Public, but that didn't help. I'm not quite sure what is happening; top1 always becomes "1", subsequently generating an error in the "Waardenregel1" line of code.

[westconn1]

a long type will round to a whole number, change to double or other type that can hold decimal values

[Thralni]

Wow. I can't believe the answer would be that simple... Didn't even occur to me that that might be the problem! I changed it to double, and everything works great again. Thanks for helping!