Search code examples
vbaexcelexcel-2010add-in

Excel Add-In Configure Variables and Save

I have been asked to create an Excel Add-in for people to load a series of xml documents into Excel spreadsheets from Excel. I will write an add-in and publish its location for people to download their own local copies for use. I would Ideally like to have some constants for the VBA configurable by each end user, to customize their experience and function.

I thought it would be nice to have one button to run the Add-in and another button called "Configure" or something, which would edit the values of some of the variables in the VBA, and save the new values so that next time the user open Excel, the Add-in remains configured for them. Is anything like this possible and do you have some suggestions about what path I should follow to get there?

Is there any way to get the variables to persist? I guess I would need to save the values somewhere on the local version of the Add-in, but if so, what is the best way to store a set of parameters?

Any general advice would be most welcome; I have not actually written the add-in yet, being still in the design stages.

Solution

  • I do not advise using cells to store settings as the user may F it up.

    However VBA does support Registry edits.

    SaveSetting "Macro name", "Settings", "Username", "John Doe"

    The above code will save a setting or registry key called Username with the name John Doe in the appname called Macro name and the section Settings.

    So in short you only need to change the last two strings when you save new settings. The first two should (to make it easy for you) be the same all the time.

    The keys will be stored in : HCU\Software\VB and VBA Program Settings\Your app name\

    To get the setting you use: 

    GetSetting("Macro name", "Settings", "Username")

    Another solution is to use txt-file.
    It's still better than cells but not as good as above mentioned registry.

    settings = "Username=John Doe, Worktitle=Janitor"
MyFile = "C:\myapp\settings.txt"
fnum = FreeFile()
Open MyFile For Output As #fnum
Print #fnum, settings
Close #fnum

    Now you have a txt file with the settings that you can read and parse.