Variable not defined for "number of trials" ?

V

velocityinc

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
 
R

Rick Rothstein

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.
 
V

velocityinc

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 isa
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)





















- 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
 
R

Rick Rothstein

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)


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)

















- 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top