+ Reply to Thread
Results 1 to 16 of 16

Using the value stored in a Global Variable in a cell's formula

  1. #1
    Forum Contributor
    Join Date
    02-01-2019
    Location
    Illinois
    MS-Off Ver
    2016
    Posts
    219

    Using the value stored in a Global Variable in a cell's formula

    I had a program that stored values in various spreadsheet cells. Since my workbook has several worksheets, when I write a formula and referring to the value held in any one cell of any one spreadsheet, the formula becomes a very long expression with no mnemonics as to the meaning of the cell's value. To make the code easier to read, I've started using Global Variables to provide mnemonic names to the cell's value. So far, so good.

    Because the global variable's cell is 'hard coded', if a user inserts, or deletes, a line above the global variable's cell, the formula will adjust which cell it looks to for the value, but the global variable's cell will not adjust and the formula will no longer work.

    Because my workbook will be used by hundreds of people, I want to make it as 'bullet proof' as possible - to include (if possible) enabling formulas that use cells that hold the value of global variables to continue working if someone inserts or deletes a line above it. I can think of two ways to accomplish this:
    1. Figure out a way to use the global variable directly in a formula, instead of having the global variable use a cell and the formula use the same cell, or
    2. When someone inserts or deletes a line above the cell holding the value of the global variable, have the code change which cell holds the value of the global variable in the same way the cell in a formula will change.

    Does anyone know the code to make this happen (either in one of the ways I described, or in another way that you come up with)?

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Using the value stored in a Global Variable in a cell's formula

    What do you mean by 'Global Variables'? I assume you have defined a cell with a name, but when I do that, I can add and remove rows/columns and everything adjusts as expected.

    You can also use Name Manager to hold a specific value. The benefit being it would be immune from any row/col deletions, but the con is that to change it, you have to use Name Manager.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Forum Contributor
    Join Date
    02-01-2019
    Location
    Illinois
    MS-Off Ver
    2016
    Posts
    219

    Re: Using the value stored in a Global Variable in a cell's formula

    Re "What do you mean by 'Global Variables'?": I mean the following code, which starts at the first line of the module:
    Please Login or Register  to view this content.
    Re "You can also use Name Manager to hold a specific value. The benefit being it would be immune from any row/col deletions": If I can use these values in the formulas contained in cells, then this sounds like what I'm looking for.
    Re "the con is that to change it, you have to use Name Manager.": That sounds a lot like 'the con of using conditional formatting is that you have to create rules'. Is there anything more difficult about using Name Manager than creating conditional format rules?

  4. #4
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Using the value stored in a Global Variable in a cell's formula

    Check this out, as I think it meets your needs:
    https://www.contextures.com/xlNames01.html

  5. #5
    Forum Contributor
    Join Date
    02-01-2019
    Location
    Illinois
    MS-Off Ver
    2016
    Posts
    219

    Re: Using the value stored in a Global Variable in a cell's formula

    First: Your link contained excellent information for my application. I learned several things that I will be using. Thank you.

    Second: I didn't see anything in your link about "us[ing] Name Manager to hold a specific value. The benefit being it would be immune from any row/col deletions". The closest thing to this (that I saw) was a way to assign a name to a specific cell.
    Are you implying that Name Manager will adjust the location of the specific cell if rows above the cell are added or deleted?

    I was hoping there was a way to assign a variable that:
    • could be assigned new values by VBA,
    • could be used in a cell's formula,
    • and was not associated with any cell.

  6. #6
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Using the value stored in a Global Variable in a cell's formula

    I have to admit, I'm still a little confused on your use case. As far as I know, global variables defined in VBA cannot be referenced in a cell formula. But based upon this statement, you seem to be doing that?
    I've started using Global Variables to provide mnemonic names to the cell's value
    It's this sharing a name between an excel cell and VBA that you seem to want. I don't know how to go from VBA to an excel cell, but you can definitely use named cells in both an excel cell (just use the name) and in VBA by Range("MyName")

    Paul

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Using the value stored in a Global Variable in a cell's formula

    I will venture this idea, though I never see it suggested by more experienced VBA programmers, so it may not be the best (but it works very well for me). I use a user-defined function, which can be used just like Excel's built in PI() function. Something like
    Please Login or Register  to view this content.
    . Use in the worksheet(s) just like the built in PI() function =54+45*Mymnemonicconstantname(). Of course, I would probably use a shorter function name, but you should get the idea.

    One of the main reasons I find this advantageous is because I want my constants available across all my workbooks, so I can store this simple UDF in an add-in and have it available to all my workbooks -- which are at most only rarely shared with others. In your case, you will need to store the UDF inside of the workbook so that everyone else using the workbook can access the UDF. With this in mind, I am not sure why this would be advantageous over storing the constant in a named constant. The contextures article mentioned using named constants, but did not seem to discuss in any detail. This article describes using named constants: https://www.techrepublic.com/article...med-constants/


    Assuming I understand what you are trying to do, I would use one of those two approaches.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  8. #8
    Forum Contributor
    Join Date
    02-01-2019
    Location
    Illinois
    MS-Off Ver
    2016
    Posts
    219

    Re: Using the value stored in a Global Variable in a cell's formula

    Re "I've started using Global Variables to provide mnemonic names to the cell's value": I'm sorry this was confusing to you, but the text you quoted describes my current state. I'm trying to move beyond that current state to a new state with code that isn't corrupted by the addition, or deletion, of rows above the cell associated with the variable.

    I'm guessing the way to do this is to avoid any association with cells at all; however, it just occurred to me that, if Excel adjusts the location of named cells to account for the addition or deletion of rows above the named cell, then I could use a named cell to get to an acceptable new state.
    Last edited by lovecolorado; 03-20-2019 at 01:01 PM.

  9. #9
    Forum Contributor
    Join Date
    02-01-2019
    Location
    Illinois
    MS-Off Ver
    2016
    Posts
    219

    Re: Using the value stored in a Global Variable in a cell's formula

    I had thought about using a function, and am holding onto it back as a fall-back position.

    Re "I never see it suggested by more experienced VBA programmers": I wouldn't be so dismissive. As you cited, yourself, Microsoft uses PI() exactly as you are suggesting and I would consider the corporation of Microsoft to qualify as an 'experienced VBA programmer'.

    Your suggestion is a nice way to establish global constants, and some of my global variables will always hold the same value; however, more of them will be assigned different values over time. I've thought of ways to approach that problem; however, IMHO, this approach goes against my basic desire to make the code more readable for the hundreds of people I hope to have using the spreadsheet. And my audience of 'people' consists entirely of advanced engineers, so many of them will want to dive into the code and see what I've done.
    Last edited by lovecolorado; 03-20-2019 at 01:01 PM.

  10. #10
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Using the value stored in a Global Variable in a cell's formula

    Yep, a UDF can work. Let me expand on the Named Range idea.

    Using the Name Manager (or you can do this in VBA), you can define a name and a hard coded value. As an example, go to Define name and a dialog box opens.
    In Name: type "MyTest" (without the quotes)
    Keep the Scope as Workbook
    In the Refers To: input just type something like "=1234" (without the quotes)

    Now, in Excel formulas, you can just use MyTest as a name in formulas, so something like =5*MyTest will give you 6170.
    Usually the Refers To: is a range, so in VBA Range("MyTest") works as expected.In VBA, but since this is a hardcoded value, this will not work. You have to use:
    evaluate(Names("MyTest").Value) to get the appropriate value saved in your Name.

    To create a Named range in VBA
    ActiveWorkbook.Names.Add Name:="MyTest", RefersTo:="=123"

    To modify the value in VBA
    ActiveWorkbook.Names("MyTest").RefersTo = "=1234"

  11. #11
    Forum Contributor
    Join Date
    02-01-2019
    Location
    Illinois
    MS-Off Ver
    2016
    Posts
    219

    Re: Using the value stored in a Global Variable in a cell's formula

    This does exactly what I am looking for. Thank you!
    Last edited by lovecolorado; 03-20-2019 at 12:59 PM.

  12. #12
    Forum Contributor
    Join Date
    02-01-2019
    Location
    Illinois
    MS-Off Ver
    2016
    Posts
    219

    Re: Using the value stored in a Global Variable in a cell's formula

    A-U-R-G-H! I celebrated too quickly. I can't figure out how to implement Pauleyb's suggestion.

    What I can do is use the 'Name Manager' to define "MyTest" (without the quotes) with a value with a 'workbook' scope.
    I can use MyTest in a formula and the formula works as one would hope; i.e., I seem to have defined a named constant.
    The previous step works in any worksheet; i.e., the scope of the named constant seems to be the entire workbook.
    So far, so good.

    The problem comes when I ask try to use MsgBox to display the value of MyTest. In this situation, MyTest appears to be not defined; i.e.,
    Please Login or Register  to view this content.
    results in MyTest.png, and
    Please Login or Register  to view this content.
    results in Error.png and
    Please Login or Register  to view this content.
    being highlighted yellow.
    (Note: The MsgBox macro is run when Sheet1 is activated; i.e., when a different worksheet than 'Sheet1' is currently selected and then the 'Sheet1' tab is selected.)

    Why is MyTest defined for formulas and not for VBA macros?
    Or, more importantly, what do I need to do so that MyTest is defined with the value I give it for both formulas and VBA macros?

    The attached file shows everything I've described in this post.
    Attached Files Attached Files
    Last edited by lovecolorado; 03-22-2019 at 03:41 PM.

  13. #13
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Using the value stored in a Global Variable in a cell's formula

    via the Names route you would need to qualify it relative to the workbook to which the name belongs (wb scope as mentioned)

    Please Login or Register  to view this content.
    via Evaluate

    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    02-01-2019
    Location
    Illinois
    MS-Off Ver
    2016
    Posts
    219

    Re: Using the value stored in a Global Variable in a cell's formula

    "Qualify it". Of course! Thank you for pointing that out, and for so-clearly describing how to qualify it.

    The variable works now, in both formulas and macros - which is the option I'm looking for to reduce, or eliminate, my spreadsheet's use of cells to hold global variables.

    (The goal of reducing the use of cells to hold global variables is to make the spreadsheet more 'bullet proof' against users (and there will be hundreds of users) corrupting macros by adding / deleting rows above the cells holding global variables.)
    Last edited by lovecolorado; 03-22-2019 at 05:02 PM.

  15. #15
    Forum Contributor
    Join Date
    02-01-2019
    Location
    Illinois
    MS-Off Ver
    2016
    Posts
    219

    Re: Using the value stored in a Global Variable in a cell's formula

    I'm getting very close to finishing this thread, but am stuck one step short of the finish line.

    Per the "MyTest" file, above, I can assign fixed values to named, public variables and use the named variables in formulas in any worksheet. Great!

    While I have some named, public variables that I want to have constant values, there are also some named, public variables with values that change. For these variables, I want to use formulas to assign the value. If I use
    Please Login or Register  to view this content.
    to assign a formula to the variable named Chapter, then Name Manager successfully assigns the formula to Chapter, which is a public variable. The problem is that the formula doesn't calculate; i.e., the named, public variable contains the text of the formula and not the result of the formula's calculation. If I try to use this named, public variable in a formula, the cell containing the formula displays the text #NAME? and I get an "Invalid Name Error".

    I have attempted to force the formula to calculate by placing
    Please Login or Register  to view this content.
    in the line of code immediately before I assign a formula to a named variable, but this doesn't change anything.

    Does anyone know how to assign a formula to a named, public variable so the variable contains the calculated result of the formula, instead of the text of the formula?

    The particulars are:
    When I use
    Please Login or Register  to view this content.
    to assign the formula to the named, public variable Chapter, Name Manager shows Chapter is assigned the text
    Please Login or Register  to view this content.
    , which is exactly the correct formula; however, the formula doesn't calculate to a value.
    Attached Files Attached Files
    Last edited by lovecolorado; 03-23-2019 at 08:57 AM.

  16. #16
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Using the value stored in a Global Variable in a cell's formula

    A couple of things to try (sorry too busy right now to dig deep into this).
    First, name manager can only use excel formulas, so putting Application.Evaluate(....) in its RefersTo field is not going to work, and is probably why it is assuming it is a string. Application.Evaluate is VBA, not an excel formula. So, it may be as easy as that.

    Second, you can always use the Name Manager in Excel to get the formula 'right' and then in the vba immediate window you can print out the Name's RefersTo text to see what it has. Then copy/paste that into your VBA and make necessary adjustments.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Using a Global Variable in a formula, in a cell, in a Worksheet
    By lovecolorado in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-19-2019, 08:33 AM
  2. Formula in vba to contain a range which is stored in a variable
    By h3llblax in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-23-2018, 02:37 PM
  3. [SOLVED] Handling Errors from Formula result, stored in a variable.
    By adibukovi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-06-2018, 10:29 AM
  4. [SOLVED] Formula using stored variable
    By fireflydreams in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-16-2015, 10:36 AM
  5. Correct syntax to add a workbook name that is stored in a variable into a formula
    By lordterrin in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-25-2014, 03:46 PM
  6. [SOLVED] Compare what cell is stored in a Range variable.
    By GaidenFocus in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-12-2012, 03:05 PM
  7. Help with a Formula to Print a stored variable
    By Modify_inc in forum Excel General
    Replies: 4
    Last Post: 12-16-2010, 09:59 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1