Max size of variable?
 

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...o&userid=28797
View this thread: http://www.excelforum.com/showthread...hreadid=489190

Max size of variable?
 

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

--
Jim
"erikcw" wrote in
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...o&userid=28797
| View this thread: http://www.excelforum.com/showthread...hreadid=489190
|

Max size of variable?
 

No. Limit for a cell is around 32K

--
Regards,
Tom Ogilvy


"erikcw" wrote in
message ...

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


--
erikcw
------------------------------------------------------------------------
erikcw's Profile:
http://www.excelforum.com/member.php...o&userid=28797
View this thread: http://www.excelforum.com/showthread...hreadid=489190

Max size of variable?
 

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


--
erikcw
------------------------------------------------------------------------
erikcw's Profile: http://www.excelforum.com/member.php...o&userid=28797
View this thread: http://www.excelforum.com/showthread...hreadid=489190

Max size of variable?
 

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

"Jim Rech" wrote in message
...
String variables have a maximum length of about 64k characters. Maybe
you're exceeding that. Easy to check with Len.

--
Jim
"erikcw" wrote in
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...o&userid=28797
| View this thread:
http://www.excelforum.com/showthread...hreadid=489190
|

Max size of variable?
 

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?


NickHK Wrote:
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

"Jim Rech" wrote in message
...
String variables have a maximum length of about 64k characters.
Maybe
you're exceeding that. Easy to check with Len.

--
Jim
"erikcw" wrote
in
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...o&userid=28797
| View this thread:
http://www.excelforum.com/showthread...hreadid=489190
|




--
erikcw
------------------------------------------------------------------------
erikcw's Profile: http://www.excelforum.com/member.php...o&userid=28797
View this thread: http://www.excelforum.com/showthread...hreadid=489190

Max size of variable?
 

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

--
erikc
-----------------------------------------------------------------------
erikcw's Profile: http://www.excelforum.com/member.php...fo&userid=2879
View this thread: http://www.excelforum.com/showthread.php?threadid=48919