Need VBA help with "Compile error: Variable not defined"

[Mark Carlson]

This is my first post. Greetings from Athens, TX!

I've used Excel for many years, but finally am learning VBA, using Excel Progamming for Dummies by John Walkenbach. Excel is version 2000.

I've searched extensively on Google and on the MrExcel site for the solution to my problem, but still haven't found it . Any direction would be most appreciated.

I'm trying to execute the examples in the book, and have gotten the error message "Compile error: Variable not defined" with "MyString =" highlighted with several of the examples. Could someone please tell me where I'm going astray?

Following is the latest subroutine I typed into the module, from page 124:

Option Explicit

Sub GetLength()
MyString = "Hello World"
StringLength = Len(MyString)
MsgBox StringLength
End Sub

Thank you for your help!

Mark Carlson

 

[VoG]

Try

Sub GetLength()
Dim MYstring As String
MYstring = "Hello World"
StringLength = Len(MYstring)
MsgBox StringLength
End Sub

 

[Mark Carlson]

Thanks, but the same error message is here, and "StringLength =" is still highlighted.

Mark

 

[Greg Truby]

Welcome to the board.

Well, yes. When you googled, did you search on the term "option explicit"? That forces variable declarations.

Option Explicit
 
Sub GetLength()
    dim strMyString as string, lngStringLength as long
    strMyString = "Hello World"
    lngStringLength = Len(strMyString)
    MsgBox lngStringLength
End Sub

 

[VoG]

Oops!

Sub GetLength()
Dim MYstring As String, StringLength As Integer
MYstring = "Hello World"
StringLength = Len(MYstring)
MsgBox StringLength
End Sub

The reason that you are getting these errors is that you have set 'Require variable declaration' in Tools > Options which is a very good idea. Shame Walchenbach et al. didn't think of that!

 

[Mark Carlson]

Greg,

I didn't Google "Option Explicit" (OE). The book suggested using OE as a matter of course. I've seen that stated several other places while trying to figure this out.

Thanks, Mark

 

[Mark Carlson]

What to do?

Thanks, Mark

 

[Greg Truby]

What to do?

Declare your variables. (= appear in a DIM statement prior to usage)

 

[Mark Carlson]

Greg,

I'm afraid I'm not quite getting this. Do you mean I need an "=" in the dim statement? Also in your signature you have the hint about CODE tags. Would that be 010, 020, etc at the start of the lines?

Have a great evening!

Thanks again, Mark

 

[RoryA]

Option Explicit requires you to declare all your variables. In your code, you use two variables, MyString and StringLength, so you have to declare them:

Dim MyString As String, StringLength as Long

(note, you don't have to declare them as having a specific type/interface, but it's good practice to do so where possible)
The code tags are what I used above to make the code line stand out.
Regards,
Rory