| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable not defined for "number of trials" ?
Says variable not defined for number of trials for some reason - any
way to correct this? Private Sub MyFunction() ' this variable holds the number of trials in the simulation NumberOfTrials = 500 ' start the simulation with the desired number of trials RiskAMP_BeginSteppedSimulation (NumberOfTrials) ' use a loop to run the simulation for the same number of trials For i = 1 To NumberOfTrials ' any VBA code can be run here; in this example, we do ' a simple calculation on some spreadsheet values. Sheets("Interest").Select Range("E28:DI28").Select Selection.Copy Range("E29").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("G5").Select ActiveCell.FormulaR1C1 = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Principal").Select Range("E28:DI28").Select Selection.Copy Range("E29").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("G5").Select ActiveCell.FormulaR1C1 = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Leasing Analysis").Select Range("A2").Select ' this call advances the simulation by one trial RiskAMP_IterateSimulationStep Next i ' when the loop is complete, finish the simulation RiskAMP_FinishSteppedSimulation End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not defined for "number of trials" ?
My guess at why you are getting this message is because you have your system
configured to add Option Explicit to the top of each code window. This is a good thing! However, it does require you to declare (using the Dim statement) each variable you use in your code (also a good thing to do). So, you need to add this... Dim NumberOfTrials As Long (I took a guess, based on the name, that this variable will never be assigned a floating point value, otherwise I would have used Single or Double instead of Long in the Dim statement.) By the way, the main benefit of using Option Explicit is so you can be alerted when you misspell a variable name later on in your coding. For example, if you declare a variable as AlertMe, assign values to it and later on when you go to use it and accidentally misspell it A1ertMe (that is a one instead of an lower case L) later on, you won't spend days tracking down the error. -- Rick (MVP - Excel) wrote in message ... Says variable not defined for number of trials for some reason - any way to correct this? Private Sub MyFunction() ' this variable holds the number of trials in the simulation NumberOfTrials = 500 ' start the simulation with the desired number of trials RiskAMP_BeginSteppedSimulation (NumberOfTrials) ' use a loop to run the simulation for the same number of trials For i = 1 To NumberOfTrials ' any VBA code can be run here; in this example, we do ' a simple calculation on some spreadsheet values. Sheets("Interest").Select Range("E28:DI28").Select Selection.Copy Range("E29").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("G5").Select ActiveCell.FormulaR1C1 = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Principal").Select Range("E28:DI28").Select Selection.Copy Range("E29").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("G5").Select ActiveCell.FormulaR1C1 = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Leasing Analysis").Select Range("A2").Select ' this call advances the simulation by one trial RiskAMP_IterateSimulationStep Next i ' when the loop is complete, finish the simulation RiskAMP_FinishSteppedSimulation End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not defined for "number of trials" ?
On Aug 31, 10:42*pm, "Rick Rothstein"
wrote: My guess at why you are getting this message is because you have your system configured to add Option Explicit to the top of each code window. This is a good thing! However, it does require you to declare (using the Dim statement) each variable you use in your code (also a good thing to do). So, you need to add this... Dim NumberOfTrials As Long (I took a guess, based on the name, that this variable will never be assigned a floating point value, otherwise I would have used Single or Double instead of Long in the Dim statement.) By the way, the main benefit of using Option Explicit is so you can be alerted when you misspell a variable name later on in your coding. For example, if you declare a variable as AlertMe, assign values to it and later on when you go to use it and accidentally misspell it A1ertMe (that is a one instead of an lower case L) later on, you won't spend days tracking down the error. -- Rick (MVP - Excel) wrote in message ... Says variable not defined for number of trials for some reason - any way to correct this? Private Sub MyFunction() * *' this variable holds the number of trials in the simulation * *NumberOfTrials = 500 * *' start the simulation with the desired number of trials * *RiskAMP_BeginSteppedSimulation (NumberOfTrials) * *' use a loop to run the simulation for the same number of trials * *For i = 1 To NumberOfTrials * * * *' any VBA code can be run here; in this example, we do * * * *' a simple calculation on some spreadsheet values. * *Sheets("Interest").Select * *Range("E28:DI28").Select * *Selection.Copy * *Range("E29").Select * *Range(Selection, Selection.End(xlDown)).Select * *Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ * * * *False, Transpose:=False * *Application.CutCopyMode = False * *Selection.Copy * *Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ * * * *:=False, Transpose:=False * *Range("G5").Select * *ActiveCell.FormulaR1C1 = "=NOW()" * *Selection.Copy * *Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ * * * *:=False, Transpose:=False * *Sheets("Principal").Select * *Range("E28:DI28").Select * *Selection.Copy * *Range("E29").Select * *Range(Selection, Selection.End(xlDown)).Select * *Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ * * * *False, Transpose:=False * *Application.CutCopyMode = False * *Selection.Copy * *Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ * * * *:=False, Transpose:=False * *Range("G5").Select * *ActiveCell.FormulaR1C1 = "=NOW()" * *Selection.Copy * *Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ * * * *:=False, Transpose:=False * * * * * *Sheets("Leasing Analysis").Select * *Range("A2").Select * * * *' this call advances the simulation by one trial * * * *RiskAMP_IterateSimulationStep * *Next i * *' when the loop is complete, finish the simulation * *RiskAMP_FinishSteppedSimulation End Sub- Hide quoted text - - Show quoted text - Solved one problem - but now this.....it highlights - RiskAMP_BeginSteppedSimulation (NumberOfTrials) and says, Compile error, expected sub, function, or property.......how do I correct. Please advise. Private Sub MyFunction() ' this variable holds the number of trials in the simulation Dim NumberOfTrials As Long NumberOfTrials = 1500 ' start the simulation with the desired number of trials Dim RiskAMP_BeginSteppedSimulation As Long RiskAMP_BeginSteppedSimulation (NumberOfTrials) ' use a loop to run the simulation for the same number of trials For i = 1 To NumberOfTrials ' any VBA code can be run here; in this example, we do ' a simple calculation on some spreadsheet values. Sheets("Interest").Select Range("E28:DI28").Select Selection.Copy Range("E29").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("G5").Select ActiveCell.FormulaR1C1 = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Principal").Select Range("E28:DI28").Select Selection.Copy Range("E29").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("G5").Select ActiveCell.FormulaR1C1 = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Leasing Analysis").Select Range("A2").Select ' this call advances the simulation by one trial RiskAMP_IterateSimulationStep Next i ' when the loop is complete, finish the simulation RiskAMP_FinishSteppedSimulation End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Variable not defined for "number of trials" ?
Give that this line declares RiskAMP_BeginSteppedSimulation as a variable of
type Long... Dim RiskAMP_BeginSteppedSimulation As Long What is this line supposed to be doing? RiskAMP_BeginSteppedSimulation (NumberOfTrials) In other words, what do you *think* the NumberOfTrials variable in parentheses is doing? -- Rick (MVP - Excel) wrote in message ... On Aug 31, 10:42 pm, "Rick Rothstein" wrote: My guess at why you are getting this message is because you have your system configured to add Option Explicit to the top of each code window. This is a good thing! However, it does require you to declare (using the Dim statement) each variable you use in your code (also a good thing to do). So, you need to add this... Dim NumberOfTrials As Long (I took a guess, based on the name, that this variable will never be assigned a floating point value, otherwise I would have used Single or Double instead of Long in the Dim statement.) By the way, the main benefit of using Option Explicit is so you can be alerted when you misspell a variable name later on in your coding. For example, if you declare a variable as AlertMe, assign values to it and later on when you go to use it and accidentally misspell it A1ertMe (that is a one instead of an lower case L) later on, you won't spend days tracking down the error. -- Rick (MVP - Excel) wrote in message ... Says variable not defined for number of trials for some reason - any way to correct this? Private Sub MyFunction() ' this variable holds the number of trials in the simulation NumberOfTrials = 500 ' start the simulation with the desired number of trials RiskAMP_BeginSteppedSimulation (NumberOfTrials) ' use a loop to run the simulation for the same number of trials For i = 1 To NumberOfTrials ' any VBA code can be run here; in this example, we do ' a simple calculation on some spreadsheet values. Sheets("Interest").Select Range("E28:DI28").Select Selection.Copy Range("E29").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("G5").Select ActiveCell.FormulaR1C1 = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Principal").Select Range("E28:DI28").Select Selection.Copy Range("E29").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("G5").Select ActiveCell.FormulaR1C1 = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Leasing Analysis").Select Range("A2").Select ' this call advances the simulation by one trial RiskAMP_IterateSimulationStep Next i ' when the loop is complete, finish the simulation RiskAMP_FinishSteppedSimulation End Sub- Hide quoted text - - Show quoted text - Solved one problem - but now this.....it highlights - RiskAMP_BeginSteppedSimulation (NumberOfTrials) and says, Compile error, expected sub, function, or property.......how do I correct. Please advise. Private Sub MyFunction() ' this variable holds the number of trials in the simulation Dim NumberOfTrials As Long NumberOfTrials = 1500 ' start the simulation with the desired number of trials Dim RiskAMP_BeginSteppedSimulation As Long RiskAMP_BeginSteppedSimulation (NumberOfTrials) ' use a loop to run the simulation for the same number of trials For i = 1 To NumberOfTrials ' any VBA code can be run here; in this example, we do ' a simple calculation on some spreadsheet values. Sheets("Interest").Select Range("E28:DI28").Select Selection.Copy Range("E29").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("G5").Select ActiveCell.FormulaR1C1 = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Principal").Select Range("E28:DI28").Select Selection.Copy Range("E29").Select Range(Selection, Selection.End(xlDown)).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("G5").Select ActiveCell.FormulaR1C1 = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Leasing Analysis").Select Range("A2").Select ' this call advances the simulation by one trial RiskAMP_IterateSimulationStep Next i ' when the loop is complete, finish the simulation RiskAMP_FinishSteppedSimulation End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I acess the printer variable "number of copies"? | Excel Worksheet Functions | |||
| How do I access the printer variable "number of copies"? | Excel Worksheet Functions | |||
| "Variable not defined" error for form that does not initially exist... | Excel Programming | |||
| use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming | |||
Hybrid Mode
