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").Value,
size, 0) - 3
DodgeBonus = CalcDodgeBonus()
WornArmorBonus = CalcArmorBonus()

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

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").Value,
> size, 0) - 3
> DodgeBonus = CalcDodgeBonus()
> WornArmorBonus = CalcArmorBonus()
>
> ArmorClass = BaseArmorClass + DexterityBonus + SizeArmor + DodgeBonus
> Range("C26").Value = ArmorClass
> End Sub

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 <[email protected]>,
[email protected] 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").Value,
> size, 0) - 3
> DodgeBonus = CalcDodgeBonus()
> WornArmorBonus = CalcArmorBonus()
>
> ArmorClass = BaseArmorClass + DexterityBonus + SizeArmor + DodgeBonus
> Range("C26").Value = ArmorClass
> End Sub
>