declaring constants from the sheet?

[robert burger]

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!

 

[Tom Ogilvy]

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

 

[robert burger]

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!

 

[robert burger]

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!

 

[Tom Ogilvy]

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