MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Remembering Variables

  Asked By: Gene    Date: Feb 18    Category: MS Office    Views: 1451

I wish to set a variable (an Exchange Rate) that will be remembered
next time the spreadsheet is loaded (without having to save it in the
spreadsheet). The variable will be changed by a designated user every
week so I don't want it as a constant.

Anybody got any ideas ?



8 Answers Found

Answer #1    Answered By: Wilbert Patterson     Answered On: Feb 18

Cannot the user  simply change the value in a VBA code module?
That would be the simplest solution.

Answer #2    Answered By: Faeezah Khan     Answered On: Feb 18

Where is the user  changing the rate? That is where is it being set?

Answer #3    Answered By: Kanya Jainukul     Answered On: Feb 18

Another possible way to go is to ask for the value when the workbook is
opened maybe

Answer #4    Answered By: Connie Wallace     Answered On: Feb 18

My intention was to have a button in the spreadsheet, accessed by
only authorised people, who are able to change the 'Default Exchange
I usualy keep this type of info in a separate sheet as 'Hutch' has
suggested, but I am trying to maximise the amount of
data/functionality into the codeing and thus minimise the size of the

Answer #5    Answered By: Adelino Fischer     Answered On: Feb 18

A separate sheet with all these
constants, including other things like file and folder locations. It is the
most elegant way to optimise your coding (assuming all the locations have
Range Names!)

Answer #6    Answered By: Joseph Evans     Answered On: Feb 18

Thanks all, I have decided to keep the constants on a separate sheet.

Answer #7    Answered By: Janis Hart     Answered On: Feb 18

IMHO, saving the value in a worksheet is by far the easiest and best approach.
The ability to do that is one of the nice things about programming in Excel. You
could save  the exchange  rate on another sheet which you can set  to xlVeryHidden
and password protect.
- I don't recommend having a user  open the VBE and edit code (even if it's
just a Constant). - Another option is to write the data out to another file,
such as a text file or another Excel workbook. The next time  your application
workbook is opened, your code can look for the other file to retrieve the
exchange rate. Hopefully the other file will still be there.

Answer #8    Answered By: Shiv Patel     Answered On: Feb 18

Or use an Excel name to store it. You can even hide the name if wished.

Didn't find what you were looking for? Find more on Remembering Variables Or get search suggestion and latest updates.