vba line continuation with bracket expression

Question

varData = [{"Interbank Placement", "LIBOR/IB/COF"; "cat", "goku"; 4, "cow"; 6, "soccer"; "test", 8; "drinks", "goal"}]

I have a code like this. It is too long and I would like to break it into lines. I tried the code below but it still says missing bracket. How can I do this?

    varData = [{"Interbank Placement", "LIBOR/IB/COF"; _ 
"cat", "goku"; 4, "cow"; 6, "soccer"; "test", 8; "drinks", "goal"}]

Why are you separating the values with semicolon? Also, add a space after `_` — 3vts, Apr 03 '17 at 01:29
seems like you have to use `Evaluate("")` instead of `[]`, because `_` is VBA but anything between `[]` is Excel expression. @3vts it's a shortcut for creating 2D array — Slai, Apr 03 '17 at 01:38
varData = Evaluate("Interbank Placement", "LIBOR/IB/COF"; "Corporate Bonds", "LIBOR/IB/COF"; "Government Bonds and T-Bills", "Sovereign"; "Customer Loans", "LIBOR/IB/COF"; "", "Board") Should I do it like this? it doesn't work though. please give me a sample? thanks — Ninja Dude, Apr 03 '17 at 01:58

score 1 · Accepted Answer · answered Apr 03 '17 at 02:06

You can do it by using Evaluate on a string with the array data - but only if the strings are properly quoted. This is a bit of pain, but possible - see below:

Option Explicit

Sub Test()

    Dim strData As String
    Dim varData As Variant

    strData = "{""Interbank Placement"", ""LIBOR/IB/COF"";" & _
        """cat"", ""goku"";" & _
        "4, ""cow"";" & _
        "6, ""soccer"";" & _
        """test"", 8;" & _
        """drinks"", ""goal""}"

    varData = Evaluate(strData)

    MsgBox "varData is array of: " & UBound(varData, 1) & "x" & UBound(varData, 2)

End Sub

vba line continuation with bracket expression

1 Answers1

Linked