Max size of variable?

[erikcw]

Hi all,

Is there a limit to the size of a variable in VBA? Here is my psuedo
code:

Dim temp

'array has about 2000 items (each item is less than 200 letters)
Loop i=0 To UBound(array)
temp = temp + vbNewLine + array(i)
Next

For some reason it won't append past a hundred or so itterations.

Any idea why this is? Do I need to declare the variable as "large" or
something?

Thanks!
Erik

 

[Jim Rech]

String variables have a maximum length of about 64k characters. Maybe
you're exceeding that. Easy to check with Len.

--
Jim
message |
| Hi all,
|
| Is there a limit to the size of a variable in VBA? Here is my psuedo
| code:
|
| Dim temp
|
| 'array has about 2000 items (each item is less than 200 letters)
| Loop i=0 To UBound(array)
| temp = temp + vbNewLine + array(i)
| Next
|
| For some reason it won't append past a hundred or so itterations.
|
| Any idea why this is? Do I need to declare the variable as "large" or
| something?
|
| Thanks!
| Erik
|
|
| --
| erikcw
| ------------------------------------------------------------------------
| erikcw's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=28797
| View this thread: http://www.excelforum.com/showthread.php?threadid=489190
|

 

[erikcw]

We don't have that limitation in PHP. What alternative do I have for
storing larger amounts of data?

Thanks!

 

[Tom Ogilvy]

I guess any workaround would depend on what you eventually want to do with a
400,000 character string

 

[erikcw]

Well, I need to have it in a variable because I will be using tha
variable in a com object. Would it work to store the data in a singl
excel cell, and use that as a variable

 

[Tom Ogilvy]

No. Limit for a cell is around 32K

 

[erikcw]

That's strange. I switched from using a variable to a cell, and the
problem appeared to stop. (It took more data than the variable did, but
I haven't fully tested it yet.)

Are there any other objects I can use to hold the data temporarily
before I assign it to this external object?

Thanks!

Erik

 

[NickHK]

Jim,
For fixed length, but for variable length much more.

<From Excel Help>
String Data Type

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.
</From Excel Help>

NickHK

 

[erikcw]

I've tried
Dim primary
Dim Primary$
Dim primary as String

In all cases, after about 100 words, the rest of the data is cut off.

This thing is driving me nuts!!!

Any more ideas?

 

[erikcw]

Here is a test I ran in my application to see what would happen


Code
-------------------

Option Explicit
' 2004-11-11, Created

Private Sub CommandButton1_Click()


'MsgBox "This macro reads data from Excel sheet and submits to com."

Dim iim1, iret, row, totalrows, kw, mrls, primary As String, sKords, i, i2, domains(), temp, anchor, domainskw

Set iim1 = CreateObject("IM.iim")

iret = iim1.iimInit
iret = iim1.iimDisplay("Submitting Data from Excel")


iret = iim1.iimPlay("login")

totalrows = ActiveSheet.UsedRange.Rows.Count
For row = 2 To totalrows

If Cells(row, 24).Value < 1 And Cells(row, 18).Value = 1 Then

'test primary variable
primary = Empty
For i = 0 To 5000
primary = primary & "word phrase " & CStr(i) & vbNewLine
Next
MsgBox primary


-------------------


msgbox primary:

Code
-------------------

word phrase 0
word phrase 1
word phrase n...
word phrase 63
word phra

-------------------


So - primary is dimensioned as a variable leangth string, but produce
this result. I've also trie variant - same result.

Any ideas