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

PAWSflash

New Member
Joined
Oct 7, 2014
Messages
4
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,523
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Sep 26, 2014
Messages
51
You will have to change the following ..
Code:
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

New Member
Joined
Oct 7, 2014
Messages
4
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

New Member
Joined
Oct 7, 2014
Messages
4

ADVERTISEMENT

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,337
Office Version
  1. 2010
Platform
  1. Windows
You will have to change the following ..
Code:
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

New Member
Joined
Oct 7, 2014
Messages
4
Thanks Rick

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

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,330
Members
409,863
Latest member
stacy09
Top