How to code ReDim Preserve when "ReDim strArr(1 To 100, 1 To 3)"

[EagleOne]

Dim strArr() As String
Dim iCtr as Long

ReDim strArr(1 To 100, 1 To 3)
......
......

Getting an error if I code:
ReDim Preserve strArr(1 To iCtr).elements(1 to 3)

Any thoughts appreciated!

EagleOne

 

[Bob Phillips]

You can only redim the 2nd dimension

Dim strArr() As String
Dim iCtr as Long

ReDim strArr(1 To 3, 1 To 100)
......
......

Getting an error if I code:
ReDim Preserve strArr(1 to 3,1 To iCtr)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein]

You can only redim the 2nd dimension

Just to clarify Bob's comment... you can only ReDim the 2nd dimension when
using the Preserve keyword. Without the Preserve keyword, you can change any
dimension, or even the number of dimensions, but doing so, of course, loses
any stored information.

 

[EagleOne]

Thanks Bob

 

[EagleOne]

Rick, using the following code from Jim Cone modified by me:

Sub FigureItOut()
Dim N As Long
Dim m As Long
Dim X As Long
Dim A As Long
Dim strWhat As String
Dim strGiven As String
Dim vThings As Variant
Dim strArr() As String

ReDim strArr(1 To 100, 1 To 3)

'some extras in the string
strGiven = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038.66^35+[C:\123]'Clos-6ing'!E3^1"
vThings = Array("-", "+", "^", "/", "*")

m = 0
For N = 0 To UBound(vThings)
Do
m = InStr(m + 1, strGiven, vThings(N), vbBinaryCompare)
If m > 0 Then
If Mid$(strGiven, m + 1, 1) Like "#" Then
A = m + 1
strWhat = Mid$(strGiven, m, 2)
Do
strWhat = strWhat & IIf(Mid$(strGiven, A + 1, 1) Like "#" Or _
Mid$(strGiven, A + 1, 1) = ".", Mid$(strGiven, A + 1, 1), "")
A = A + 1
Loop While Mid$(strGiven, A + 1, 1) Like "#" Or Mid$(strGiven, A + 1, 1) = "."
X = X + 1
strArr(X, 1) = strWhat
strArr(X, 2) = m
strArr(X, 3) = Len(strArr(X, 1))
Debug.Print "CharPlusSign: "; strArr(X, 1) & Space(5) & "StartPosInStr: " & _
strArr(X, 2) & Space(5) & "StrLength: " & strArr(X, 3)
End If
Else
Exit Do
End If
Loop
Next

'ReDim Preserve strArr(1 To X)

End Sub

I tried (and it worked) "ReDim strArr(1 To 100, 1 To 3)" but I have 100 elements.

Is there way to Preserve the array above so that I have six "items" each with 3 elements?

EagleOne

 

[Rick Rothstein]

First off, the rule about being able to change ONLY that last dimension when
using the Preserve keyword is a 'fast and hard' rule... there is no way of
getting around it. The reason for its existence has to do with the way VB
stores arrays in memory. Now, if I understand what you are doing, the "1 To
3" dimension isn't going to change, only the "1 To 100" dimension will. If
that is the case, the only way to do what you want is to reverse how you
think of the dimensions. So, where you have...

strArr(1 To 100, 1 To 3)

then just reverse them...

strArr(1 To 3, 1 To 100)

and just specify them in the reverse of the way you do now. Doing this makes
the "1 To 100" dimension the last one and, hence, changeable when using the
Preserve keyword.

--
Rick (MVP - Excel)

Rick, using the following code from Jim Cone modified by me:

Sub FigureItOut()
Dim N As Long
Dim m As Long
Dim X As Long
Dim A As Long
Dim strWhat As String
Dim strGiven As String
Dim vThings As Variant
Dim strArr() As String

ReDim strArr(1 To 100, 1 To 3)

'some extras in the string
strGiven = "-9'Min. Int.'!F26-'Min.-7
Int.'!F31+28038.66^35+[C:\123]'Clos-6ing'!E3^1"
vThings = Array("-", "+", "^", "/", "*")

m = 0
For N = 0 To UBound(vThings)
Do
m = InStr(m + 1, strGiven, vThings(N), vbBinaryCompare)
If m > 0 Then
If Mid$(strGiven, m + 1, 1) Like "#" Then
A = m + 1
strWhat = Mid$(strGiven, m, 2)
Do
strWhat = strWhat & IIf(Mid$(strGiven, A + 1, 1) Like "#"
Or _
Mid$(strGiven, A + 1, 1) = ".", Mid$(strGiven, A + 1, 1),
"")
A = A + 1
Loop While Mid$(strGiven, A + 1, 1) Like "#" Or
Mid$(strGiven, A + 1, 1) = "."
X = X + 1
strArr(X, 1) = strWhat
strArr(X, 2) = m
strArr(X, 3) = Len(strArr(X, 1))
Debug.Print "CharPlusSign: "; strArr(X, 1) & Space(5) &
"StartPosInStr: " & _
strArr(X, 2) & Space(5) & "StrLength: " & strArr(X, 3)
End If
Else
Exit Do
End If
Loop
Next

'ReDim Preserve strArr(1 To X)

End Sub

I tried (and it worked) "ReDim strArr(1 To 100, 1 To 3)" but I have 100
elements.

Is there way to Preserve the array above so that I have six "items" each
with 3 elements?

EagleOne

Just to clarify Bob's comment... you can only ReDim the 2nd dimension when
using the Preserve keyword. Without the Preserve keyword, you can change
any
dimension, or even the number of dimensions, but doing so, of course,
loses
any stored information.

 

[Dana DeLouis]

Dim strArr() As String
Dim iCtr as Long

ReDim strArr(1 To 100, 1 To 3)
......
......

Getting an error if I code:
ReDim Preserve strArr(1 To iCtr).elements(1 to 3)

Any thoughts appreciated!

EagleOne

Any ideas here you can use?

Sub Demo()
Dim m()
ReDim m(1 To 1, 1 To 1)
m(1, 1) = "Test Data"

With WorksheetFunction
ReDim Preserve m(1 To 1, 1 To 3)
m = .Transpose(m)
ReDim Preserve m(1 To 3, 1 To 100)
m = .Transpose(m)
End With
End Sub

'It's resized to (100 by 3)
= = =
HTH :>)
Dana DeLouis

 

[EagleOne]

Dana, much appreciated!

As you can tell, I am new to Array VBA code.

Therefore I would never have expected that one could use a worksheet function to manipulate a VBA
created Array.

EagleOne

 

[EagleOne]

This has been a great learning experience for me.

People like you give me hope for the world.

Thanks

 

[Dana DeLouis]

I am new to Array VBA code.

Therefore I would never have expected that one could use a worksheet

function to manipulate a VBA created Array.

Hi. Just some ideas to add to your Library you may find interesting.
Given a 3*3 array, take the 3rd column of 'All' rows, and sum them up.

Sub Demo()
Dim m, t
Const All As Long = 0
m = [{1,2,3; 21,22,23; 31,32,33}]
With WorksheetFunction
t = .Sum(.Index(m, All, 3)) 'Or just use 0 for 'All
End With
End Sub
'3+23+33 = 59

A good technique when working with Arrays is to use the "Locals Window"
when stepping through code. It's a good way to check one's array dimensions.
= = = =
Dana DeLouis

 

[EagleOne]

Very powerful. Your Sub provides to me a quantum leap in internalizing arrays.

I have wondered how I could sum a "column" in Arrays. Previously, I had been unknowingly using
arrays (via w/s Row,Column ranges). Never would I have thought of using the Index function coupled
with arrays to Sum.

Do you have any other quick examples? I.e. a VLookup for Arrays?

Would it be something like:

Sub Demo()
Dim m, t
Const All As Long = 0
m = [{1,2,3; 21,22,23; 31,32,33}]
With WorksheetFunction
t = .VLookup(21,(.Index(m, All, 1),FALSE)
End With
End Sub

Also, why use "Const" vs All = 0?

Also, what is advantage for m=("One", "Two", "Three") vs
m=({"One", "Two", "Three"}) ?

EagleOne

I am new to Array VBA code.
Therefore I would never have expected that one could use a worksheet

function to manipulate a VBA created Array.

Hi. Just some ideas to add to your Library you may find interesting.
Given a 3*3 array, take the 3rd column of 'All' rows, and sum them up.

Sub Demo()
Dim m, t
Const All As Long = 0
m = [{1,2,3; 21,22,23; 31,32,33}]
With WorksheetFunction
t = .Sum(.Index(m, All, 3)) 'Or just use 0 for 'All
End With
End Sub
'3+23+33 = 59

A good technique when working with Arrays is to use the "Locals Window"
when stepping through code. It's a good way to check one's array dimensions.
= = = =
Dana DeLouis

Dana, much appreciated!

As you can tell, I am new to Array VBA code.

Therefore I would never have expected that one could use a worksheet function to manipulate a VBA
created Array.

EagleOne