VBA - storing variables in worksheet for later refrence

[dave2018]

Hi.

We all know that vba can't remember variables after the code finishes. (or could it?).

I am writing a program (pos style) in excel vba and sometimes, a code will depend on a condition that was set by a previously (not connected) code.

For example, if the user clicks CTRL when a certain textbox was active, the textbox goes into another mode (in my case, ctrl toggles whether the product barcode that was scanned should be added to the sale, or deducted etc.)

What I am looking for, is to make my reading/writing variables to this worksheet, for example by typing some type of function such as:

GetVariable(ProductDeleteMode)

It would give me a true or false, depending on what is written on variablesheet.range("b2"), because B1 says ProductDeleteMode (my variable name so to speak). Alternatively, I should be able to type:

WriteVariable(ProductDeleteMode,False)

In which the second parameter is the value to be written to cell B2.

My question is, by using class modules or the like (which I'm not familiar with) is it possible to dynamically display a list with intellisense when I type: getvariable(intellisense pops up here with all the values in variablesheet.range(a1 until the end of the current list) and it will match up the value that I chose, with the corresponding record on that sheet.

Thanks to those who even saw this line of my post .

P's to clarify, my finished code should look like this:

Sub textbox1_keydown(i am writing this from a cellphone so ignore syntax errors :)) 

If keycode = the one for ctrl. Actually it's probably if shift = 2 then

Writevariable(productdeletemode,not getvariable(productdeletemode))

End if

End sub

'then in another sub

Sub textbox1_change(...)

If getvariable(productdeletemode) = true then

'have my code here which executes what needs to happen in this mode

Else

'write the code for normal mode

End if

End sub

 

[Fluff]

Whilst I don't understand what you are trying to do.
You can declare variables as Private, or Global at the top of a module & they will hold that value, until you close the workbook.
Try

Option Explicit
Private Flg As Boolean

Sub test1()
   Flg = False
   If "Fluff" = "Fluff" Then Flg = True
End Sub

Sub test2()
   MsgBox Flg
End Sub

If declared as Private, it will only be available to that module, but if declared as Global it will be available to all modules in ThisWorkbook.
To declare a variable as Global it must be in a regular module, not a sheet/Form/ThisWorkbook module

 

[dave2018]

interesting. I never knew that about declaring variables. But I need it to preserve its data even after closing the workbook. And besides, I would imagine that it takes up less ram if it was stored on the worksheet rather that a variable. True?

 

[Fluff]

But I need it to preserve its data even after closing the workbook

In that case you will need to write it to a sheet.

I would imagine that it takes up less ram if it was stored on the worksheet rather that a variable.

Very possibly, but then you are going to be reading/writing to the sheet more often which will slow down the macro.