Results 1 to 5 of 5

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

Hybrid View

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    6

    Resolved [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

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    6

    Re: VBA Excel: Problem with WorksheetFunction.Large?

    Quote Originally Posted by westconn1 View Post
    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.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  5. #5

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    6

    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
  •  



Click Here to Expand Forum to Full Width