-
Feb 23rd, 2012, 09:52 AM
#1
Thread Starter
New Member
[RESOLVED] VBA Excel: Problem with WorksheetFunction.Large?
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:
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.
Last edited by Thralni; Feb 28th, 2012 at 03:01 AM.
Reason: Problem resolved
-
Feb 24th, 2012, 05:31 AM
#2
Re: VBA Excel: Problem with WorksheetFunction.Large?
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?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 27th, 2012, 03:21 AM
#3
Thread Starter
New Member
Re: VBA Excel: Problem with WorksheetFunction.Large?
Originally Posted by westconn1
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:
Code:
'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.
-
Feb 27th, 2012, 03:10 PM
#4
Re: VBA Excel: Problem with WorksheetFunction.Large?
a long type will round to a whole number, change to double or other type that can hold decimal values
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 28th, 2012, 03:00 AM
#5
Thread Starter
New Member
Re: VBA Excel: Problem with WorksheetFunction.Large?
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|