How to handle double quotes in vba-CodePudding

I have a string assigned to variables and need to declare that in cell using vba.

I tried below code and throws error

Dim Var2,Str as string
Var1="OD"
Var2="Leave"
Str="Employee":Var2,"Type":Var1
set ws=ActiveWorkbook.Worksheets("Sheet1")
ws.cells(2,4).value=str

My expected output in cells(2,4) should be "Employee":"Leave","Type":"OD"

CodePudding user response：

My expected output in cells(2,4) should be "Employee":"Leave","Type":"OD"

Store the values in an array. It will be much easier to handle as compared to having so many variables.

You can use Chr(34) for ". Is this what you are trying?

Option Explicit

Sub Sample()
    Dim Ar(1 To 4) As String
    Dim Strg As String
    Dim ws As Worksheet
    
    Ar(1) = "OD"
    Ar(2) = "Type"
    Ar(3) = "Leave"
    Ar(4) = "Employee"

    '"Employee":"Leave","Type":"OD"
    Strg = Chr(34) & Ar(4) & Chr(34)
    Strg = Strg & ":" & Chr(34) & Ar(3) & Chr(34)
    Strg = Strg & "," & Chr(34) & Ar(2) & Chr(34)
    Strg = Strg & ":" & Chr(34) & Ar(1) & Chr(34)
    
    'Debug.Print Strg
    
    Set ws = ActiveWorkbook.Worksheets("Sheet1")
    ws.Cells(2, 4).Value = Strg
End Sub