Very Long String

Lupison

Board Regular
Joined
Jun 14, 2006
Messages
50
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?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

Board Regular
Joined
May 24, 2006
Messages
121
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

Board Regular
Joined
Jun 14, 2006
Messages
50
Here's the start of the function

Code:
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

Board Regular
Joined
May 24, 2006
Messages
121

ADVERTISEMENT

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:

Code:
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

Board Regular
Joined
Jun 14, 2006
Messages
50

ADVERTISEMENT

Even doing this

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

Still results in a string 256 characters long.
 

MagiCat

Board Regular
Joined
May 24, 2006
Messages
121
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

Board Regular
Joined
Jun 14, 2006
Messages
50
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.
 

Forum statistics

Threads
1,136,305
Messages
5,674,984
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top