VBA: Variable not defined

Question

I am trying to use the IIF function in the code below but it gives me a Compile error that states that "Variable [is] not defined". It highlights the "Zero" in fifth line of code:

Option Explicit

Sub macro()

Dim ws As Worksheet
With ThisWorkbook.Worksheets("Sheet1")
    MsgBox IIf(ws.Range("A1") = 0, “Zero”, “Nonzero”)
End With

End Sub

I don't know why it's pulling this error as I thought that the range of a cell does not have to be defined. I also tried defining the Range as a variable but that did not resolve it either.

What would be the problem here?

The With block has no effect here. Remove the ws declaration, and replace ws.Range with .Range. — Mathieu Guindon
– Mathieu Guindon, Commented May 26, 2016 at 15:14

user3598756 · Accepted Answer · 2016-05-26 15:27:01Z

4

because you're missing to set ws

so either it has to be ThisWorkbook.Worksheets("Sheet1") itself and so go like this:

Option Explicit

Sub macro()

With ThisWorkbook.Worksheets("Sheet1")
    MsgBox IIf(.Range("A1") = 0, "Zero", "Nonzero") '<~~ '.Range("A1")' implies that the object following the 'With' keyword is assumed to be just before the dot 
End With

or it has to be a different sheet, and then go like this:

Option Explicit

Sub macro()

Dim ws As Worksheet
set ws =ThisWorkbook.Worksheets("Sheet2") '<~~ set the "new" worksheet
With ThisWorkbook.Worksheets("Sheet1")
    MsgBox IIf(ws.Range("A1") = 0, "Zero", "Nonzero")
End With

End Sub

edited May 26, 2016 at 15:27

answered May 26, 2016 at 15:05

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user112947 Over a year ago

Thanks. But I just don't understand why defining ws and then using ws.Range did not do the trick. Is it because I used the with statement where I refer to the worksheet?

user112947 Over a year ago

Please ignore the above comment. I reread your answer and I understand. Thank you for clarifying.

user3598756 Over a year ago

with Dim ws As Worksheet you only declared a variable named ws being of type Worksheet. but you didn't initialize it, so VBA doesn't know what actual object ws should reference. if you add set ws =ThisWorkbook.Worksheets("Sheet2") statement, then the ws variable is initialized to reference a real object (i.e. the Worksheet named "Sheet2" in the Workbook where the macro resides in)

user112947 Over a year ago

I've edited my code but despite the change there is still the same Compile error where it states that the variable is not defined. The error highlights the 'Zero', which is the second parameter in the IIF function. I don't have to define "Zero", would I?

user3598756 Over a year ago

look for double quotes: “ vs ". this a deadly annoying issue. see edited answer

|

Collectives™ on Stack Overflow

VBA: Variable not defined

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related