How do i modify the List Seperator Character

Posted on 2006-07-24
Last Modified: 2008-01-09
I have a question regarding the regional settings on a PC.

I have two applications, one is an Excel workbook with lots of VBA and another is a Word document also with lots of VBA.

The Word document will only work properly when the LIST SEPARATOR is set to ;
The Excel sheet will only work properly when the LIST SEPARATOR is set to ,

This is set via:

Control Panel
Regional & Language Options
Regional Options Tab
List Separator  , or ; depending on application.

Its not possible to modify things to make it work with a general list separator... tried that earlier ... see

However, my question is, is there any way of setting the regional settings via VBA when the sheet opens so that an error does not occur ?

Alternatively can i check the value of the List Seperator and if its not a ; as required then use a pop up box to issue a warning to the user.


Question by:GordonMasson
  • 3
  • 2
LVL 76

Expert Comment

ID: 17169793
HI Gordon.
Where, in Word, does the list separator need to be a ";"?

Author Comment

ID: 17170107
Hi Graham

Its a historic document that i cant really change... i dont think???

code is to do with inserting a table of contents at a bookmark ...TOC

Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX;1;SUBAPPENDIX;2""", NumberFormat:=""

I assumee that changing this to:
Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX,1,SUBAPPENDIX,2""", NumberFormat:=""
would fix the problem????? b ut im not sure if there is anything else in the code that uses this.... is it only formulas that use the list seperator ?

perhaps i can do somthing like ...... (using syntax that detects the list seperator

If ListSeperator = ";" Then
Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX;1;SUBAPPENDIX;2""", NumberFormat:=""
Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX,1,SUBAPPENDIX,2""", NumberFormat:=""
End If

Thanks  Gordon
LVL 76

Assisted Solution

GrahamSkan earned 150 total points
ID: 17170897
I have done a few quick tests (Word 2003), and I think that the TOC field, while being sensitve to the List separator setting, seems to work as  one would expect.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


Author Comment

ID: 17173933
Sorry Graham ... not with you ... are you saying that the code below will work (or some varient of that which has the correct syntax)....

If ListSeperator = ";" Then
Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX;1;SUBAPPENDIX;2""", NumberFormat:=""
Selection.InsertFormula Formula:="TOC \F \N \T ""APPENDIX,1,SUBAPPENDIX,2""", NumberFormat:=""
End If

I think this is the safest way for me to go so that the code would detect how the List Seperator is set up on the PC (some pc's that use this have it set as ' others as ;) and then run the line of code which suits the application / PC.

If this is correct can you tell mw what the code would be to detect the PC's List Seperator setting in VBA?



Accepted Solution

dmang earned 350 total points
ID: 17176269
Found a sample on the net that was originally taken from winapi.txt, and tweaked it some!

You can retrieve and set regional settings using the following APIs....

Declare Function GetLocaleInfo Lib "kernel32" Alias _
"GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, _
ByVal lpLCData As String, ByVal cchData As Long) As Long

Declare Function SetLocaleInfo Lib "kernel32" Alias _
"SetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, _
ByVal lpLCData As String) As Boolean

Declare Function GetUserDefaultLCID% Lib "kernel32" ()

'parm value
Public Const LOCALE_SLIST = &HC

'Subs to retireve / set:

Public Sub Get_locale() ' Retrieve the regional setting

      Dim Symbol As String
      Dim iRet1 As Long
      Dim iRet2 As Long
      Dim lpLCDataVar As String
      Dim Pos As Integer
      Dim Locale As Long
      Locale = GetUserDefaultLCID()
      iRet1 = GetLocaleInfo(Locale, LOCALE_SLIST, _
      lpLCDataVar, 0)
      Symbol = String$(iRet1, 0)
      iRet2 = GetLocaleInfo(Locale, LOCALE_SLIST, Symbol, iRet1)
      Pos = InStr(Symbol, Chr$(0))
      If Pos > 0 Then
           Symbol = Left$(Symbol, Pos - 1)
           MsgBox "Regional Setting = " + Symbol
      End If

End Sub

Public Sub Set_locale() 'Change the regional setting

      Dim Symbol As String
      Dim iRet As Long
      Dim Locale As Long      

      Locale = GetUserDefaultLCID() 'Get user Locale ID
      Symbol = "-" 'New character for the locale
      iRet = SetLocaleInfo(Locale, LOCALE_SLIST, Symbol)
End Sub


Author Comment

ID: 17190510
Hi folks

I got this working using this....

Dim fld As Field
If ActiveDocument.Bookmarks.Exists("TOCApp") = True Then
    For Each fld In ActiveDocument.Fields
        If fld.Type = wdFieldTOC Then
            If InStr(fld.Code.Text, "SUBAPPENDIX") Then
                fld.Code.Text = "TOC \F \N \T " & "APPENDIX" & Application.International(wdListSeparator) & "1" & Application.International(wdListSeparator) & "SUBAPPENDIX" & Application.International(wdListSeparator) & "2"
                Exit For
            End If
        End If
    Next fld
End If

Gets me round the problem of PC's with different List Separators set.

i have split the points...thanks for your help...


Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question