Terrifying Error in VBA! Need Help!

[Cyb3rT3k]

I just found this kind of error that is driving me crazy. I was writing a module and didn't understand why the codes would still going into the loop statements even though I was positive that I shouldn't at all. Then I found out that I had an undefined variable (due to typo) in my condition for IF-ELSE statement checking if it equals to zero. VBA would take this as a true statement and go into the loop. To make it easier to understand, let me write out a sample codes:

Sub test()

Dim num As Integer

num = 1

If numb = 0 Then
MsgBox ("Something")
Else:
MsgBox ("Nothing")
End If

End Sub


As you can see above, I mispelled the variable num, and checking numb = 0 instead even though numb is never defined. In this case, VBA supposed to pop up a message "Nothing" since I mispelled the variable, but it still takes numb = 0 as a true statement and displays "Something".

What I'm afraid is when I write a very very long module and this kind of problem happens, it would mess up my data even without me knowing it since the codes will run smoothly. Is there any idea to eliminate this kind of problem? Maybe a function to check all of the variables are being defined on the top of the sub? Please help me out, very appreciate!

 

[DJkarl]

At the top of your code module type in Option Explicit, then if you have any undeclared variables you will get a compile error.

 

[KenHigg]

I think if you use the explict option and compile, these should jump out - ?

 

[pbaldy]

I agree, and would further recommend going into Tools/Options (in the VBA editor) and checking the box "Require Variable Declaration" which will automatically place Option Explicit at the top of every new module created (you'll still have to add it to existing modules).

 

[KenHigg]

Beat me to the punch

ken

 

[Cyb3rT3k]

wow... so simple but works very well! Thanks a lot!