Modify a constant in VB Project

[innov]

Hello everyone.

I can't seem to gather enough pieces of code thru help and the web to do the following.

I have a defined constant in an EXCEL VBA and I want to change the value in the VBA code itself.

I know it involves using the VBProject object, but I can't find an example to go directly to a variable name and change it's value.

Below helps describe my need

--- VBA code prior to modify ---
DIM xyz as boolean = TRUE

--- code (needed) to modify xyz constant ---
VBProject.Components.Item(xyz) = FALSE

(or something like this...)


Thanks in advance!

innov

 

[fumei]

You are using Dim. Therefore it is not a constant, it is a variable. In which case you just revalue the variable.

DIM xyz as boolean

xyz = True ' set it as true
xyz = False ' set it as false

Constants by definition are constant throughout the execution of the code. After a constant is declared, it cannot be modified or assigned a new value.

Gerry

 

[innov]

Oops. My bad. I actually AM defining a constant.

My actual code is:

Public Const xyz as boolean = true

Can you help me change it?

 

[fumei]

No, I can't. It is a constant, and as stated... after a constant is declared, it cannot be modified or assigned a new value.

Which makes sense, what would be a constant be, if it was not constant?

Gerry

 

[SkipVought]

CONSTANT: Value CANNOT be changed. Value is INVARIABLE!

VARIABLE: Value CAN be changed. Value is CHANGEABLE.


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[innov]

Thanks, I understand all that.

I have a good reason to modify the constant (versus storing it in a worksheet cell).

I' making the assumption that if the VBProject can be used to add and remove modules and code (Self-modifying), that the same capabilities would allow me to change a constant.

Maybe I can just delete the applicable line and re-add with new code?

C'mon, some of you know what I'm talking about...

 

[EBGreen]

Use a variable. If you want to modify it's value there id absolutely no reason to make it a constant.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[innov]

Maybe I'm a little slow here.

I didn't think a variable would maintain a value that survives close/save and re-open. I thought, open opening a workbook, all variables had to be (re-)initialized in VBA code.

If not, please shoe me the sample code.

Thank you!

 

[jebry]

Hi!

It is true that any variable will need to be re-initialized if the workbook is closed but the same is true of constants:

Public Const xyz as boolean = true

is logically the same as:

Public xyz As Boolean combined with

xyz = True.

The only difference is that, in the second case, xyz can be changed. In neither case will xyz retain a value if the workbook is closed or if the code stops running for any other reason.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[EBGreen]

So you want to preserve process state. This is a different matter. To preserve state, you must do this in a different file than the one that you are executing. I'll speak in terms of exe's, but the concepts are the same for Office files. If I execute an exe in process #1, then that process cannot re-write the exe while it is running. As an example, open Notepad then try to delete C:\Winnt\Notepad.exe. To preserve state from one session to another, you need something outside of the executable. This in part is what gave rise to the presence of the registry.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[innov]

Arrrggghhh. Okay, folks. Thanks for your replies. I appreciate your time.

FYI, VBA CAN modify it's own project.

I have seen numerous examples on the web where the VBProject object is used to add or remove code in the project ON THE FLY (such as creating dynamic forms, etc.)


I just wanted to do it to preserve a value (not in a worksheet) that survives, open/save, etc.

 

[Zathras]

innov, you are correct that it is possible to modify the VBA code "on the fly" and there are examples here to show you how it can be done.

Some might consider that "tricky code" however. Self-modifying code is not the easiest for someone to maintain. If you are the only one who will ever use or modify the code, then go right ahead. But if you are a corporate developer and there is a possiblity that someone coming after you will need to maintain the code, you aren't doing him/her any favors that way.

A separate ".ini" file or a registry entry would be a more natural way of saving a variable between runs.

However, I don't understand your reluctance to store a value in one of the worksheets. You can easily place a value in an unused cell before saving and retrieve it after opening (and blank it from the worksheet).

Perhaps if you revealed a little more about why you think it is necessary to use and modify a constant, a better solution can be found.

 

[fumei]

OK, I don't know about Excel, but you CAN store a (sort of) variable in Word that survives closing and opening. Maybe there is something similar in Excel.

Bottom line though is: a constant is a constant. If there is something you want to change..it ain't a constant.

Yes VBA can modify itself by code, on the fly as you put it. And yes, you would use VBAProject properties. However, that is NOT the same as making a constant variable.

Dynamic forms have their changeability written in code. The run time changes are not (usually) changes in code, but results of that code.



Gerry

 

[strongm]

I'll second (or third; I've lost count) the recommendations to use an INI file or the registry to preserve this value.

 

[PHV]

If the workbook may be open on another PC, the .INI or RegValue isn't an option.
I suggest a hidden sheet to store such values preserved upon close/open.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[innov]

Yeah, I think the hidden sheet is the way to go after all.

Thanks to all for chiming in. It's nice to have a community of people so willing to share knowledge.

All the best!

Innov