changing constants

[electricbluelady]

HI Everyone,
When billing every month there are 'constants' that change. Currently, I am
trying to design a way where a user does not have to go into the program
every month to change 'constants'. Is there a way to refer to a cell as a
'constant'? This is what I have so far....

Const D37 as Long = 10000.
Next month D37 may be 11000. The number is entered into the spreadsheet, but
the macro runs formulas off of the declared constant in the macro.

Thanks again.

 

[RyanH]

I would do this. I'm not sure how many constants you have, so I did six.
Replace "Sheet1" with the Worksheet Name the constants are located in and
change the "A1" with the cell the constant is located in.

Then replace your old numbers in the macros with the constant variables I
listed below.

Sub Macro1()

Dim constant1 As Range
Dim constant2 As Range
Dim constant3 As Range
Dim constant4 As Range
Dim constant5 As Range
Dim constant6 As Range

constant1 = Sheets("Sheet1").Range("A1").Value
constant2 = Sheets("Sheet1").Range("A2").Value
constant3 = Sheets("Sheet1").Range("A3").Value
constant4 = Sheets("Sheet1").Range("A4").Value
constant5 = Sheets("Sheet1").Range("A5").Value
constant6 = Sheets("Sheet1").Range("A6").Value

'the rest of your code here

End Sub

 

[Gary''s Student]

Let's say the value for D37 is already being entered in Sheet1, cell D37. In
VBA:

Sub dural()
Dim D37 As Long
D37 = Sheets("Sheet1").Range("D37").Value
End Sub

 

[Jim Thomlinson]

Your question is how to change constants. But if they are changing then they
are not constants. Constants are used for holding values that you never
expect to change. Pi is a contant. The number of hours in a day is a contant.
If you reasonably expect the value to change then store it in a user
accessable location in your spreadsheet and then read that value at run time,
similar to Ryan and Gary's suggestions.