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

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)

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

> 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.

--
Rick (MVP - Excel)


"Bob Phillips" <(E-Mail Removed)> wrote in message
news:Ow5T7N$(E-Mail Removed)...
> 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)
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>
>
>

Thanks Bob

"Bob Phillips" <(E-Mail Removed)> wrote:

>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)

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" <(E-Mail Removed)> wrote:

>> 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.

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)


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote:
>
>>> 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.

(E-Mail Removed) wrote:
> 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

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

Dana DeLouis <(E-Mail Removed)> wrote:

>(E-Mail Removed) wrote:
>> 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

This has been a great learning experience for me.

People like you give me hope for the world.

Thanks

"Rick Rothstein" <(E-Mail Removed)> wrote:

>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.

> 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


(E-Mail Removed) wrote:
> 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
>
> Dana DeLouis <(E-Mail Removed)> wrote:
>
>> (E-Mail Removed) wrote:
>>> 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