Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How do i modify the List Seperator Character

Posted on 2006-07-24
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 600 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.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


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 1400 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
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…
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

718 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