Referring to VBA code variables in Worksheet Cells

[Legacy 433589]

Hi, long time listener, first time caller...

I've searched both the internet at large and this forum, and all the responses that pop up go in reverse - how to use the variables in VBA to reference cells in various dynamic ways.

I have a variable in my VBA code (based on an embedded checkbox form, which does not have a Control tab on the Format Control window) that I would like to reference in a formula in a cell on a worksheet. I can find no reference for doing this.

If I just type in "=[variable name]" in a cell, I get a #NAME ? error.

I'm pretty advanced with Excel, but my formal training in it was 20 years ago, so I've missed many things that were introduced since then (and that training never did cover VBA scripting), and there are other things I may just not have been exposed to yet. I do have the MrExcel VBA and MACROS book handy, but a quick skim through the Table of Contents and Index aren't showing any promising entries. So I realize this may be a dumb question one way or the other.

Is this possible? If so, how?

 

[Kamolga]

Hi,
I do the opposite, I define names in Excel and use them as variable in VBA.
What I use is the "define name" in formula tab, you don't need to assign cell, you can chose to put a value. For example, I define name Turnover and in "refers to" I put 100. If in a cell I type =Turnover I will get 100.
Then in VBA I use

Names("Turnover").RefersTo = 5000

and the cell shows 5000. You can use dynamically Turnover in all formulae. I usually use Boolean (true/false) rather than values but it works with all. If your variable is range, it works too

 

[Legacy 433589]

That's a great solution, Kamolga! I will definitely use that in the future.

I wish I could now, but I'm dealing with a 20 year old spreadsheet that's the Excel/VBA equivalent of the world's biggest ball of bailing wire, chewing gum, and twine with a few booby traps thrown in for good measure. The Name Manager is a mess, and none of them seem to be used well.

In the meantime, I went to the code in the Form, and had it smack down a 'true' or 'false' in a cell on a hidden tab and used that in my formula. Not what I wanted exactly, but functionally it does what I was looking for.

Thanks for responding!