Alternative coding for different regional settings

[RandomGerman]

Hi all,

decimal seperator is a full stop in most countries, but there are some countries that use a comma. Unfortunately (for me) Germany is one of these countries. As I sometimes have to create slides in German, sometimes in English, it does make sense to change the regional settings of my computer from time to time.

But changing the regional settings does affect my macros, e.g., when I define the line weight or the internal text margins of a shape I create with a VBA macro. After switching to the "wrong" regional settings, run-time errors do appear as soon as the macro reaches the first comma/full stop used as decimal seperator. (This does not affect the values for position and size.)

So I'm dreaming of something like this:
(The example creates a TextBox in the master. It is tagged, because I have a function to delete it, too)

Sub Example ()
    Dim shp As Shape
    'Red Work in progress stamp on Master
    Set shp = Application.ActivePresentation.SlideMaster.Shapes.AddTextbox(msoTextOrientationHorizontal, Left:=305.29115, Top:=3.1181083, Width:=182.83453, Height:=17.007863)

If RegionalSettingsOfThisComputer = German Then
With shp
    .Fill.Visible = msoFalse
    .Line.Visible = msoTrue
    .Line.ForeColor.RGB = RGB(255, 0, 0)
    .Line.Weight = "1,5"
    .Tags.Add "DRAFTMASTER", "YES"
With .TextFrame
    .AutoSize = ppAutoSizeNone
    .TextRange.Text = "WORK IN PROGRESS"
    .VerticalAnchor = msoAnchorMiddle
    .MarginBottom = "3,9685014"
    .MarginLeft = "0"
    .MarginRight = "0"
    .MarginTop = "3,9685014"
    .WordWrap = msoTrue
With .TextRange
    .Font.Size = 14
    .Font.Name = "Arial"
    .Font.Color.RGB = RGB(255, 0, 0)
    .Font.Bold = msoTrue
    .ParagraphFormat.Alignment = ppAlignCenter
End With
End With
End With
Else
With shp
    .Fill.Visible = msoFalse
    .Line.Visible = msoTrue
    .Line.ForeColor.RGB = RGB(255, 0, 0)
    .Line.Weight = "1.5"
    .Tags.Add "DRAFTMASTER", "YES"
With .TextFrame
    .AutoSize = ppAutoSizeNone
    .TextRange.Text = "WORK IN PROGRESS"
    .VerticalAnchor = msoAnchorMiddle
    .MarginBottom = "3.9685014"
    .MarginLeft = "0"
    .MarginRight = "0"
    .MarginTop = "3.9685014"
    .WordWrap = msoTrue
With .TextRange
    .Font.Size = 14
    .Font.Name = "Arial"
    .Font.Color.RGB = RGB(255, 0, 0)
    .Font.Bold = msoTrue
    .ParagraphFormat.Alignment = ppAlignCenter
End With
End With
End With
 
End Sub

Of course, "RegionalSettingsOfThisComputer = German" is not a VBA command. But is there any other helpful command for this problem? Or is there any other (simple) way to solve it?

Any help is appreciated.

Thank you,
RG

[John Wilson]

In Excel you can read the decimal sep. but not is PowerPoint

Couple or workarounds:

1. create and use Excel

Function usesComma() As Boolean
Dim XLapp As Object
Set XLapp = CreateObject("EXCEL.Application")
If XLapp.International(3) = "," Then usesComma = True
Set XLapp = Nothing
End Function

2. Simple and not elegant but works (quicker)

Function usesComma2() As Boolean

If InStr(3 / 2, ",") > 0 Then usesComma2 = True
End Function

You might also consider using whole numbers! The difference between 4 and 3.968 is < 1/1000 inch

[SamT]

Have you tried using the Application's LanguageSettings property?

[RandomGerman]

Hi Sam,

yes I played with it, but it didn't seem to influence the language PowerPoint uses to read VBA code.

Thanks for thinking about it.

[RandomGerman]

H John,

of course you are right, the gap between 3.968 and 4 is ridiculous, but this was only an example (maybe not the best one). For line weight or shadow settings it is much more important, what happens behind the decimal seperator.

Just to I make sure, I didn't get it wrong: The workarounds you posted, both are for Excel, not only the first one. Right?

Thank you for your time and patience!

[SamT]

X = CLng("42" & Application.DecimalSeparator & "5837589") '= 42.5837589 or 42,5837589 as appropriate.

Dim A, B, C 

Sub Initialize()
Select Case Application.DecimalSeparator
Case "."
A = "1.2"
B = "2.3"
C = "3.4"
Case ","
A = "1,2"
b = "2,3"
C =" 3,4"
End Select
End Sub

[RandomGerman]

Interesting, thank you, Sam. But at last the impossibility to handle the decimal seperator flexibly in VBA for PowerPoint means, one has to use a hidden presentation and paste in prepared shapes from there, as this is the only really simple solution to keep shadow settings and other stuff working for sure. A bit sad from my point of view, as I prefer creating objects with VBA, not carrying around big catalogies of elements.

Once again, thank you!

[John Wilson]

My examples are both for PowerPoint

[SamT]

So are both of mine, but the OP knows best, regardless of your signature block.

[John Wilson]

I must be confused too then Sam. I thought DecimalSeparator property was only in Excel.

[SamT]

According to what I read online, I may be confused.

Actually, I don't remember where that came from. I do remember thinking that the MSO.LanguageSetting Object, LanguageID property might work.

I was thinking something along the lines

Dim DecSep As String
If Application.LanguageSettings.LanguageID(msoLanguageIDUI) = msoLanguageIDEnglishUS then DecSep = "."

I have never used those Properties, so...

[John Wilson]

Yeah

While LanguageID and decimal character settings would usually correlate they are in fact independent of each other so not reliable. The simplest way is to perform a calculation that creates a decimal answer and look for a comma. or period. For some strange reason you also cannot read the LanguageID on a Mac.

[Paul_Hossler]

In Windows, I read those setting out of the registry. Don't know about the Mac

Private 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

I changed the PC to think it was in Germany, and the separators and others seemed correct

Option Explicit
Sub testing()
    MsgBox "DecimalSeperator = " & DecimalSeperator
    MsgBox "ThousandSeperator = " & ThousandSeperator
    
    MsgBox "CountryName = " & CountryName
    MsgBox "CountryNameShort = " & CountryNameShort
    MsgBox "CurrencyName = " & CurrencyName
    MsgBox "Language = " & Language
    MsgBox "LanguageShort = " & LanguageShort
    MsgBox "LanguageAbbr = " & LanguageAbbr
    
End Sub

The code that uses the API is too long but it's in the attachment


So something like this should work

If Language = "Germany" Then

unless you wanted to make it general purpose like


.MarginBottom = "3" & DecimalSeperator & "9685014"

[SamT]

@ John,

thanks

Public Function pptDecimalSeparator() As String
  If InStr(1 / 2, ".") Then pptDecimalSeparator = "."
  If InStr(1 / 2, ",") Then pptDecimalSeparator = ","
End Function

Sub test()
Dim X
X = pptDecimalSeparator
End Sub

[John Wilson]

Yep that's pretty much what I meant in my (incorrectly formatted!) answer above.

The only options are "," and "." (I think) so:

Function usesComma2() As Boolean
If InStr(3 / 2, ",") > 0 Then usesComma2 = True  
End Function

[RandomGerman]

Thank you so much for this. Reading all your ideas I felt a bit like a someone who has learned the chess rules a day ago and then watches a world championship match :-)

In the end it was Paul's solution I felt close to understanding it and I dared to extract those parts of it I need. So this is what I got:

Option Explicit
Private 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
            
Private Const LOCALE_USER_DEFAULT As Long = &H400
Private Const LOCALE_SDECIMAL As Long = &HE         'decimal sep char, e.g. dot

Property Get DecimalSeperator() As String ' dot
    DecimalSeperator = pvtGetInfo(LOCALE_SDECIMAL)
End Property
 Function pvtGetInfo(ByVal lInfo As Long) As String
    Dim Buffer As String
    Dim ret As String
    Buffer = String$(256, 0)
    ret = GetLocaleInfo(LOCALE_USER_DEFAULT, lInfo, Buffer, Len(Buffer))
    If ret > 0 Then
        pvtGetInfo = Left$(Buffer, ret - 1)
    Else
        pvtGetInfo = vbNullString
    End If
lbl_Exit:
    Exit Function
End Function
            
    Sub DecSepProb()
    Dim shp As Shape
    Dim sld As Slide
    'create a stamp
    Set sld = Application.ActiveWindow.View.Slide
    Set shp = sld.Shapes.AddShape(Type:=msoShapeRectangle, Left:=50, Top:=75, Width:=125, Height:=50)
    shp.Fill.ForeColor.RGB = RGB(255, 255, 255)
    shp.Line.ForeColor.RGB = RGB(255, 0, 0)
    shp.Line.Weight = "1" & DecimalSeperator & "5"
    shp.Rotation = "355"
    shp.Shadow.Style = msoShadowStyleOuterShadow
    shp.Shadow.ForeColor.RGB = RGB(0, 0, 0)
    shp.Shadow.Transparency = "0" & DecimalSeperator & "6"
    shp.Shadow.Size = "100"
    shp.Shadow.Blur = "4"
    shp.Shadow.OffsetX = "2" & DecimalSeperator & "1"
    shp.Shadow.OffsetY = "2" & DecimalSeperator & "1"
    
    shp.TextFrame.TextRange.Font.Color.RGB = RGB(255, 0, 0)
    shp.TextFrame.TextRange.Characters.Text = "Comma"
    shp.TextFrame.TextRange.Paragraphs.ParagraphFormat.Alignment = ppAlignCenter
    shp.TextFrame.VerticalAnchor = msoAnchorMiddle
    shp.TextFrame.TextRange.Font.Size = 14
    shp.TextFrame.TextRange.Font.Name = "Arial"
    shp.TextFrame.TextRange.Font.Bold = msoTrue
    shp.TextFrame.TextRange.Font.Italic = msoFalse
    shp.TextFrame.TextRange.Font.Underline = msoFalse
    shp.TextFrame.MarginBottom = "3" & DecimalSeperator & "685037"
    shp.TextFrame.MarginLeft = "7" & DecimalSeperator & "0866097"
    shp.TextFrame.MarginRight = "7" & DecimalSeperator & "0866097"
    shp.TextFrame.MarginTop = "3" & DecimalSeperator & "685037"
    shp.TextFrame.Orientation = msoTextOrientationHorizontal
End Sub

(It is not the same example as above, but one with line weight, internal margins and shadow settings included, three of the troublemakers.)

And it seems to work.

So, once again, thank you very much, all three of you.

[Paul_Hossler]

My (very) personal style is to have 'toolbox' modules (e.g. Sys_Regional_03) with sometimes many related subs, etc., most of which will never be used on a given project.

Since I have many projects going, it's just a poor attempt as some kind of configuration management

So when I fix a bug or add a feature to Sys_Regional_03, I'll rename it as Sys_Regional_04 so that when I'm in another project that has Sys_Regional_03 I know I need to update that module

Extracting the pieces you wanted is good way to go also