Making "public equation" within a vba function

hfler

Board Regular
Joined
Jun 10, 2008
Messages
95
Hi,

I'll start by saying I'm new to VBA. I'm wondering how I can make a "public equation", similar to a Public variable? So I have a couple of Public variables, call them "Growth" and "Rate". How could I get the equation,

Rate = Application.Exp(Growth/2)

To be available throughout all functions, macro, etc?

Any help is always appreciated.


Thanks,

Hunter
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Code:
Public Growth As Double
Public Rate As Double
Rate = Application.Exp(Growth / 2)

sub myFcn()
'rest of code
 
Upvote 0
Joe, that has to go inside a procedure

Code:
Function dRate(dGrowth As Double) As Double
    dRate = Exp(dGrowth / 2#)
    Debug.Print Rate
End Function

e.g.,

Code:
Debug.Print dRate(1)
 
Upvote 0
agreed with shg, i get the following response

"Compile Error:

Invalid Outside Procedure"

Any knowledge on how to fix this? Still trying to get a formula outside of a procedure


Thanks
 
Upvote 0
VBA formulas go inside VBA procedures. The function I posted accepts the Growth value and returns the Rate.

You don't want a function that uses public variables rather than receiving the parameters it needs to compute.
 
Upvote 0
The Debug.Print inside the function should be deleted.
 
Upvote 0

Forum statistics

Threads
1,224,551
Messages
6,179,476
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top