message-Windows-International-registry Ms Access Gurus
Home Code

VBA > Registry > Get Windows List Separator

VBA function to read the Registry to get the Windows List Separator character.

Quick Jump

Example Code Logic Why the Registry? Other methods Download BAS Reference Backstory Share contact Branislav

Example

Here's a message box like the one you'll see if you run the fGetSeparator_run subroutine:

message with Windows Region setting read from the registry

After you read a setting, you might wish to store it so loading is faster next time. You can use a field in a table, or an object property. Here is code to get and set properties.

Goto Top  

Code 

'*************** Code Start *****************************************************  
' module name: mod_Registry_GetSeparator_Branislav  
'  http://msaccessgurus.com/VBA/Code/Registry_Separator_Branislav.htm  
'-------------------------------------------------------------------------------  
' Purpose  : read Windows Region List Separator using the Registry 
' Return   : string 
' Author   : Branislav Mihaljev   
'------------------------------------------------------------------------------- 
' Code List: www.msaccessgurus.com/code.htm  
'------------------------------------------------------------------------------- 
'early binding:  
'  Windows Script Host Object Model  
'  wshom.ocx  
' WScript.Shell = IWshRuntimeLibrary.WshShell 
'-------------------------------------------------------------------------------  
'           fGetSeparator  
'-------------------------------------------------------------------------------  
Function fGetSeparator() As String
    Dim RegObj As Object, RegKey As String
    Set RegObj = CreateObject("WScript.Shell")
    RegKey = RegObj.RegRead("HKEY_CURRENT_USER\Control Panel\International\Slist")
    If RegKey = "" Then
        MsgBox "There Is no registry value For this key!", vbInformation, "Separator"
    Else
        fGetSeparator = RegKey
    End If
    Set RegObj = Nothing
End Function
'-------------------------------------------------------------------------------  
'           fGetSeparator_run  
'-------------------------------------------------------------------------------  
Sub fGetSeparator_run()
   MsgBox "Your List Separator is" _
      & vbCrLf & Space(5) _
      & fGetSeparator _
      , , "fGetSeparator_run"
End Sub
'*************** Code End ******************************************************

Goto Top  

Logic

In the fGetSeparator function, an object is created for WScript.Shell. Late binding is used so you don't have to reference the Windows Script Host Object Model library (wshom.ocx).

The RegRead function is used to get the sList value for the HKEY_CURRENT_USER\Control Panel\International key. If successful, this is assigned as the return value for fGetSeparator

fGetSeparator_run is a subroutine to launch the fGetSeparator function, and show the value in a massage box

Goto Top  

Why use the Registry?

Reading from the Registry is faster and more reliable than, for instance, automating Excel, unless you already have an Excel automation object, or are in Excel itself.

International Registry Key Property Names

Daniel Pineault's blog article on devhut.com has a list of entries with values for the Control Panel International key. -- Go to "Special Characters and Internationalization of an Application" and then search for "Control Panel\International"

Using RegEdit

You can inspect and modify your registry by running RegEdit yourself. Press Win-R (hold down Windows key and press R) to run a program. In the Run dialog box, type regedit and press enter.

Windows-Run-RegEdit

On the left, click ▷ right pointing gray triangle, or ❯ right-pointing angle (however it is displayed) to expand the tree and turn the key indicator into ◢ a black lower right triangle or ﹀ a down angle.

Under HKEY_CURRENT_USER, go to Control Panel, and then International

Regedit-Keys

From there, you'll see the International setting for sList, and other values.

Regedit-Control-Panel-International-sList

Goto Top  

Download

Click HERE to download the zipped BAS file to get read Windows Region List Separator using the Registry. This can be run from Access, or any Microsoft Office application usng VBA.
(2 kb, unzips to a module BAS file)  

Goto Top  

Reference

CreateObject function

Docs / Office VBA Reference / Language reference / Reference / Functions / CreateObject

Help: CreateObject

PowerShell Scripting - Working with Registry Entries

Docs / PowerShell / Scripting / Sample Scripts / Manage Drives & files / Working with Registry Entries

Help: PowerShell Scripting - Working with Registry Entries, including RegRead

PowerShell Overview

Docs / PowerShell / Scripting / Overview

Help: PowerShell overview

wscript

Docs / Windows Server / Windows Commands / Commands by Server Role / wscript

Help: wscript

Goto Top  

Backstory

Branislav commented on VBA code to get International settings using Excel, and thought it a bit much to launch Excel for just one setting. So he shared this code, and said he'd gotten the idea from the devhut blog. I already knew about this article but until I tried Branislav's code, I didn't realize how much faster it was. Thanks!

Goto Top  

Share with others

here's the link to copy:
http://msaccessgurus.com/VBA/Code/Registry_Separator_Branislav.htm

Share your comments and code!

Let's communicate, collaborate, and appreciate ... we all get better by sharing. Email me anytime at info@msAccessGurus.com. I enjoy hearing from Access users and developers.

Do you need help?

Do you have a project that could benefit from an expert developer helping you? Let's connect and build your application together. I love teaching and developing, and look forward to hearing from you. ~ crystal

Goto Top  

more about Branislav:

click to see Branislav's shingle

Goto Top