CODEWITHSUNDEEP
vbaexcel
Greedo
Greedo

Reputation: 5543

How to hardcode long strings in VBA

I'm writing a macro to save VBA modules as 64 bit strings in another self-extracting module. The self-extracting module is designed to hold several long strings (could be any length, up to the max 2GB strings I suppose), and a few short snippets of code to decompress the strings and import the modules they represent.

Anyway, when my macro builds the self extracting module it needs to save the really long strings (I'm saving as hardcoded Consts). But if they are too long (>1024 ish) to fit on a single line in the VBA editor, I get errors.

How should I format these hardcoded strings so that I can save them either as Consts or in another way in my self-extracting module? So far I've been saving each string as several Const declarations in 1000 character chunks, but it would be preferable to have one string per item only.

Upvotes: 1

Views: 950

Answers (1)

cyboashu
cyboashu

Reputation: 10433

As suggested in the comment, you can use custom XML part to store information inside the workbook.

Here’s the code:

Option Explicit

Public Sub AddCustomPart()

    Dim oXmlPart    As CustomXMLPart
    Dim strTest     As String

    strTest = "<Test_ID>123456</Test_ID>"

    Set oXmlPart = ReadCustomPart("Test_ID")

    '/ Check if there is already an elemnt available with same name.
    '/ VBA or Excel Object Model, doesn't perevnt duplicate entries.
    If oXmlPart Is Nothing Then
        Set oXmlPart = ThisWorkbook.CustomXMLParts.Add(strTest)
    Else
        MsgBox oXmlPart.DocumentElement.Text
    End If

End Sub

Function ReadCustomPart(strTest As String) As CustomXMLPart

    Dim oXmlPart    As CustomXMLPart

    For Each oXmlPart In ThisWorkbook.CustomXMLParts
        If Not oXmlPart.DocumentElement Is Nothing Then
            If oXmlPart.SelectSingleNode("/*").BaseName = strTest Then
                Set ReadCustomPart = oXmlPart
                Exit Function
            End If
        End If
    Next

    Set ReadCustomPart = Nothing

End Function

Upvotes: 1

Related Questions