declaring constants from the sheet?
 

Good morning,

I'm new to VBA so bear with me.

I declare a constant:
Const tier1 = 0.01

which is used in a function:
Case 1 To 499999.99
fee = Assets * tier1

the problem is that this constant is not fixed but needs to be variable.

Is it possible to have the constant declared in a sheet1 and then be
subsequently used in the function in the module?
In other words, can i enter a number in a cell in the sheet and have
that number be used in a function?

Thanks,
RTB


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

declaring constants from the sheet?
 

Public Tier1 as Double

Sub SetTier()
Tier1 = Worksheets("Sheet1").Range("A19").Value
End Sub

Sub ProcessDate
setTier
Assets = worksheets("Data").Range("B12")
Case Assets
Case 1 To 499999.99
fee = Assets * tier1
End Select
msgbox fee & " - " & tier1
End Sub

--
Regards,
Tom Ogilvy



"robert burger" wrote in message
...
Good morning,

I'm new to VBA so bear with me.

I declare a constant:
Const tier1 = 0.01

which is used in a function:
Case 1 To 499999.99
fee = Assets * tier1

the problem is that this constant is not fixed but needs to be variable.

Is it possible to have the constant declared in a sheet1 and then be
subsequently used in the function in the module?
In other words, can i enter a number in a cell in the sheet and have
that number be used in a function?

Thanks,
RTB


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

declaring constants from the sheet?
 

Tom,
did you get my reply?
If not i will post it again.
thanks,
Rob

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

declaring constants from the sheet?
 

Tom,
Thanks for your reply. I took part of your code and incorporated it
into my function as follows:

Public Tier1 As Double

Sub SetTier()
Tier1 = Worksheets("sheet3").Range("a1").Value
tier2 = Worksheets("sheet3").Range("a2").Value
tier3 = Worksheets("sheet3").Range("a3").Value
tier4 = Worksheets("sheet3").Range("a4").Value
tier5 = Worksheets("sheet3").Range("a5").Value
End Sub

Function fee(Assets)
' calculates annual management fee
Select Case Assets
Case 1 To 499999.99
fee = Assets * Tier1
Case 500000 To 999999.99
fee = 500000 * Tier1 + (Assets - 500000) * tier2
Case 1000000 To 1999999.99
fee = 500000 * Tier1 + 500000 * tier2 + (Assets - 1000000)
* tier3
Case 2000000 To 5000000
fee = 500000 * Tier1 + 500000 * tier2 + 1000000 * tier3 + _
(Assets - 2000000) * tier4
Case Is = 5000000
fee = 500000 * Tier1 + 500000 * tier2 + 1000000 * tier3 + _
3000000 * tier4 + (Assets - 5000000) * tier5
End Select
End Function

I'm entering the respective tier's into A1:A5 and in B1 i enter
fee(500000) which gives me 5000 given A1 = 0.01 which is great!!! Thank
You!

Can you direct me to a process that will make this happen by pushing a
button (in the sheet) instead of going back into the VBE and running the
SetTier macro each time the amount changes in A1?

Thanks again,

p.s.
I didn't real understand the following:
Sub ProcessDate
setTier
Assets = worksheets("Data").Range("B12")
Case Assets
Case 1 To 499999.99
fee = Assets * tier1
End Select
msgbox fee & " - " & tier1
End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

declaring constants from the sheet?
 

Public Tier1 As Double
Public Tier2 As Double
Public Tier3 as Double
Public Tier4 as Double
Public Tier5 as Double

Sub SetTier()
Tier1 = Worksheets("sheet3").Range("a1").Value
tier2 = Worksheets("sheet3").Range("a2").Value
tier3 = Worksheets("sheet3").Range("a3").Value
tier4 = Worksheets("sheet3").Range("a4").Value
tier5 = Worksheets("sheet3").Range("a5").Value
End Sub

Function fee(Assets)
' calculates annual management fee
SetTier '<== this makes sure it is current
Select Case Assets
Case 1 To 499999.99
fee = Assets * Tier1
Case 500000 To 999999.99
fee = 500000 * Tier1 + (Assets - 500000) * tier2
Case 1000000 To 1999999.99
fee = 500000 * Tier1 + 500000 * tier2 + (Assets - 1000000)
* tier3
Case 2000000 To 5000000
fee = 500000 * Tier1 + 500000 * tier2 + 1000000 * tier3 + _
(Assets - 2000000) * tier4
Case Is = 5000000
fee = 500000 * Tier1 + 500000 * tier2 + 1000000 * tier3 + _
3000000 * tier4 + (Assets - 5000000) * tier5
End Select
End Function


--
Regards,
Tom Ogilvy

"robert burger" wrote in message
...
Tom,
Thanks for your reply. I took part of your code and incorporated it
into my function as follows:

Public Tier1 As Double

Sub SetTier()
Tier1 = Worksheets("sheet3").Range("a1").Value
tier2 = Worksheets("sheet3").Range("a2").Value
tier3 = Worksheets("sheet3").Range("a3").Value
tier4 = Worksheets("sheet3").Range("a4").Value
tier5 = Worksheets("sheet3").Range("a5").Value
End Sub

Function fee(Assets)
' calculates annual management fee
Select Case Assets
Case 1 To 499999.99
fee = Assets * Tier1
Case 500000 To 999999.99
fee = 500000 * Tier1 + (Assets - 500000) * tier2
Case 1000000 To 1999999.99
fee = 500000 * Tier1 + 500000 * tier2 + (Assets - 1000000)
* tier3
Case 2000000 To 5000000
fee = 500000 * Tier1 + 500000 * tier2 + 1000000 * tier3 + _
(Assets - 2000000) * tier4
Case Is = 5000000
fee = 500000 * Tier1 + 500000 * tier2 + 1000000 * tier3 + _
3000000 * tier4 + (Assets - 5000000) * tier5
End Select
End Function

I'm entering the respective tier's into A1:A5 and in B1 i enter
fee(500000) which gives me 5000 given A1 = 0.01 which is great!!! Thank
You!

Can you direct me to a process that will make this happen by pushing a
button (in the sheet) instead of going back into the VBE and running the
SetTier macro each time the amount changes in A1?

Thanks again,

p.s.
I didn't real understand the following:
Sub ProcessDate
setTier
Assets = worksheets("Data").Range("B12")
Case Assets
Case 1 To 499999.99
fee = Assets * tier1
End Select
msgbox fee & " - " & tier1
End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!