Excel VBA: run-time error 91

[JeenLeen]

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


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:


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:



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.



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; i@@@ = calculated #s; wValue = i@@@@ 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

[Jasdoif]

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 errorHave you tried
Set Rng = Sheets(ws).Cells(5, colNum)?

A quick test with

Sub Test()
Dim Rng As Range
Rng = Cells(3, 1)
MsgBox (Rng.Value)
End Sub gives me the error 91, while


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.

[factotum]

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.

[Jasdoif]

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 (http://msdn.microsoft.com/en-us/library/aa192490.aspx) 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.

[JeenLeen]

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:


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

[Jasdoif]

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.
LoopOn 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.

[JeenLeen]

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. :smallsigh:
Edit 2: Figured it out. Also updated the code below.

I figured it was some simple typo like that :smallsmile: 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:


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

[WendyRussell858]

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