Compile Error:Variable not defined

[Seth J. Turok]

I am an ultra novice and am trying to teach myself VBA using "Excel
VBA Programming for Dummies". I have attempted to insert some of the
sample code provided in the book. I appear to get the same error
message when I execute the code, “compile error: variable not
defined”. The two examples I have used are below. I was hoping you
might be able to tell me what I am doing incorrectly. Thank you.

EXAMPLE 1

Sub GuessName()
Msg = "Is your name " & Application.UserName & "?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then MsgBox "Oh, never mind."
If Ans = vbYes Then MsgBox "I must be clairvoyant!"
End Sub

Example 2

Answer = MsgBox("Convert formulas to values?", vbYesNo)
If Answer <> vbYes Then Exit Sub

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

 

[James Ravenswood]

I am an ultra novice and am trying to teach myself VBA using "Excel
VBA Programming for Dummies". I have attempted to insert some of the
sample code provided in the book. I appear to get the same error
message when I execute the code, “compile error: variable not
defined”. The two examples I have used are below. I was hoping you
might be able to tell me what I am doing incorrectly. Thank you.

EXAMPLE 1

Sub GuessName()
Msg = "Is your name " & Application.UserName & "?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then MsgBox "Oh, never mind."
If Ans = vbYes Then MsgBox "I must be clairvoyant!"
End Sub

Example 2

Answer = MsgBox("Convert formulas to values?", vbYesNo)
If Answer <> vbYes Then Exit Sub

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

make sure you try them in molules:



Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[joeu2004]

I appear to get the same error message when I execute
the code, “compile error: variable not defined”. [....]
Sub GuessName()
Msg = "Is your name " & Application.UserName & "?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then MsgBox "Oh, never mind."
If Ans = vbYes Then MsgBox "I must be clairvoyant!"
End Sub

My guess is: you have an Option Explicit near the top of the module.

That is a good programming practice. But it does mean that you must declare
every variable explicitly. For example:

Sub GuessName()
Dim Msg As String, Ans As String

It is good programming practice to use the "closest" (best) date type for
each variable. Your code will execute more efficiently.

But if you are not sure what type to use, you can get away with the As
Variant. If you omit the words "As Variant", that is the default type.

 

[Vacuum Sealed]

Hi Seth

You have to Dim ( which is kinda like Declaring ) what each string,
range, variant is.

1st Example would read something like:

Sub GuessName()

Dim ans As Integer

ans = MsgBox("Is your name " & Application.UserName & "?", vbYesNo)
If ans = vbNo Then
MsgBox ("Oh, never mind.")
Else
MsgBox ("I must be clairvoyant!")
End If

End Sub


2nd example will replace any formulas in cells within the range you
specify with their actual values.

Again, we Dim what it is we are pointing to eg, the objects and ranges.

Sub ConvertFormula_2()

Dim mySht As Worksheet
Dim myFormulaRange As Range, fCell As Range
Dim answer As Integer

Set mySht = Sheets("Sheet1") 'çhange name to your sheet name.
Set myFormulaRange = mySht.Range("A1:A2") 'çhange range to suit.

answer = MsgBox("Convert formulas to values?", vbYesNo)
If answer <> vbYes Then Exit Sub

For Each fCell In myFormulaRange
If fCell <> "" Then

With fCell
.Value = fCell
End With
End If
Next

End Sub

Now, my terminology I use may not be 100% accurate, but hopefully it
will point you in the right direction.

One of the many Guru's will no doubt correct any ambiguous terminology
mistakes I have made....

HTH
Mick.

 

[joeu2004]

Errata....

My guess is: you have an Option Explicit near the top
of the module. [....] But it does mean that you must
declare every variable explicitly. For example:
Sub GuessName()
Dim Msg As String, Ans As String

Of course, that should Ans As Long.

PS: Always use Long instead of Integer or Byte, unless you are allocating a
huge array. The point is: for individual variables, there is no longer any
advantage to using smaller integer types; and more often than not, there is
a disadvantage.

Similarly, always use Double instead of Single.