Can't figure out the right way to break a long parameter to multiple lines

Question

I've created a script to fetch json response from a website. To get the response I had to issue post http requests along with appropriate parameters. The script is doing fine.

The payload that I've used within the script is substantially long. It could have been longer.

Now, my question is, how can I break such long line to multiple lines?

This is how I've tried:

Sub GetJsonResponse()
    Const URL = "https://api.pcexpress.ca/product-facade/v3/products/category/listing"
    Dim payload$

    payload = "{""pagination"":{""from"":2,""size"":48},""banner"":""loblaw"",""cartId"":""702da51e-a7ab-4f54-be5e-5bf38bd6d7a2"",""lang"":""en"",""date"":""09062021"",""storeId"":""1032"",""pcId"":null,""pickupType"":""STORE"",""enableSeldonIntegration"":true,""features"":[""loyaltyServiceIntegration"",""sunnyValeServiceIntegration""],""inventoryInfoRequired"":true,""sort"":{""topSeller"":""desc""},""categoryId"":""27985""}"
    
    With CreateObject("MSXML2.XMLHTTP")
        .Open "POST", URL, False
        .setRequestHeader "content-type", "application/json;charset=UTF-8"
        .setRequestHeader "x-apikey", "1im1hL52q9xvta16GlSdYDsTsG0dmyhF"
        .send (payload)
        Debug.Print .responseText
    End With
End Sub

Answer 1

Use the & concatenation to join smaller parts. I would personally examine the json structure and then decide on logical breaks (within reason), then transfer to a text editor and use regex/ find and replace to generate the new strings to concatenate based on your chosen line breaks.

Below you will see most lines have payload = payload & " at the start and " at the end, after the break indicated by the ,.

Of course, also replacing inner " with "".

Option Explicit

Sub GetJsonResponse()
    Const URL = "https://api.pcexpress.ca/product-facade/v3/products/category/listing"
    Dim payload$

    payload = "{""pagination"": {""from"": 2,""size"": 48},"
    payload = payload & """banner"": ""loblaw"","
    payload = payload & """cartId"": ""702da51e-a7ab-4f54-be5e-5bf38bd6d7a2"","
    payload = payload & """lang"": ""en"","
    payload = payload & """date"": ""09062021"","
    payload = payload & """storeId"": ""1032"","
    payload = payload & """pcId"": null,"
    payload = payload & """pickupType"": ""STORE"","
    payload = payload & """enableSeldonIntegration"": true,"
    payload = payload & """features"": [""loyaltyServiceIntegration"", ""sunnyValeServiceIntegration""],"
    payload = payload & """inventoryInfoRequired"": true,"
    payload = payload & """sort"": {""topSeller"": ""desc""},"
    payload = payload & """categoryId"": ""27985""}"

    With CreateObject("MSXML2.XMLHTTP")
        .Open "POST", URL, False
        .setRequestHeader "content-type", "application/json;charset=UTF-8"
        .setRequestHeader "x-apikey", "1im1hL52q9xvta16GlSdYDsTsG0dmyhF"
        .send payload
        Debug.Print .responseText
    End With
End Sub

This fits with how I re-arranged this:

To this:

---

As you noted in the comments, you can absolutely split the string into pieces and continue the line with the line continuation character _.

Answer 2

Using the Windows Clipboard API functions from here: https://learn.microsoft.com/en-us/office/vba/access/concepts/windows-api/send-information-to-the-clipboard

You can copy text to the clipboard and do something like:

Sub ClipboardTextToVbaString()
    Dim s As String, arr, e, rv As String, i As Long, n As Long
    s = GetClipboard()  'read text from clipboard
    If Len(s) = 0 Then Exit Sub
    arr = Split(s, vbCrLf)
    rv = "s = "
    For i = LBound(arr) To UBound(arr)
        e = Replace(arr(i), """", """""")
        rv = rv & """" & e & """ "
        If i < UBound(arr) Then
            If n < 20 Then
                rv = rv & " & vbCRLf &  _" & vbCrLf
                n = n + 1
            Else
                rv = rv & " & vbCRLf" & vbCrLf & "s = s & "
                n = 0
            End If
        End If
    Next i
    'Debug.Print rv
    SetClipboard rv  'set the modified text back into the clipboard for pasting
End Sub

Not very thoroughly-tested but you get the idea: something for your personal.xlsb file... Note this is more aimed at formatting multi-line text into a VB-compatible format - not really for breaking up long single lines, which I guess was your original form.