Calling sub procedure
 

Hi,
I have the following code, that fails on the line where I call the sub
procedure from the Worsheet_Activate procedure. The error is 1004 "The macro
Calculate_AC cannot be found"
Why can't it find the macro?
(The CalcDodgeBonus() and CalcArmorBonus() are functions that also exist and
work on the sheet)

thanks

Private Sub Worksheet_Activate()
Application.Run "Calculate_AC"

End Sub

Private Sub Calculate_AC()
Dim BaseArmorClass As Integer
Dim DexterityBonus As Integer
Dim SizeArmor As Double
Dim DodgeBonus As Integer
Dim WornArmorBonus As Integer
Dim size As Range

Set size = ThisWorkbook.Names("Size").RefersToRange
BaseArmorClass = 10
DexterityBonus = Range("F9").Value
SizeArmor = Application.WorksheetFunction.Match(Range("I30").V alue,
size, 0) - 3
DodgeBonus = CalcDodgeBonus()
WornArmorBonus = CalcArmorBonus()

ArmorClass = BaseArmorClass + DexterityBonus + SizeArmor + DodgeBonus
Range("C26").Value = ArmorClass
End Sub

Calling sub procedure
 

Hi,

Why not try replacing application.run, with just Call?

Ashman

"Yossi" wrote:

Hi,
I have the following code, that fails on the line where I call the sub
procedure from the Worsheet_Activate procedure. The error is 1004 "The macro
Calculate_AC cannot be found"
Why can't it find the macro?
(The CalcDodgeBonus() and CalcArmorBonus() are functions that also exist and
work on the sheet)

thanks

Private Sub Worksheet_Activate()
Application.Run "Calculate_AC"

End Sub

Private Sub Calculate_AC()
Dim BaseArmorClass As Integer
Dim DexterityBonus As Integer
Dim SizeArmor As Double
Dim DodgeBonus As Integer
Dim WornArmorBonus As Integer
Dim size As Range

Set size = ThisWorkbook.Names("Size").RefersToRange
BaseArmorClass = 10
DexterityBonus = Range("F9").Value
SizeArmor = Application.WorksheetFunction.Match(Range("I30").V alue,
size, 0) - 3
DodgeBonus = CalcDodgeBonus()
WornArmorBonus = CalcArmorBonus()

ArmorClass = BaseArmorClass + DexterityBonus + SizeArmor + DodgeBonus
Range("C26").Value = ArmorClass
End Sub

Calling sub procedure
 

Run requires the subroutine to be in a standard module. Either use
just

Private Sub Worksheet_Activate()
Calculate_AC
End Sub

or move that routine.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,
I have the following code, that fails on the line where I call the sub
procedure from the Worsheet_Activate procedure. The error is 1004 "The macro
Calculate_AC cannot be found"
Why can't it find the macro?
(The CalcDodgeBonus() and CalcArmorBonus() are functions that also exist and
work on the sheet)

thanks

Private Sub Worksheet_Activate()
Application.Run "Calculate_AC"

End Sub

Private Sub Calculate_AC()
Dim BaseArmorClass As Integer
Dim DexterityBonus As Integer
Dim SizeArmor As Double
Dim DodgeBonus As Integer
Dim WornArmorBonus As Integer
Dim size As Range

Set size = ThisWorkbook.Names("Size").RefersToRange
BaseArmorClass = 10
DexterityBonus = Range("F9").Value
SizeArmor = Application.WorksheetFunction.Match(Range("I30").V alue,
size, 0) - 3
DodgeBonus = CalcDodgeBonus()
WornArmorBonus = CalcArmorBonus()

ArmorClass = BaseArmorClass + DexterityBonus + SizeArmor + DodgeBonus
Range("C26").Value = ArmorClass
End Sub