Change a const value in VBA from a cell formula value.

[PAWSflash]

I have been given a spreadsheet to modify,(original author has retired) it has an object that you click and it either inserts a value of 0 or the "Standardweight of 216 lbs.

I need to modify the code to reference a cell value eg. in cell C1 the following formula A1-B1 = (required value for the Const value in the below code)

" Option Explicit
Public std_wt As Integer
Const StandardWeight = 216
Const sw$ = "216"

Many thanks for any assistance

 

[RoryA]

A constant is, by definition, constant not dependent on some other value. You would have to change your constant to a variable (or use the cell address instead and adjust your code to refer to that range) and then you need a routine that initialises the value of that variable.

 

[Reggie74]

You will have to change the following ..

const StandardWeight = Range("C1").Value

Just make sure that the vba code does not attempt to change the value of StandardWeight during execution as this will give an error

I have been given a spreadsheet to modify,(original author has retired) it has an object that you click and it either inserts a value of 0 or the "Standardweight of 216 lbs.

I need to modify the code to reference a cell value eg. in cell C1 the following formula A1-B1 = (required value for the Const value in the below code)

" Option Explicit
Public std_wt As Integer
Const StandardWeight = 216
Const sw$ = "216"

Many thanks for any assistance

 

[PAWSflash]

Thank you that is proving the tricky part for me. I have been through my Mr EXCEL VBA code disk and not having much luck. But you have put me on new direction. Thanks

 

[PAWSflash]

Thanks Reggie 74 Have tried that but code doesn't run I will keep working on it. Thanks

 

[Rick Rothstein]

You will have to change the following ..

const StandardWeight = Range("C1").Value

That won't work. The Const keyword is short for "constant"... and <acronym title="vBulletin">VB</acronym> means it... only constant can be assign to the constant's name. As I understand it, at compile time but before the actual compile itself, <acronym title="vBulletin">VB</acronym> substitutes the value to the right of the equal sign for the constant's name everywhere it appears in code (within the scope for the declared constant). So, because nothing has been compiled at that time, you cannot assign anything that requires being evaluated... this eliminates variable and functions from being used in a Const statement (another constant that is declared earlier can be used though).

 

[PAWSflash]

Thanks Rick

I will look at creating what I require before I get to this point.

Thanks