Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error:Variable not defined
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error:Variable not defined
On Saturday, May 19, 2012 11:57:11 AM UTC-4, Seth J. Turok wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error:Variable not defined
"Seth J. Turok" wrote:
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error:Variable not defined
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile Error:Variable not defined
Errata....
"joeu2004" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compile Error: Variable Not Defined | Excel Programming | |||
Compile Error: variable not defined | Excel Programming | |||
Variable not Defined Compile error | Excel Programming | |||
Variable not defined compile error | Excel Programming | |||
Compile error, variable not defined | Excel Programming |