Excel VBA: how to add data validation list which contain comma included value without using reference of Range

Question

I need to add a data validation list to Cell whose few values contains comma. I have the same problem like this Data Validation to Include Comma Character

But I can't reference a range, because I am creating a new workbook and feed its cell with the data validation list. So reference is not working for me and since some values contain comma so I can't set the Range into String and use that after Formula1

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=myStr

Another idea I found that I can replace comma with other character(dummy character) and after populating the cell replace that dummy character with comma but problem is that how to replace that dummy character with comma ?

Another way is that override the Formula1 so that I can use dummy character as delimiter but I don't know how to achieve this.

Please suggest any solution, my final goal is to create a new workbook and populate it with data validation list through Excel VBA

Answer 1

Let's use the dummy character method for cell B9. This sets up the DV:

Sub InternalString2()
    Range("B9").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Replace("alpha%ralpha,beta%waiter,gamma%hammer,delta%faucet","%",Chr(130))
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = False
    End With
End Sub

where Chr(130) is the dummy. The .ShowError is important.

Now to replace the dummy, we use the Event Macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub

    Application.EnableEvents = False
        Range("B9").Replace What:=Chr(130), Replacement:=","
    Application.EnableEvents = True
End Sub