Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Declaring variables | Excel Discussion (Misc queries) | |||
Declaring arrays | Excel Programming | |||
Declaring a DLL | Excel Programming | |||
DEclaring variables | Excel Programming | |||
DEclaring variables | Excel Programming |