Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code ReDim Preserve when "ReDim strArr(1 To 100, 1 To 3)"
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code ReDim Preserve when "ReDim strArr(1 To 100, 1 To 3)"
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) wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code ReDim Preserve when "ReDim strArr(1 To 100, 1 To 3)"
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" wrote in message ... 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) wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code ReDim Preserve when "ReDim strArr(1 To 100, 1 To 3)"
Thanks Bob
"Bob Phillips" 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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code ReDim Preserve when "ReDim strArr(1 To 100, 1 To 3)"
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" 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code ReDim Preserve when "ReDim strArr(1 To 100, 1 To 3)"
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) wrote in message ... 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" 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code ReDim Preserve when "ReDim strArr(1 To 100, 1 To3)"
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code ReDim Preserve when "ReDim strArr(1 To 100, 1 To 3)"
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 wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code ReDim Preserve when "ReDim strArr(1 To 100, 1 To 3)"
This has been a great learning experience for me.
People like you give me hope for the world. Thanks "Rick Rothstein" 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code ReDim Preserve when "ReDim strArr(1 To 100, 1 To 3)"
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 Dana DeLouis wrote: 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 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Redim preserve does not work for me | Excel Programming | |||
Redim Preserve question | Excel Programming | |||
redim preserve | Excel Programming | |||
Code for optimization, ReDim Preserve not handling it well, HELP please! | Excel Programming | |||
Redim Preserve doesn't work | Excel Programming |