question

GavinJarvie-7824 avatar image
0 Votes"
GavinJarvie-7824 asked GavinJarvie-7824 commented

excel vba redim limit length of element contents

I am trying to define an array in vba that is based on a variable number of elements but I want to try and limit the size of the contents of the array elements to a calculated number of characters is this possible?

for example a normal string declaration of size 5 characters in length can be defined as:

Dim MyString As String * 5

is it possible to do something like the below??

ReDim MyArray (1 to MyLastRow) As String * MyCharCount

Reason I want to be able to do this is to loop through a huge number of rows inside an array but want to keep memory requirements lower to enable running code on larger arrays of data inside memory without having to chunk it up. The data being analysed is of type string.

I get a constant expression required error when I compile it so I am guessing no. If any MSFT expert / developer knows a way around this (or anyone else) I'd love to hear from you please.

thanks


office-vba-dev
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Define huge.

const huge as long = 1234567
dim s() as string 'not a fixed length string, so no trailing spaces
redim s(huge) 'index 0 to huge-1

You did not say where the data for the array is coming from. A recent poster had it come from an Excel Range, and I suggested that Range was essentially an array so sucking the data into an array was superfluous. Maybe this applies to your case as well.

Large amounts of data are better dealt with by a database engine, where set-based operations are orders of magnitude faster than RBAR.

1 Vote 1 ·

Thanks - appreciate the time and advice from you and also Viorel-1

0 Votes 0 ·

Hi @GavinJarvie-7824
As your issue is more related to VBA code, but the other two tags "office-excel-itpro" and "office-itpro" focus on general issues about Excel/Office clients, I will remove them. Thanks for your understanding.

0 Votes 0 ·

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered GavinJarvie-7824 commented

I think that it will work if MyCharCount is a constant:

 Const MyCharCount = 5
 . . .
 ReDim MyArray(1 To MyLastRow) As String * MyCharCount

However, note that definitions like String*5 allocate five characters, even if the text is shorter. For example:

 Dim s As String * 5
 s = "ab"
 MsgBox "[" & s & "]" ' shows “[ab   ]”
 MsgBox Len(s) ' shows 5

It seems that the system appends spaces. Therefore, in contrast with normal strings, this approach requires additional memory for trailing spaces.

Theoretically you can declare a class module, that includes the Let and Get properties and which truncates the long strings or raises errors.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks...will have a look at class modules as suggested and see how I progress


I have a column of strings of various length. Am attempting to get the lengths of each cell in the column which I have done. What I was attempting to do is to load that into an array but set the length of each array element to the length of the string so fo example... if A1 was of length 5 the array MyArray(1) would be set equal to a type string of length 5. If A2 was of length 10 the element MyArray(2) would be type string of length 10 and so on to try and define my array element lengths to each value along lines of

ReDim MyArray (1 to MyLastRow) As String * MyCharCount(i)

The reason I am trying to do this is because I could be working with a table of data which is upwards of 50k rows of data in an excel spreadsheet and don't want an array of 50k elements wasting valuable memory.

0 Votes 0 ·