Private Profile Strings using the Registry using VBA in Microsoft Excel

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/general-topics-in-vba/private-profile-strings-using-the-registry-using-vba-in-microsoft-excel.html
SHARE




Private Profile Strings are often used to store user specific information outside the application/document for later use.
You could for example store information about the latest content in a dialog/UserForm,
how many times a workbook has been opened or the last used invoice number for an invoice template.
Private Profile Strings for each user can be stored in the Registry. You can also use an INI-file,
either on the local harddisk or on a shared network folder.

Here are the example macros for writing to and reading from Private Profile Strings in the Registry.

' the examples below assumes that the range B3:B5 in the active sheet contains
' information about Lastname, Firstname and Birthdate

Sub WriteUserInfoToRegistry()
' saves information in the Registry to
' HKEY_CURRENT_USER\Software\VB and VBA Program Settings\TESTAPPLICATION
    On Error Resume Next
    SaveSetting "TESTAPPLICATION", "Personal", "Lastname", Range("B3").Value
    SaveSetting "TESTAPPLICATION", "Personal", "Firstname", Range("B4").Value
    SaveSetting "TESTAPPLICATION", "Personal", "Birthdate", Range("B5").Value
    On Error GoTo 0
End Sub

Sub ReadUserInfoFromRegistry()
' reads information in the Registry from
' HKEY_CURRENT_USER\Software\VB and VBA Program Settings\TESTAPPLICATION
    Range("B3").Formula = GetSetting("TESTAPPLICATION", "Personal", "Lastname", "")
    Range("B4").Formula = GetSetting("TESTAPPLICATION", "Personal", "Firstname", "")
    Range("B5").Formula = GetSetting("TESTAPPLICATION", "Personal", "Birthdate", "")
End Sub

' the example below assumes that the range D4 in the active sheet contains
' information about the unique number

Sub GetNewUniqueNumberFromRegistry()
Dim UniqueNumber As Long
    UniqueNumber = 0
    On Error Resume Next
    UniqueNumber = CLng(GetSetting("TESTAPPLICATION", "Personal", "UniqueNumber", ""))
    On Error GoTo 0
    Range("D4").Formula = UniqueNumber + 1
    SaveSetting "TESTAPPLICATION", "Personal", "UniqueNumber", Range("D4").Value
End Sub

Sub DeleteUserInfoFromRegistry()
' deletes information in the Registry from
' HKEY_CURRENT_USER\Software\VB and VBA Program Settings\TESTAPPLICATION
    On Error Resume Next
    DeleteSetting "TESTAPPLICATION" ' delete all information
    'DeleteSetting "TESTAPPLICATION", "Personal" ' delete one section
    'DeleteSetting "TESTAPPLICATION", "Personal", "Birthdate" ' delete one key
    On Error GoTo 0
End Sub
Please follow and like us:
0


Leave a Reply

Your email address will not be published. Required fields are marked *

To avoid automated spam,Please enter the value *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>