How to determine or find out what list separator will VBA use for range validation? (Excel VBA Range.Validation list separator interference with Hyperlinks)

Hello,

Description + Reproduction of the problem + Purpose

Microsoft Excel uses different list separators in different conditions while working with Hyperlinks and Validation in Cells. I use European version of Office so my regional list separator is semicolon while VBA default list separator is comma.

While working with Validation and Hyperlinks, list separator behave differently according to order when individual commands are called. To better describe the issue lets use simplified procedures as an example:

Private Sub addLink()
  Range("B1").Hyperlinks.Add Range("B1"), "", "B1", , "Link"
End Sub

Private Sub addValidation()

  With Range("A1").Validation
    .Delete

    .Add xlValidateList, , , "one,two,three;four,five,six"

  End With

End Sub

Sub runOk()

  addValidation

  addLink

End Sub

Sub runWrong()

  addLink

  addValidation

End Sub

If I call "runOk" procedure (first Validation, then Hyperlink), comma as list separator is used so the validation list in Cell is as follows:

one

two

three;four

five

six

If I call "runWrong" procedure (first Hyperlink, then Validation), semicolon as list separator is used so the validation list in Cell is as follows:

one,two,three

four,five,six

Purpose of the solution is meant especially for restoring accidentally deleted Hyperlinks and Validations in Sheet Cells and updating its content (different values in Validation lists, different references in Hyperlinks) on Change / FollowHyperlink events.

Question

I would like to know if there is some elegant solution how to force VBA to use either comma separated list or semicolon separated list to be used in all cases, so no matter the order when I call addLink and addValidation, I get the same resulting validation list in the Cell.

Another acceptable approach would be if there is some Application.International(xlListSeparator) alternative returning separator that will VBA use on runtime in that particular case so I can save it in a variable and use it instead of hard coded comma or semicolon.

Limitations

I cannot make the solution dependant on changing default list separator in system settings or any other settings. Need a VBA programatically solution only.

I would like to avoid using a Worksheet Range for Validation Formula1 argument as well as create a temporary hyperlink to force VBA to use regional list separator. I would like to avoid also any similar solution that works with Cells/Sheets.

Also I would like to avoid approach to keep an eye on correct order (to always Call all Validations before Hyperlinks) in all procedures as it might not be possible in all cases as well as if someone would edit the code it could possibly break the entire functionality because of wrong validation list in Cell.

What I have tried + Specification

With help of internet search as well as debugging the code I have found that the issue appears when Hyperlinks are used before Validations called in one procedure or in sub procedures (i.e. calling Hyperlink action will make subsequent Validation to use regional list separator even if it is in sub sub sub procedure). If procedures are called separately, Hyperlink can be called first and subsequent Validation uses comma as list separator, so some reset of list separator must happen when the entire macro ends.

Since this I had no idea what to try that could help (except things listed in Limitations above) as I didn't find any usable Application.Path/DecimalSeparator alternatives for list separator. Although I might forgot something that I've tried.

Application.UseSystemSeparators doesn't affect list separator.

Tested on Windows 10 with Microsoft 365 64-bit and Windows 7 with Microsoft Office 2010 32-bit - same behavior on both specifications. European version of Windows and Excel with semicolon (;) as regional list separator.

Any advice or idea how to work this out with elegance is very appreciated,

Thanks