Very Long String

[Lupison]

I have a very long string I need to use in VBA, but it's getting truncated by VBA at the end. Honeslty it really isn't all that long only 255+ characters, C# could handle much much longer strings. Is there a long string type class in VBA?

 

[pgc01]

Hi Lupison

A string in vba can contain up to 2 billion (2^31) characters.

Can you explain what you are doing?

Kind regards
PGC

 

[MagiCat]

Do you have any sample code? The VBA code help for VBA says:

There are two kinds of strings: variable-length and fixed-length strings.

A variable-length string can contain up to approximately 2 billion (2^31) characters.

A fixed-length string can contain 1 to approximately 64K (2^16) characters.

 

[Lupison]

Here's the start of the function

Function LoadCSV(CSV1or2 As Integer, fileLoc As Variant, dt As Date, shortFileLoc As Variant, tempFileLoc As String, batchCalLoc As String, templateFileLoc As String) As Boolean
    
    Dim wbThis As Workbook
    Set wbThis = ThisWorkbook
    Dim wsInput As Worksheet
    Dim wsOutput As Worksheet
    Dim wbInput As Workbook
    Dim outputSheetName As String
    Dim copyRange As Range
    Dim pasteRange As Range
    Dim rng As Range
    Dim rootDir As String
    Dim fso As New FileSystemObject
    Dim str As String

    
            If (fso.FileExists(tempFileLoc)) Then _
                fso.DeleteFile (tempFileLoc)
            
            str = batchCalLoc & " /nc """ & fileLoc & """ """ & tempFileLoc & """ /template """ & templateFileLoc & """"
            Shell (str)
            
            c = 1
            maxWaitCount = 60
            While (fso.FileExists(tempFileLoc) = False And c < maxWaitCount)
                Application.Wait (Now + TimeValue("0:00:03"))
                c = c + 1
            Wend
            
            If (fso.FileExists(tempFileLoc) = False) Then
                GoTo ErrorOut
            End If

 

[MagiCat]

I guess the best way to determine what's going wrong is to break things down to their smallest componet parts.

To test a couple of basic string characteristics I wrote this simple VBA Script:

Sub test()

    Dim x As String
    Dim y As String
    
    x = "12345678901234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
1234567890123456789012345678901234567890
12345678901234567890123456789012345678901234567890"
    MsgBox CStr(Len(x))
    y = x
    MsgBox CStr(Len(y))
    
End Sub

Note that all the numbers are on the same line, I split them to avoid the screen scrolling to the right in HTML.

If you want to see where things are going wrong, just put MsgBox in the program at key points (ok, other people will say do it in the editor and trace it but I'm too lazy to do that)

 

[Lupison]

Or just break the code and "add watch"...

 

[Lupison]

Even doing this

            s = batchCalLoc & " /nc """
            s = s & fileLoc & """ """
            s = s & tempFileLoc
            s = s & """ /template """
            s = s & templateFileLoc & """"

Still results in a string 256 characters long.

 

[MagiCat]

My post was more a test to see if the string function worked properly with strings larger than 255 characters which it did for me.

My suggesting is to break down your program line by line to determine where things are going wrong. Sounds like you're assuming that it's the string copying that is causing things to go wrong.

I'm not entirely sure where you're saying your program is going wrong, I see a shell command, not sure the limits of that, could it be that the string is more than 256 characters but the shell command isn't allowing anything above that?

 

[Lupison]

the work around I did was using a cell in the sheet itself. Most definately wasn't the Shell command. I have no clue why it doesn't/didn't work, nor do I realy care at this point.