Setting "Const" equal to a cell reference in VBA

[carstenp]

Howdy,

I have a setup where I've written several functions referencing variables that I have hardcoded in

Const SmallDP As Double = 6.5

but I'd like to set up something like this

Dim SmallDP As Double
SmallDP = Thisworkbook.worksheets("Input").cells(15,8)

Is there a way to do this without running a subroutine?

 

[JoeMo]

Not sure I understand what you mean by w/o running a subroutine. You can use the Dim statement you posted directly in a function.

 

[BiocideJ]

I think what you are trying to do is to set a global variable through the VBA compiler using an Excel sheet value.

As far as I know, this is not possible and if there is some solution out there it would be very wonky indeed.
That being said, what is the purpose of setting a CONST or global variable if you don't want to run a SUB/FUNCTION to use it.

Perhaps some more detail might clarify the situtation.

 

[carstenp]

I think what you are trying to do is to set a global variable through the VBA compiler using an Excel sheet value.

As far as I know, this is not possible and if there is some solution out there it would be very wonky indeed.
That being said, what is the purpose of setting a CONST or global variable if you don't want to run a SUB/FUNCTION to use it.

Perhaps some more detail might clarify the situtation.

That is what I was trying to do. I've got over 100 custom functions that reference the constants and rather than either hardcoding them in VBA every time they change or adding another variable in the equation where I link to cell H15 on the input page I thought I could just set VBA to pull from there, but it doesn't sound like that's possible. Thanks for the responses

 

[Kyle123]

You could use named ranges to contain the values and just reference them in your code