excel vba redim limit length of element contents

Question

excel vba redim limit length of element contents

Gavin Jarvie 1

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

Erin Ding-MSFT 4,476 Reputation points

2021-05-11T01:20:24.313+00:00

Hi @Gavin Jarvie
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.
Tom van Stiphout 1,861 Reputation points MVP Volunteer Moderator

2021-05-12T03:42:38.003+00:00

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.
Gavin Jarvie 1 Reputation point

2021-05-14T10:19:51.447+00:00

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

1 answer

Your answer

Erin Ding-MSFT 4,476 Reputation points

2021-05-11T01:20:24.313+00:00

Hi @Gavin Jarvie
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.
Tom van Stiphout 1,861 Reputation points MVP Volunteer Moderator

2021-05-12T03:42:38.003+00:00

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.
Gavin Jarvie 1 Reputation point

2021-05-14T10:19:51.447+00:00

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

Answer 1

Viorel 122.6K

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.

Gavin Jarvie 1 Reputation point

2021-05-12T10:25:58.347+00:00

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.

Share via

excel vba redim limit length of element contents

1 answer

Your answer