Declare constant as a cell value

[xenedra13]

I need to declare a lot of constants as strings made up of whatever is in the cell contents of a bunch of cells. The value of the cells in one particular sheet contain references to a lot of different workbooks, worksheets, and cell ranges. I need to declare each one of those as variables, but the variables need to stay the same whenever the other workbooks are active. I have tried

Const WLD As String = Range("").Value

, but this reference changes depending on which workbook is open.

Const WLD As String = Workbooks("").Worksheets("").Range("").Value

This doesn't work either. It gives me a "Compile Error: Constant expression required".

Const WLD As String = "Workbooks("""").Worksheets("""").Range("""").Value"

This also doesn't work. It just declares the constant as exactly what's in the quotes and not the cell contents. How can I get this to work?

 

[Zack Barresse]

Hi there,

It doesn't work like that. You can't declare a constant as something which needs to be set. This is because constants are loaded upon compiling, not during run-time, which is when you would need to set the reference. You'll have to find another way to accomplish what you want. Can I ask why you're trying to do this? There's probably a better way, but I can't give you any information since you haven't described what you have, or what you want to do with it.

 

[Rick Rothstein]

No, you cannot assign an expression to a constant. The reason is that <acronym title="vBulletin">VB</acronym> physically substitutes the value of the constant for the constant's name throughout your code before it compiles the code, so it cannot evaluate expression, methods, functions, etc. in order to assign in a Const statement because no code has executed yet at that time in the process.

 

[Zack Barresse]

Oh potatoes potatoes, Rick.

 

[Rick Rothstein]

Oh potatoes potatoes, Rick.

 

[Zack Barresse]

Before compile, during, potato potato (po-tae-toe, po-tah-toe).

 

[Rick Rothstein]

Before compile, during, potato potato (po-tae-toe, po-tah-toe).

Oh, I was not "correcting" you with my post.. your message was not visible in my reader when I viewed the message... I had remembered that I posted a response to a similar question...

http://www.mrexcel.com/forum/excel-questions/801011-assign-expression-constant.html#post3916504

and spent a few minutes finding it, then copy/pasted it into my response... only then did I see you had posted your answer. I noted they were similar, but figured the different wording would help the OP to understand better, so I did not delete my already posted message.

 

[xenedra13]

So are you saying that if I moved the declaration into the sub and didn't make them constants, that it would work? I have a page of references that are constantly changing and need to be able to be edited by a user without having to go into the VBE to edit it. I am not stuck on the idea of putting them all on one worksheet, it just seemed to be the easiest way to put all the references in one place. There are quite a few of them. They include references to workbooks, worksheets, file paths, cell ranges, and what month it is (drop down box). Any ideas on how to achieve this?