A Monster for Every Season: Summer 2
You can get A Monster for Every Season: Summer 2 now at Gumroad
Results 1 to 8 of 8
  1. - Top - End - #1
    Ettin in the Playground
     
    BardGuy

    Join Date
    Jan 2009

    Default Excel VBA: run-time error 91

    I posted earlier today about an issue I thought I fixed, but then I realized it wasn't working if the two variables I was comparing are equal to 0.
    Basically, I'm looking at some set positions on an Excel file and calculating the difference. The results should be at one decimal place, highlighted according to their value, and, if positive, have a + in front of them.

    I think I resolved the 'how to handle it being 0' by going a different route. But now I get Run-time Error 91: Object variable or With block variable not set. I've looked at Windows help, and it doesn't help me.
    Any idea what's causing it?

    Also, I got an error message whenever iCalc was set to something equaling zero. Why would that give an error? (That is, iCalc = Format(Sheets(ws).Cells(rowNum, colNum) - Sheets(ws).Cells(rowNum + 1, colNum), "###.#;(###.#)") gave an error about a type mismatch if the subtraction would yield a 0.) Can doubles not be negative?

    Update: the error is coming (I'm pretty sure) from this line: Rng = Sheets(ws).Cells(5, colNum)
    Any ideas how I can modify or take that out while still preserving the ability to highlight? I don't how know how translate something like "B5" (an acceptable range) into something I can express with rowNum & colNum. I tried Rng = Range(Cells(5, colNum)), but that doesn't work, either; does change the error code to 1004: Application-defined or object-defined error

    My current code is
    Code:
    Private Sub CommandButton1_Click()
    '
    'Format of cell-selection code: Sheets("SheetName").Cells(row, column)
    'Rounding note: Long or Int rounds, so we can't use that to get the precision this output requires
    '   A Double could be used.  However, since we are outputting this as text, I instead went with
    '   using a String. The 'Fixed' Format gives one decimal place: Format(Sheets(ws).Cells(3, 2) - Sheets(ws).Cells(4, 2), Fixed)
    
    Dim ws As String, wValue As String
    Dim rowNum As Integer, colNum As Integer 'sets row and column position
    Dim iCalc As Double 'generic calculation to use for multiple programs
    Dim Rng As Range
    ws = ActiveSheet.Name
    
    'for 2 HSAP Results.  This has a range of A2 through D5.
        rowNum = 3
        colNum = 2
        'if the current year is on the top row
        If Sheets(ws).Cells(3, 1) > Sheets(ws).Cells(4, 1) Then
           Rng = Sheets(ws).Cells(5, colNum)
           Do While colNum < 5
                If Sheets(ws).Cells(rowNum, colNum) = Sheets(ws).Cells(rowNum + 1, colNum) Then 'if zero
                    Range(Rng).Select
                    With Selection
                        .NumberFormat = "@"
                        .Interior.ThemeColor = xlThemeColorDark1
                    End With
                    Sheets(ws).Cells(5, colNum) = 0
                ElseIf Sheets(ws).Cells(rowNum, colNum) > Sheets(ws).Cells(rowNum + 1, colNum) Then 'if positive
                    iCalc = Format(Sheets(ws).Cells(rowNum, colNum) - Sheets(ws).Cells(rowNum + 1, colNum), "###.#;(###.#)")
                    Range(Rng).Select
                    With Selection
                        .NumberFormat = "@"
                        .Interior.Color = 5296274
                    End With
                    wValue = "+" & iCalc
                    Sheets(ws).Cells(5, colNum) = wValue
                ElseIf Sheets(ws).Cells(rowNum, colNum) < Sheets(ws).Cells(rowNum + 1, colNum) Then  'if negative
                    iCalc = Format(Sheets(ws).Cells(rowNum, colNum) - Sheets(ws).Cells(rowNum + 1, colNum), "###.#;(###.#)")
                    Range(Rng).Select
                    With Selection
                        .NumberFormat = "@"
                        .Interior.Color = 255
                    End With
                    Sheets(ws).Cells(5, colNum) = iCalc
                Else
                    MsgBox "error: 2 HSAP Results: non-number calculated", "error message 1"
                End If
            colNum = colNum + 1
            Loop
        Else      'if the current year is not on the top row
            MsgBox "error: 2 HSAP Results: A3 is not the current year", "error message 2"
        End If
    
    End Sub
    My previous post:
    Spoiler
    Show


    I've run into a bug in an Excel VBA program I'm working on. I'm calculating numbers to one-decimal place and, when the numbers are positive, I want an "+" in front of the number. For example, 2.6 - .3 should give "+2.3".

    I figured out I had to set the cells to text format to get the "+" to remain visible, but when I get a "+" to show up, I also get multiple decimal places. I tried making iELA a Double, and it still had the same problem.

    My code is a lot longer than ideal (should've used a Loop), so I'm only putting the relevant pieces below:
    Code:
    Dim iELA As String, Dim wValue As String
    
    iELA = Format(Sheets(ws).Cells(3, 2) - Sheets(ws).Cells(4, 2), Fixed)
    
    If CInt(iELA) > 0 Then 'highlight green if positive
                    Range("B5").Select
                    Selection.NumberFormat = "@"
                    Selection.Interior.Color = 5296274
                    wValue = "+" & iELA
                    Sheets(ws).Cells(5, 2) = wValue
    End If

    -----
    EDIT
    -----

    Upon further digging, I solved (or think I solved) my problem soon after posting. Rather than double-post, I'm editing this to add it.
    I set the Function more specifically (something I didn't know would work) then concatenated with the "+". I'm using a Double. It might work just as well with a String, but using a Double feels more right for a calculated number.

    Code:
    Dim ws As String, wValue As String
    'Dim iELA As String, iMath As String, iBoth As String
    Dim iELA As Double, iMath As Double, iBoth As Double
    ws = ActiveSheet.Name
    'ws = worksheet name; [email protected]@@ = calculated #s; wValue = [email protected]@@@ with a '+'
    
    
    'NOTE: this could be made more concise with a loop, but I didn't think of that until the coding was done
        'if the current year is on row 3
        If Sheets(ws).Cells(3, 1) > Sheets(ws).Cells(4, 1) Then
            iELA = Format(Sheets(ws).Cells(3, 2) - Sheets(ws).Cells(4, 2), "###.#;(###.#)")
            iMath = Format(Sheets(ws).Cells(3, 3) - Sheets(ws).Cells(4, 3), Fixed)  'fix not implemented for this or iBoth yet
            iBoth = Format(Sheets(ws).Cells(3, 4) - Sheets(ws).Cells(4, 4), Fixed)
            'ELA calculation
                If iELA > 0 Then 'highlight green if positive
                    Range("B5").Select
                    Selection.NumberFormat = "@"
                    Selection.Interior.Color = 5296274
                    wValue = "+" & Format(iELA, Fixed)
                    Sheets(ws).Cells(5, 2) = wValue
    
    '....various other code follows
    Last edited by JeenLeen; 2014-04-21 at 03:57 PM. Reason: realized did not solve problem

  2. - Top - End - #2
    Firbolg in the Playground
     
    Jasdoif's Avatar

    Join Date
    Mar 2007
    Location
    Oregon, USA

    Default Re: Excel VBA: run-time error 91

    Quote Originally Posted by JeenLeen View Post
    Update: the error is coming (I'm pretty sure) from this line: Rng = Sheets(ws).Cells(5, colNum)
    Any ideas how I can modify or take that out while still preserving the ability to highlight? I don't how know how translate something like "B5" (an acceptable range) into something I can express with rowNum & colNum. I tried Rng = Range(Cells(5, colNum)), but that doesn't work, either; does change the error code to 1004: Application-defined or object-defined error
    Have you tried
    Code:
    Set Rng = Sheets(ws).Cells(5, colNum)
    ?

    A quick test with
    Code:
    Sub Test()
        Dim Rng As Range
        Rng = Cells(3, 1)
        MsgBox (Rng.Value)
    End Sub
    gives me the error 91, while

    Code:
    Sub Test()
        Dim Rng As Range
        Set Rng = Cells(3, 1)
        MsgBox (Rng.Value)
    End Sub
    works correctly.


    VBA can be...weird when it comes to variable assignments.
    Feytouched Banana eldritch disciple avatar by...me!

    The Index of the Giant's Comments VI―Making Dogma from Zapped Bananas

  3. - Top - End - #3
    Colossus in the Playground
     
    BlackDragon

    Join Date
    Feb 2007
    Location
    Manchester, UK
    Gender
    Male

    Default Re: Excel VBA: run-time error 91

    Quote Originally Posted by Jasdoif View Post
    VBA can be...weird when it comes to variable assignments.
    It's actually quite logical about it: you have to use Set to assign a value to an *object* (like an Excel Range). Let (or nothing at all) is used to assign to ordinary variables.

  4. - Top - End - #4
    Firbolg in the Playground
     
    Jasdoif's Avatar

    Join Date
    Mar 2007
    Location
    Oregon, USA

    Default Re: Excel VBA: run-time error 91

    Quote Originally Posted by factotum View Post
    It's actually quite logical about it: you have to use Set to assign a value to an *object* (like an Excel Range). Let (or nothing at all) is used to assign to ordinary variables.
    OK, I tried re-looking it up, and indeed Set assigns an object reference, and without it assigns a value....And I finally discovered a reference mentioning why the Set keyword exists for VBA: VBA's default property support lets you assign a value to an object and that value gets put into the default property for that object. (For example, you could assign a string to a Range object and the string would get put into the Text property, because that's the default property for Range) And because of that, the language requires a way to differentiate whether the program is trying to set an object or its default property, which is where Set comes in.

    And that explains the "object not set" error; without the Set, it was trying to set the Range variable's default property...but the variable didn't have an object assigned to it yet.

    I still think it's a weird and unintuitive bit of syntax...but at least now I understand the weirdness.
    Feytouched Banana eldritch disciple avatar by...me!

    The Index of the Giant's Comments VI―Making Dogma from Zapped Bananas

  5. - Top - End - #5
    Ettin in the Playground
     
    BardGuy

    Join Date
    Jan 2009

    Default Re: Excel VBA: run-time error 91

    The Set Range code worked, so thank you very much. I think I understand the difference between 'Set x =' and 'x =' now, but even if I don't at least I know to try adding a Set.

    I still had some trouble (I think) with comparing a Double to 0, but I got around that by making a Double and setting it equal to 0, then comparing the two doubles. (This may or may not actually have been the case. Once I get it working, I'll try to remove the extra Double and just compare it directly to 0, and see if that works.)

    Now, though--and I have no idea why--it is saying that I don't have a Do for my Loop. "Compile error: Loop without Do". I tried commenting out my Loop, but then it says I have a Do without a Loop! Any idea why it's no longer seeing the Do While? I'm pretty sure I didn't change anything that should have impacted that.

    Current code:
    Code:
    Private Sub CommandButton1_Click()
    
    Dim ws As String, wValue As String
    Dim rowNum As Integer, colNum As Integer 'sets row and column position
    Dim dCalc As Double, d1 As Double, d2 As Double, dZero As Double 'generic calculation to use for multiple programs
    Dim Rng As Range    'coding note: you must use Set to put a cell's value to this, not simply =.  Ex. Set Rng = Sheets().Cells(,)
    ws = ActiveSheet.Name
    dZero = 0   'set to zero to get around some odd error messages
    
    'for 2 HSAP Results.  This has a range of A2 through D5.
        rowNum = 3
        colNum = 2
        
        If Sheets(ws).Cells(3, 1) > Sheets(ws).Cells(4, 1) Then  'if the current year is on the top row
           Do While colNum < 5
                Set Rng = Sheets(ws).Cells(5, colNum)
                d1 = Sheets(ws).Cells(rowNum, colNum)
                d2 = Sheets(ws).Cells(rowNum + 1, colNum)
                dCalc = Format(d1 - d2, Fixed)
                If dCalc = dZero Then
                    With Selection
                        .NumberFormat = "@"
                        .Interior.ThemeColor = xlThemeColorDark1
                    End With
                    Sheets(ws).Cells(5, colNum) = 0
                If d1 > d2 Then
                    With Selection
                        .NumberFormat = "@"
                        .Interior.Color = 5296274
                    End With
                    wValue = "+" & dCalc
                    Sheets(ws).Cells(5, colNum) = wValue
                ElseIf d1 < d2 Then
                    With Selection
                        .NumberFormat = "@"
                        .Interior.Color = 255
                    End With
                    Sheets(ws).Cells(5, colNum) = dCalc
                Else
                    MsgBox "error: 2 HSAP Results: non-number calculated", , "error message 1"
                End If
                colNum = colNum + 1
                MsgBox "d1 is " & d1 & ". d2 is " & d2 & ". dCalc is " & dCalc, , "debugging report" 'QC check.  Comment this out for final product.
            Loop
        Else      'if the current year is not on the top row
            MsgBox "error: 2 HSAP Results: top row (A3) is not the current year", "error message 2"
        End If
    
    
    End Sub

  6. - Top - End - #6
    Firbolg in the Playground
     
    Jasdoif's Avatar

    Join Date
    Mar 2007
    Location
    Oregon, USA

    Default Re: Excel VBA: run-time error 91

    Quote Originally Posted by JeenLeen View Post
    Code:
            Do While colNum < 5
                Set Rng = Sheets(ws).Cells(5, colNum)
                d1 = Sheets(ws).Cells(rowNum, colNum)
                d2 = Sheets(ws).Cells(rowNum + 1, colNum)
                dCalc = Format(d1 - d2, Fixed)
                If dCalc = dZero Then
                    With Selection
                        .NumberFormat = "@"
                        .Interior.ThemeColor = xlThemeColorDark1
                    End With
                    Sheets(ws).Cells(5, colNum) = 0
                If d1 > d2 Then
                    With Selection
                        .NumberFormat = "@"
                        .Interior.Color = 5296274
                    End With
                    wValue = "+" & dCalc
                    Sheets(ws).Cells(5, colNum) = wValue
                ElseIf d1 < d2 Then
                    With Selection
                        .NumberFormat = "@"
                        .Interior.Color = 255
                    End With
                    Sheets(ws).Cells(5, colNum) = dCalc
                Else
                    MsgBox "error: 2 HSAP Results: non-number calculated", , "error message 1"
                End If
                colNum = colNum + 1
                MsgBox "d1 is " & d1 & ". d2 is " & d2 & ". dCalc is " & dCalc, , "debugging report" 'QC check.  Comment this out for final product.
            Loop
    On the bolded line (If d1 > d2 Then), I think you meant for that to be an ElseIf, not an If. With it as If, the Loop is in a different containing block than the Do, so they can't be matched up.
    Feytouched Banana eldritch disciple avatar by...me!

    The Index of the Giant's Comments VI―Making Dogma from Zapped Bananas

  7. - Top - End - #7
    Ettin in the Playground
     
    BardGuy

    Join Date
    Jan 2009

    Default Re: Excel VBA: run-time error 91

    Edit: spoke too soon. By changing the format from "###.#;(###.#)" to Fixed, I lost the one-decimal point. But changing it back is giving me a type mismatch when I try to calculate dCalc.
    Edit 2: Figured it out. Also updated the code below.

    I figured it was some simple typo like that I just wasn't seeing it even after re-reading the code a couple times.

    I had changed it to If while commenting out the above line.
    Thank you everyone, especially Jasdoif. The code works fine now. (Well, I also had to do Rng.Select to get the highlighting working right, but that was an easy extra fix.) And I checked and that dZero was unnecessary; I must have thought there was an error comparing a Double to 0, when really it was something else getting flagged.

    I'm trying to put a lot of Excel files we use to calculate manually (i.e., do the math and then type it into Excel) through VBA, since I think that now that I have the code as something I can copy & paste, I think that'll be faster and less tedious to modify than typing code into each Excel cell.

    Final code for the sake of recording a correct answer:
    Code:
    Private Sub CommandButton1_Click()
    
    Dim ws As String, wValue As String
    Dim rowNum As Integer, colNum As Integer 'sets row and column position
    Dim dCalc As Double, d1 As Double, d2 As Double 'generic calculation to use for multiple programs
    Dim Rng As Range    'coding note: you must use Set to put a cell's value to this, not simply =.  Ex. Set Rng = Sheets().Cells(,)
    ws = ActiveSheet.Name
    
    'for 2 HSAP Results.  This has a range of A2 through D5.
        rowNum = 3
        colNum = 2
        
        If Sheets(ws).Cells(3, 1) > Sheets(ws).Cells(4, 1) Then  'if the current year is on the top row
           Do While colNum < 5
                Set Rng = Sheets(ws).Cells(5, colNum)
                d1 = Format(Sheets(ws).Cells(rowNum, colNum), "###.#;(###.#)")
                d2 = Format(Sheets(ws).Cells(rowNum + 1, colNum), "###.#;(###.#)")
                dCalc = d1 - d2
                Rng.Select
                If dCalc = 0 Then
                    With Selection
                        .NumberFormat = "@"
                        .Interior.ThemeColor = xlThemeColorDark1
                    End With
                    Sheets(ws).Cells(5, colNum) = " 0.0"
                ElseIf d1 > d2 Then
                    With Selection
                        .NumberFormat = "@"
                        .Interior.Color = 5296274
                    End With
                    wValue = "+" & Format(dCalc, "###.#;(###.#)")
                    Sheets(ws).Cells(5, colNum) = wValue
                ElseIf d1 < d2 Then
                    With Selection
                        .NumberFormat = "@"
                        .Interior.Color = 255
                    End With
                    Sheets(ws).Cells(5, colNum) = Format(dCalc, "###.#;(###.#)")
                Else
                    MsgBox "error: 2 HSAP Results: non-number calculated", , "error message 1"
                End If
                colNum = colNum + 1
                'MsgBox "d1 is " & d1 & ". d2 is " & d2 & ". dCalc is " & dCalc, , "debugging report" 'QC check.  Comment this out for final product.
            Loop
        Else      'if the current year is not on the top row
            MsgBox "error: 2 HSAP Results: top row (A3) is not the current year", , "error message 2"
        End If
    
    
    End Sub
    Last edited by JeenLeen; 2014-04-22 at 02:23 PM.

  8. - Top - End - #8
    Pixie in the Playground
     
    AssassinGuy

    Join Date
    May 2018

    Default Re: Excel VBA: run-time error 91

    i want to share what i found on this website
    www errorsolutions.tech/error/vba-runtime-error-91

    it actually works for me they have step by step guide with pictures

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •