Using a Global Variable in a formula, in a cell, in a Worksheet

**lovecolorado** · 03-18-2019, 02:04 PM

Is there a way I can use the value of a global variable, e.g., the global variable Chapter

Please Login or Register  to view this content.

in a formula that appears in one of the cells of one of my worksheets? I can access the value of these variables in any procedure or function in any worksheet, but I don't see how to access the variables' values in my formulas in cells.

I currently have working VBA code that assigns a value to the cell of a worksheet. As long as I fully reference the cell, any formulas in any spreadsheet can read the value of a cell that was assigned its value by a VBA assignment.
The problem with this implementation is remembering what the various cells' meanings are. If I could replace storing a value from VBA in a cell (and using the cell's value in the cell in formulas) with storing values in a variable in VBA (and using the variable's value in formulas in cells), I could name the variables in a way that makes
my code much more mnemonic and much easier to read.

**shg** · 03-18-2019, 02:12 PM

Named (constant) formula:

Please Login or Register  to view this content.

	A	B
1	1	A1: =Chapter

**shg** · 03-18-2019, 02:22 PM

Better example:

Please Login or Register  to view this content.

	A	B
1	Book The First: Recalled to Life	A1: =Chapter

Be aware that changing the variable won't update the named formula; you would need to change it as the variable changes.

**lovecolorado** · 03-18-2019, 02:25 PM

OK . . . so rather than tell me how to use a public variable from VBA in the formula within a cell, you're telling me a way to provide mnemonic names to the cells in which I'm currently storing values. That does achieve my desired goal of making the code more mnemonic and easier to read, so I like where you're going.

I have three questions:
1) Do I have to repeat the command in every module where I use the public variable, or can I do this just once for all modules in all worksheets?
2) What would a fully-referenced version of the command look like, or does the above command have to be in a module (as opposed to a worksheet)?
3) Re "changing the variable won't update the named formula; you would need to change it as the variable changes": I'm guessing you mean the VBA code needs to assign a new value to the public variable, and the new value will appear in the cell of a worksheet. I'm also guessing that, assuming the worksheet is set to automatically recalculate, that the new value in the public variable will be used in any formulas that reference the cell. Is this all correct?

**mjr veverka** · 03-18-2019, 04:36 PM

Developing the shg thread/idea (see attachment, maybe you can find some answer here):

**lovecolorado** · 03-19-2019, 08:24 AM

I thought I 'had it', but something is still wrong.

I inserted the code

Please Login or Register  to view this content.

and I confirmed the value of the variable named 'Chapter' was now available in any worksheet - which is exactly what I originally wanted. Super!

So, I generalized the solution to the following

Please Login or Register  to view this content.

When I try to run this code, the debugger stops execution and highlights the line

Please Login or Register  to view this content.

in yellow. Does anyone have an idea why this would occur?

**rorya** · 03-19-2019, 08:27 AM

Why don't you just create a function that returns the value of the variable?

**lovecolorado** · 03-19-2019, 08:33 AM

Nice option. I can see how that would work.

Aesthetically, I would prefer to get the global variables working properly; however, if I can't get that to work, you've given me a good work-around.

Using a Global Variable in a formula, in a cell, in a Worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Using a Global Variable in a formula, in a cell, in a Worksheet

Re: Using a Global Variable in a formula, in a cell, in a Worksheet

Re: Using a Global Variable in a formula, in a cell, in a Worksheet

Re: Using a Global Variable in a formula, in a cell, in a Worksheet

Re: Using a Global Variable in a formula, in a cell, in a Worksheet

Re: Using a Global Variable in a formula, in a cell, in a Worksheet

Re: Using a Global Variable in a formula, in a cell, in a Worksheet

Re: Using a Global Variable in a formula, in a cell, in a Worksheet

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Global variable declared in Userform. Variable value is not sticking

A variable which is used by several subs in a worksheet but is not global?

[SOLVED] why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel?

[SOLVED] why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel?

[SOLVED] why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel?

why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel?

[SOLVED] why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel?

why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel?

Bookmarks

Bookmarks

Posting Permissions