Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

SaveSetting, GetSetting and DeleteSetting on Mac

In Windows you can use it to add information of your file or add-in into the Windows register, you can use Regedit.exe to view this info in the register in Windows. you find it here : HKEY_CURRENT_USER\SOFTWARE\VB and VBA Program Settings

The good news is that SaveSetting, GetSetting and DeleteSetting are all working on a Mac. But there is no register on a Mac where can you find the information that you save ?

On a Mac it will store this information in .plist file, the name of the plist file is the name that you use as first argument in savesetting

You find it here /Users/<UserName>/Library/Group Containers/UBF8T346G9.Office/Profile Settings/

Note: Library is a hidden folder on your Mac, this is how to open it in Finder :

1. Open a Finder Window
2. Hold the Alt key and click Go in the Finder menu bar
3. Click Library

Basic example to save for example the version number that is in a worksheet cell:

Sub Macro1SaveTheValue()
Dim VersionNumber As String
VersionNumber = Sheets("Sheet1").Range("A1")
SaveSetting "MyTools", "MyProgram", "version", VersionNumber
End Sub

Sub Macro2GetTheValue()
Dim VersionNumber As String
VersionNumber = GetSetting("MyTools", "MyProgram", "version", "")
MsgBox VersionNumber
End Sub


Sub Macro3DeleteTheValue()
On Error Resume Next
DeleteSetting "MyTools", "MyProgram", "version"
On Error GoTo 0
End Sub

Note: The only thing I notice that with the getsetting default argument you can't use a empty string to load if there is no value saved. As a workeround I use "?" instead of "" in a few of my add-ins, but it looks like they fix it in Mac Office 365 now so there is no problem anymore.