|
ReDim not working as expected. Array expert needed.
Greetings! This one has me totally baffled.
In Sub ArrayStudies1, MyArray is dimensioned as an array of type Integer. MyArray is then ReDim'ed, Ranges A1 through A3 are set to integers, successive array locations are set to these ranges, and the first array location is successfully displayed. Sub ArrayStudies2 differs from Sub ArrayStudies1 only in that the array MyArray is set to a 3 cell range having integers. In this case, upon execution, I get the message " "Run-time error '13': Type mismatch". Sub ArrayStudies3 differs from Sub ArrayStudies2 only in that MyArray is declared as a dynamic array of type Variant rather than as type Integer. And this worked perfectly with no error messages. Why can't MyArray be declared as an array of type Integer in Sub ArrayStudies2? (At the top of the module containing these three subs is: Option Base 1) Sub ArrayStudies1() ' example patterned after Excel VBA Help on ReDim statement: ' This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1. Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray(1) = Range("A1") MyArray(2) = Range("A2") MyArray(3) = Range("A3") ' The following instruction works fine: MsgBox "MyArray(1) = " & MyArray(1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr gives: "Run-time error '13': Type mismatch" MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies3() Dim MyArray() ' Declare dynamic array of type Variant. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr works fine MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub May you have a most blessed day! Sincerely, Michael Fitzpatrick |
ReDim not working as expected. Array expert needed.
Hi,
In your 3rd example the variant array has the flexibility to allow a range to be assigned to it. I don't think you can do that with a explicitely "typed" array like in your second example. Alternatively you could use this modified version. Its better this way as you have to explicitely size both dimensions of your array, so it is more clear what you are actually doing. When assigning a range to an array, even if its only one row (or column) it is still a 2 dimensional array (or matrix for visualization purposes). Also, instead of passing the entire range in one call, you have to do it one element at a time. I did this with a small loop, which isn't really necessary in this case, but would be if you were populating the array with a large number of values. I used the UBound of the array to return the upper limit of the array. This is a common approach to identify the upper limit of the array, for purposes like this. Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. Dim i As Integer ReDim MyArray(1 To 3, 1 To 1) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i ' The follwg instr gives: "Run-time error '13': Type mismatch" 'MyArray = Range("A1:A3") MsgBox "MyArray(1, 1) = " & MyArray(1, 1) MsgBox "MyArray(2, 1) = " & MyArray(2, 1) MsgBox "MyArray(3, 1) = " & MyArray(3, 1) End Sub Thanks Ariel "MichaelDavid" wrote in message ... Greetings! This one has me totally baffled. In Sub ArrayStudies1, MyArray is dimensioned as an array of type Integer. MyArray is then ReDim'ed, Ranges A1 through A3 are set to integers, successive array locations are set to these ranges, and the first array location is successfully displayed. Sub ArrayStudies2 differs from Sub ArrayStudies1 only in that the array MyArray is set to a 3 cell range having integers. In this case, upon execution, I get the message " "Run-time error '13': Type mismatch". Sub ArrayStudies3 differs from Sub ArrayStudies2 only in that MyArray is declared as a dynamic array of type Variant rather than as type Integer. And this worked perfectly with no error messages. Why can't MyArray be declared as an array of type Integer in Sub ArrayStudies2? (At the top of the module containing these three subs is: Option Base 1) Sub ArrayStudies1() ' example patterned after Excel VBA Help on ReDim statement: ' This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1. Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray(1) = Range("A1") MyArray(2) = Range("A2") MyArray(3) = Range("A3") ' The following instruction works fine: MsgBox "MyArray(1) = " & MyArray(1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr gives: "Run-time error '13': Type mismatch" MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies3() Dim MyArray() ' Declare dynamic array of type Variant. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr works fine MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub May you have a most blessed day! Sincerely, Michael Fitzpatrick |
ReDim not working as expected. Array expert needed.
Hi Ariel!
Thanks for your speedy reply. The original reason for my post is that the code in question typifies a lot of the code in a hugh macro which takes hours to run. Consequently, I have performed timing studies on the various possible ways to code certain tasks. For example, the instruction of the third subroutine which does: MyArray = Range("A1:A3") ' Initialize array. runs much quicker than the code which does the For Next Loop of example 2 as you modified it: For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i Therefore I am looking for a way to dimension MyArray as an Integer like so: Dim MyArray() As Integer rather than as the Variant, Dim MyArray() (which results in execution without error), for even greater speed. Hopefully some "super expert" in this discussion group will come up with a way of dimensioning MyArray() As Integer such that MyArray can be set to Range("A1:A3") or an equivalent without a Type Mismatch upon execution. Again, thanks for your response. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ariel Dugan" wrote: Hi, In your 3rd example the variant array has the flexibility to allow a range to be assigned to it. I don't think you can do that with a explicitely "typed" array like in your second example. Alternatively you could use this modified version. Its better this way as you have to explicitely size both dimensions of your array, so it is more clear what you are actually doing. When assigning a range to an array, even if its only one row (or column) it is still a 2 dimensional array (or matrix for visualization purposes). Also, instead of passing the entire range in one call, you have to do it one element at a time. I did this with a small loop, which isn't really necessary in this case, but would be if you were populating the array with a large number of values. I used the UBound of the array to return the upper limit of the array. This is a common approach to identify the upper limit of the array, for purposes like this. Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. Dim i As Integer ReDim MyArray(1 To 3, 1 To 1) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i ' The follwg instr gives: "Run-time error '13': Type mismatch" 'MyArray = Range("A1:A3") MsgBox "MyArray(1, 1) = " & MyArray(1, 1) MsgBox "MyArray(2, 1) = " & MyArray(2, 1) MsgBox "MyArray(3, 1) = " & MyArray(3, 1) End Sub Thanks Ariel "MichaelDavid" wrote in message ... Greetings! This one has me totally baffled. In Sub ArrayStudies1, MyArray is dimensioned as an array of type Integer. MyArray is then ReDim'ed, Ranges A1 through A3 are set to integers, successive array locations are set to these ranges, and the first array location is successfully displayed. Sub ArrayStudies2 differs from Sub ArrayStudies1 only in that the array MyArray is set to a 3 cell range having integers. In this case, upon execution, I get the message " "Run-time error '13': Type mismatch". Sub ArrayStudies3 differs from Sub ArrayStudies2 only in that MyArray is declared as a dynamic array of type Variant rather than as type Integer. And this worked perfectly with no error messages. Why can't MyArray be declared as an array of type Integer in Sub ArrayStudies2? (At the top of the module containing these three subs is: Option Base 1) Sub ArrayStudies1() ' example patterned after Excel VBA Help on ReDim statement: ' This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1. Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray(1) = Range("A1") MyArray(2) = Range("A2") MyArray(3) = Range("A3") ' The following instruction works fine: MsgBox "MyArray(1) = " & MyArray(1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr gives: "Run-time error '13': Type mismatch" MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies3() Dim MyArray() ' Declare dynamic array of type Variant. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr works fine MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub May you have a most blessed day! Sincerely, Michael Fitzpatrick |
ReDim not working as expected. Array expert needed.
Hi Ariel!
Thanks for your speedy reply. The original reason for my post is that the code in question typifies a lot of the code in a hugh macro which takes hours to run. Consequently, I have performed timing studies on the various possible ways to code certain tasks. For example, the instruction of the third subroutine (ArrayStudies3()) which does: MyArray = Range("A1:A3") ' Initialize array. runs much quicker than the code which does the For Next Loop of example 2 as you modified it: For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i Therefore I am looking for a way to dimension MyArray as an Integer like so: Dim MyArray() As Integer rather than as the Variant, Dim MyArray() (which results in execution without error), for even greater speed. Hopefully some "super expert" in this discussion group will come up with a way of dimensioning MyArray() As Integer such that MyArray can be set to Range("A1:A3") or an equivalent without a Type Mismatch upon execution. Again, thanks for your response. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ariel Dugan" wrote: Hi, In your 3rd example the variant array has the flexibility to allow a range to be assigned to it. I don't think you can do that with a explicitely "typed" array like in your second example. Alternatively you could use this modified version. Its better this way as you have to explicitely size both dimensions of your array, so it is more clear what you are actually doing. When assigning a range to an array, even if its only one row (or column) it is still a 2 dimensional array (or matrix for visualization purposes). Also, instead of passing the entire range in one call, you have to do it one element at a time. I did this with a small loop, which isn't really necessary in this case, but would be if you were populating the array with a large number of values. I used the UBound of the array to return the upper limit of the array. This is a common approach to identify the upper limit of the array, for purposes like this. Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. Dim i As Integer ReDim MyArray(1 To 3, 1 To 1) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i ' The follwg instr gives: "Run-time error '13': Type mismatch" 'MyArray = Range("A1:A3") MsgBox "MyArray(1, 1) = " & MyArray(1, 1) MsgBox "MyArray(2, 1) = " & MyArray(2, 1) MsgBox "MyArray(3, 1) = " & MyArray(3, 1) End Sub Thanks Ariel "MichaelDavid" wrote in message ... Greetings! This one has me totally baffled. In Sub ArrayStudies1, MyArray is dimensioned as an array of type Integer. MyArray is then ReDim'ed, Ranges A1 through A3 are set to integers, successive array locations are set to these ranges, and the first array location is successfully displayed. Sub ArrayStudies2 differs from Sub ArrayStudies1 only in that the array MyArray is set to a 3 cell range having integers. In this case, upon execution, I get the message " "Run-time error '13': Type mismatch". Sub ArrayStudies3 differs from Sub ArrayStudies2 only in that MyArray is declared as a dynamic array of type Variant rather than as type Integer. And this worked perfectly with no error messages. Why can't MyArray be declared as an array of type Integer in Sub ArrayStudies2? (At the top of the module containing these three subs is: Option Base 1) Sub ArrayStudies1() ' example patterned after Excel VBA Help on ReDim statement: ' This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1. Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray(1) = Range("A1") MyArray(2) = Range("A2") MyArray(3) = Range("A3") ' The following instruction works fine: MsgBox "MyArray(1) = " & MyArray(1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr gives: "Run-time error '13': Type mismatch" MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies3() Dim MyArray() ' Declare dynamic array of type Variant. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr works fine MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub May you have a most blessed day! Sincerely, Michael Fitzpatrick |
ReDim not working as expected. Array expert needed.
A few notes.
The variable that accepts the values in a range should be declared as a non array variant. Dim MyArray As Variant Don't redim it prior to populating it, because VBA does that anyway, using the correct dimensions. When you redim the variable using: ReDim MyArray(3) that is the same as redimming it like this: ReDim MyArray(0 to 3) because VBA assumes a lower bound of 0 (0-base) unless told otherwise. When you then assign the values in A1:A3 MyArray = Range("A1:A3").Value (use the .Value property of the range, even though it's the default) the array comes out dimensioned as MyArray(1 to 3, 1 to 1) because the worksheet range is a 2D array, and it is treated as a 1-base array (lower bound is 1 not 0). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Ariel! Thanks for your speedy reply. The original reason for my post is that the code in question typifies a lot of the code in a hugh macro which takes hours to run. Consequently, I have performed timing studies on the various possible ways to code certain tasks. For example, the instruction of the third subroutine (ArrayStudies3()) which does: MyArray = Range("A1:A3") ' Initialize array. runs much quicker than the code which does the For Next Loop of example 2 as you modified it: For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i Therefore I am looking for a way to dimension MyArray as an Integer like so: Dim MyArray() As Integer rather than as the Variant, Dim MyArray() (which results in execution without error), for even greater speed. Hopefully some "super expert" in this discussion group will come up with a way of dimensioning MyArray() As Integer such that MyArray can be set to Range("A1:A3") or an equivalent without a Type Mismatch upon execution. Again, thanks for your response. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ariel Dugan" wrote: Hi, In your 3rd example the variant array has the flexibility to allow a range to be assigned to it. I don't think you can do that with a explicitely "typed" array like in your second example. Alternatively you could use this modified version. Its better this way as you have to explicitely size both dimensions of your array, so it is more clear what you are actually doing. When assigning a range to an array, even if its only one row (or column) it is still a 2 dimensional array (or matrix for visualization purposes). Also, instead of passing the entire range in one call, you have to do it one element at a time. I did this with a small loop, which isn't really necessary in this case, but would be if you were populating the array with a large number of values. I used the UBound of the array to return the upper limit of the array. This is a common approach to identify the upper limit of the array, for purposes like this. Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. Dim i As Integer ReDim MyArray(1 To 3, 1 To 1) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i ' The follwg instr gives: "Run-time error '13': Type mismatch" 'MyArray = Range("A1:A3") MsgBox "MyArray(1, 1) = " & MyArray(1, 1) MsgBox "MyArray(2, 1) = " & MyArray(2, 1) MsgBox "MyArray(3, 1) = " & MyArray(3, 1) End Sub Thanks Ariel "MichaelDavid" wrote in message ... Greetings! This one has me totally baffled. In Sub ArrayStudies1, MyArray is dimensioned as an array of type Integer. MyArray is then ReDim'ed, Ranges A1 through A3 are set to integers, successive array locations are set to these ranges, and the first array location is successfully displayed. Sub ArrayStudies2 differs from Sub ArrayStudies1 only in that the array MyArray is set to a 3 cell range having integers. In this case, upon execution, I get the message " "Run-time error '13': Type mismatch". Sub ArrayStudies3 differs from Sub ArrayStudies2 only in that MyArray is declared as a dynamic array of type Variant rather than as type Integer. And this worked perfectly with no error messages. Why can't MyArray be declared as an array of type Integer in Sub ArrayStudies2? (At the top of the module containing these three subs is: Option Base 1) Sub ArrayStudies1() ' example patterned after Excel VBA Help on ReDim statement: ' This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1. Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray(1) = Range("A1") MyArray(2) = Range("A2") MyArray(3) = Range("A3") ' The following instruction works fine: MsgBox "MyArray(1) = " & MyArray(1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr gives: "Run-time error '13': Type mismatch" MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies3() Dim MyArray() ' Declare dynamic array of type Variant. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr works fine MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub May you have a most blessed day! Sincerely, Michael Fitzpatrick |
ReDim not working as expected. Array expert needed.
Hi Michael,
If you send the the entire application, I can look at possible ways to speed it up. There may be other issues causing the bog down. Thanks Ariel "MichaelDavid" wrote in message ... Hi Ariel! Thanks for your speedy reply. The original reason for my post is that the code in question typifies a lot of the code in a hugh macro which takes hours to run. Consequently, I have performed timing studies on the various possible ways to code certain tasks. For example, the instruction of the third subroutine (ArrayStudies3()) which does: MyArray = Range("A1:A3") ' Initialize array. runs much quicker than the code which does the For Next Loop of example 2 as you modified it: For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i Therefore I am looking for a way to dimension MyArray as an Integer like so: Dim MyArray() As Integer rather than as the Variant, Dim MyArray() (which results in execution without error), for even greater speed. Hopefully some "super expert" in this discussion group will come up with a way of dimensioning MyArray() As Integer such that MyArray can be set to Range("A1:A3") or an equivalent without a Type Mismatch upon execution. Again, thanks for your response. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ariel Dugan" wrote: Hi, In your 3rd example the variant array has the flexibility to allow a range to be assigned to it. I don't think you can do that with a explicitely "typed" array like in your second example. Alternatively you could use this modified version. Its better this way as you have to explicitely size both dimensions of your array, so it is more clear what you are actually doing. When assigning a range to an array, even if its only one row (or column) it is still a 2 dimensional array (or matrix for visualization purposes). Also, instead of passing the entire range in one call, you have to do it one element at a time. I did this with a small loop, which isn't really necessary in this case, but would be if you were populating the array with a large number of values. I used the UBound of the array to return the upper limit of the array. This is a common approach to identify the upper limit of the array, for purposes like this. Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. Dim i As Integer ReDim MyArray(1 To 3, 1 To 1) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i ' The follwg instr gives: "Run-time error '13': Type mismatch" 'MyArray = Range("A1:A3") MsgBox "MyArray(1, 1) = " & MyArray(1, 1) MsgBox "MyArray(2, 1) = " & MyArray(2, 1) MsgBox "MyArray(3, 1) = " & MyArray(3, 1) End Sub Thanks Ariel "MichaelDavid" wrote in message ... Greetings! This one has me totally baffled. In Sub ArrayStudies1, MyArray is dimensioned as an array of type Integer. MyArray is then ReDim'ed, Ranges A1 through A3 are set to integers, successive array locations are set to these ranges, and the first array location is successfully displayed. Sub ArrayStudies2 differs from Sub ArrayStudies1 only in that the array MyArray is set to a 3 cell range having integers. In this case, upon execution, I get the message " "Run-time error '13': Type mismatch". Sub ArrayStudies3 differs from Sub ArrayStudies2 only in that MyArray is declared as a dynamic array of type Variant rather than as type Integer. And this worked perfectly with no error messages. Why can't MyArray be declared as an array of type Integer in Sub ArrayStudies2? (At the top of the module containing these three subs is: Option Base 1) Sub ArrayStudies1() ' example patterned after Excel VBA Help on ReDim statement: ' This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1. Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray(1) = Range("A1") MyArray(2) = Range("A2") MyArray(3) = Range("A3") ' The following instruction works fine: MsgBox "MyArray(1) = " & MyArray(1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr gives: "Run-time error '13': Type mismatch" MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies3() Dim MyArray() ' Declare dynamic array of type Variant. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr works fine MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub May you have a most blessed day! Sincerely, Michael Fitzpatrick |
ReDim not working as expected. Array expert needed.
Hi Ariel:
Greetings! It is very kind of you to look at speeding up my application. The module has 1714 lines. How should I send it?--it would not look very nice in this small box I am typing in. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ariel Dugan" wrote: Hi Michael, If you send the the entire application, I can look at possible ways to speed it up. There may be other issues causing the bog down. Thanks Ariel "MichaelDavid" wrote in message ... Hi Ariel! Thanks for your speedy reply. The original reason for my post is that the code in question typifies a lot of the code in a hugh macro which takes hours to run. Consequently, I have performed timing studies on the various possible ways to code certain tasks. For example, the instruction of the third subroutine (ArrayStudies3()) which does: MyArray = Range("A1:A3") ' Initialize array. runs much quicker than the code which does the For Next Loop of example 2 as you modified it: For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i Therefore I am looking for a way to dimension MyArray as an Integer like so: Dim MyArray() As Integer rather than as the Variant, Dim MyArray() (which results in execution without error), for even greater speed. Hopefully some "super expert" in this discussion group will come up with a way of dimensioning MyArray() As Integer such that MyArray can be set to Range("A1:A3") or an equivalent without a Type Mismatch upon execution. Again, thanks for your response. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ariel Dugan" wrote: Hi, In your 3rd example the variant array has the flexibility to allow a range to be assigned to it. I don't think you can do that with a explicitely "typed" array like in your second example. Alternatively you could use this modified version. Its better this way as you have to explicitely size both dimensions of your array, so it is more clear what you are actually doing. When assigning a range to an array, even if its only one row (or column) it is still a 2 dimensional array (or matrix for visualization purposes). Also, instead of passing the entire range in one call, you have to do it one element at a time. I did this with a small loop, which isn't really necessary in this case, but would be if you were populating the array with a large number of values. I used the UBound of the array to return the upper limit of the array. This is a common approach to identify the upper limit of the array, for purposes like this. Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. Dim i As Integer ReDim MyArray(1 To 3, 1 To 1) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i ' The follwg instr gives: "Run-time error '13': Type mismatch" 'MyArray = Range("A1:A3") MsgBox "MyArray(1, 1) = " & MyArray(1, 1) MsgBox "MyArray(2, 1) = " & MyArray(2, 1) MsgBox "MyArray(3, 1) = " & MyArray(3, 1) End Sub Thanks Ariel "MichaelDavid" wrote in message ... Greetings! This one has me totally baffled. In Sub ArrayStudies1, MyArray is dimensioned as an array of type Integer. MyArray is then ReDim'ed, Ranges A1 through A3 are set to integers, successive array locations are set to these ranges, and the first array location is successfully displayed. Sub ArrayStudies2 differs from Sub ArrayStudies1 only in that the array MyArray is set to a 3 cell range having integers. In this case, upon execution, I get the message " "Run-time error '13': Type mismatch". Sub ArrayStudies3 differs from Sub ArrayStudies2 only in that MyArray is declared as a dynamic array of type Variant rather than as type Integer. And this worked perfectly with no error messages. Why can't MyArray be declared as an array of type Integer in Sub ArrayStudies2? (At the top of the module containing these three subs is: Option Base 1) Sub ArrayStudies1() ' example patterned after Excel VBA Help on ReDim statement: ' This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1. Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray(1) = Range("A1") MyArray(2) = Range("A2") MyArray(3) = Range("A3") ' The following instruction works fine: MsgBox "MyArray(1) = " & MyArray(1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr gives: "Run-time error '13': Type mismatch" MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies3() Dim MyArray() ' Declare dynamic array of type Variant. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr works fine MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub May you have a most blessed day! Sincerely, Michael Fitzpatrick |
ReDim not working as expected. Array expert needed.
Hi Jon!
I tried your notes and could not get the Procedure ArrayStudies14 to work with ReDim, so I commented out the ReDim statement, and ran the procedure to see what would happen: Sub ArrayStudies14() ' with Jon's suggestions from Excel VBA Group Dim MyArray As Variant ' Declare nonarray variant. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray = Range("A1:A3").Value ' ReDim MyArray(3) ' Allocate 3 elements. COMMENTED OUT. MsgBox "MyArray(3) = " & MyArray(3, 1) End Sub Would you believe that it works perfectly without the ReDim? Could an unadvertised Excel VBA feature be that sometimes ReDim is not needed? This is strange, and should be brought to the attention of Microsoft Programming. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: A few notes. The variable that accepts the values in a range should be declared as a non array variant. Dim MyArray As Variant Don't redim it prior to populating it, because VBA does that anyway, using the correct dimensions. When you redim the variable using: ReDim MyArray(3) that is the same as redimming it like this: ReDim MyArray(0 to 3) because VBA assumes a lower bound of 0 (0-base) unless told otherwise. When you then assign the values in A1:A3 MyArray = Range("A1:A3").Value (use the .Value property of the range, even though it's the default) the array comes out dimensioned as MyArray(1 to 3, 1 to 1) because the worksheet range is a 2D array, and it is treated as a 1-base array (lower bound is 1 not 0). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Ariel! Thanks for your speedy reply. The original reason for my post is that the code in question typifies a lot of the code in a hugh macro which takes hours to run. Consequently, I have performed timing studies on the various possible ways to code certain tasks. For example, the instruction of the third subroutine (ArrayStudies3()) which does: MyArray = Range("A1:A3") ' Initialize array. runs much quicker than the code which does the For Next Loop of example 2 as you modified it: For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i Therefore I am looking for a way to dimension MyArray as an Integer like so: Dim MyArray() As Integer rather than as the Variant, Dim MyArray() (which results in execution without error), for even greater speed. Hopefully some "super expert" in this discussion group will come up with a way of dimensioning MyArray() As Integer such that MyArray can be set to Range("A1:A3") or an equivalent without a Type Mismatch upon execution. Again, thanks for your response. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ariel Dugan" wrote: Hi, In your 3rd example the variant array has the flexibility to allow a range to be assigned to it. I don't think you can do that with a explicitely "typed" array like in your second example. Alternatively you could use this modified version. Its better this way as you have to explicitely size both dimensions of your array, so it is more clear what you are actually doing. When assigning a range to an array, even if its only one row (or column) it is still a 2 dimensional array (or matrix for visualization purposes). Also, instead of passing the entire range in one call, you have to do it one element at a time. I did this with a small loop, which isn't really necessary in this case, but would be if you were populating the array with a large number of values. I used the UBound of the array to return the upper limit of the array. This is a common approach to identify the upper limit of the array, for purposes like this. Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. Dim i As Integer ReDim MyArray(1 To 3, 1 To 1) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i ' The follwg instr gives: "Run-time error '13': Type mismatch" 'MyArray = Range("A1:A3") MsgBox "MyArray(1, 1) = " & MyArray(1, 1) MsgBox "MyArray(2, 1) = " & MyArray(2, 1) MsgBox "MyArray(3, 1) = " & MyArray(3, 1) End Sub Thanks Ariel "MichaelDavid" wrote in message ... Greetings! This one has me totally baffled. In Sub ArrayStudies1, MyArray is dimensioned as an array of type Integer. MyArray is then ReDim'ed, Ranges A1 through A3 are set to integers, successive array locations are set to these ranges, and the first array location is successfully displayed. Sub ArrayStudies2 differs from Sub ArrayStudies1 only in that the array MyArray is set to a 3 cell range having integers. In this case, upon execution, I get the message " "Run-time error '13': Type mismatch". Sub ArrayStudies3 differs from Sub ArrayStudies2 only in that MyArray is declared as a dynamic array of type Variant rather than as type Integer. And this worked perfectly with no error messages. Why can't MyArray be declared as an array of type Integer in Sub ArrayStudies2? (At the top of the module containing these three subs is: Option Base 1) Sub ArrayStudies1() ' example patterned after Excel VBA Help on ReDim statement: ' This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1. Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray(1) = Range("A1") MyArray(2) = Range("A2") MyArray(3) = Range("A3") ' The following instruction works fine: MsgBox "MyArray(1) = " & MyArray(1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr gives: "Run-time error '13': Type mismatch" MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies3() Dim MyArray() ' Declare dynamic array of type Variant. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr works fine MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub May you have a most blessed day! Sincerely, Michael Fitzpatrick |
ReDim not working as expected. Array expert needed.
Hi Jon!
I commented out all the ReDims in the hugh procedure which utilizes arrays. Would you believe that the procedure now runs 1.56% faster on average with the same correct results as before? I should mention that the procedure sets arrays to ranges only once in the life of the procedure, so I guess ReDims are really not needed in this procedure. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: A few notes. The variable that accepts the values in a range should be declared as a non array variant. Dim MyArray As Variant Don't redim it prior to populating it, because VBA does that anyway, using the correct dimensions. When you redim the variable using: ReDim MyArray(3) that is the same as redimming it like this: ReDim MyArray(0 to 3) because VBA assumes a lower bound of 0 (0-base) unless told otherwise. When you then assign the values in A1:A3 MyArray = Range("A1:A3").Value (use the .Value property of the range, even though it's the default) the array comes out dimensioned as MyArray(1 to 3, 1 to 1) because the worksheet range is a 2D array, and it is treated as a 1-base array (lower bound is 1 not 0). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Ariel! Thanks for your speedy reply. The original reason for my post is that the code in question typifies a lot of the code in a hugh macro which takes hours to run. Consequently, I have performed timing studies on the various possible ways to code certain tasks. For example, the instruction of the third subroutine (ArrayStudies3()) which does: MyArray = Range("A1:A3") ' Initialize array. runs much quicker than the code which does the For Next Loop of example 2 as you modified it: For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i Therefore I am looking for a way to dimension MyArray as an Integer like so: Dim MyArray() As Integer rather than as the Variant, Dim MyArray() (which results in execution without error), for even greater speed. Hopefully some "super expert" in this discussion group will come up with a way of dimensioning MyArray() As Integer such that MyArray can be set to Range("A1:A3") or an equivalent without a Type Mismatch upon execution. Again, thanks for your response. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ariel Dugan" wrote: Hi, In your 3rd example the variant array has the flexibility to allow a range to be assigned to it. I don't think you can do that with a explicitely "typed" array like in your second example. Alternatively you could use this modified version. Its better this way as you have to explicitely size both dimensions of your array, so it is more clear what you are actually doing. When assigning a range to an array, even if its only one row (or column) it is still a 2 dimensional array (or matrix for visualization purposes). Also, instead of passing the entire range in one call, you have to do it one element at a time. I did this with a small loop, which isn't really necessary in this case, but would be if you were populating the array with a large number of values. I used the UBound of the array to return the upper limit of the array. This is a common approach to identify the upper limit of the array, for purposes like this. Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. Dim i As Integer ReDim MyArray(1 To 3, 1 To 1) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i ' The follwg instr gives: "Run-time error '13': Type mismatch" 'MyArray = Range("A1:A3") MsgBox "MyArray(1, 1) = " & MyArray(1, 1) MsgBox "MyArray(2, 1) = " & MyArray(2, 1) MsgBox "MyArray(3, 1) = " & MyArray(3, 1) End Sub Thanks Ariel "MichaelDavid" wrote in message ... Greetings! This one has me totally baffled. In Sub ArrayStudies1, MyArray is dimensioned as an array of type Integer. MyArray is then ReDim'ed, Ranges A1 through A3 are set to integers, successive array locations are set to these ranges, and the first array location is successfully displayed. Sub ArrayStudies2 differs from Sub ArrayStudies1 only in that the array MyArray is set to a 3 cell range having integers. In this case, upon execution, I get the message " "Run-time error '13': Type mismatch". Sub ArrayStudies3 differs from Sub ArrayStudies2 only in that MyArray is declared as a dynamic array of type Variant rather than as type Integer. And this worked perfectly with no error messages. Why can't MyArray be declared as an array of type Integer in Sub ArrayStudies2? (At the top of the module containing these three subs is: Option Base 1) Sub ArrayStudies1() ' example patterned after Excel VBA Help on ReDim statement: ' This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1. Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray(1) = Range("A1") MyArray(2) = Range("A2") MyArray(3) = Range("A3") ' The following instruction works fine: MsgBox "MyArray(1) = " & MyArray(1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr gives: "Run-time error '13': Type mismatch" MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies3() Dim MyArray() ' Declare dynamic array of type Variant. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr works fine MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub May you have a most blessed day! Sincerely, Michael Fitzpatrick |
ReDim not working as expected. Array expert needed.
No. It's not unadvertised. This is well documented as the way that a variant
is populated by a worksheet range. Any dimensions you declare your variable in are wiped out, and the dimensions of the range are used. When I told you how to declare your variable as a variant, I next told you not to redim it. Then I showed a few examples of how array dimensions work in VBA. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Jon! I tried your notes and could not get the Procedure ArrayStudies14 to work with ReDim, so I commented out the ReDim statement, and ran the procedure to see what would happen: Sub ArrayStudies14() ' with Jon's suggestions from Excel VBA Group Dim MyArray As Variant ' Declare nonarray variant. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray = Range("A1:A3").Value ' ReDim MyArray(3) ' Allocate 3 elements. COMMENTED OUT. MsgBox "MyArray(3) = " & MyArray(3, 1) End Sub Would you believe that it works perfectly without the ReDim? Could an unadvertised Excel VBA feature be that sometimes ReDim is not needed? This is strange, and should be brought to the attention of Microsoft Programming. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: A few notes. The variable that accepts the values in a range should be declared as a non array variant. Dim MyArray As Variant Don't redim it prior to populating it, because VBA does that anyway, using the correct dimensions. When you redim the variable using: ReDim MyArray(3) that is the same as redimming it like this: ReDim MyArray(0 to 3) because VBA assumes a lower bound of 0 (0-base) unless told otherwise. When you then assign the values in A1:A3 MyArray = Range("A1:A3").Value (use the .Value property of the range, even though it's the default) the array comes out dimensioned as MyArray(1 to 3, 1 to 1) because the worksheet range is a 2D array, and it is treated as a 1-base array (lower bound is 1 not 0). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Ariel! Thanks for your speedy reply. The original reason for my post is that the code in question typifies a lot of the code in a hugh macro which takes hours to run. Consequently, I have performed timing studies on the various possible ways to code certain tasks. For example, the instruction of the third subroutine (ArrayStudies3()) which does: MyArray = Range("A1:A3") ' Initialize array. runs much quicker than the code which does the For Next Loop of example 2 as you modified it: For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i Therefore I am looking for a way to dimension MyArray as an Integer like so: Dim MyArray() As Integer rather than as the Variant, Dim MyArray() (which results in execution without error), for even greater speed. Hopefully some "super expert" in this discussion group will come up with a way of dimensioning MyArray() As Integer such that MyArray can be set to Range("A1:A3") or an equivalent without a Type Mismatch upon execution. Again, thanks for your response. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ariel Dugan" wrote: Hi, In your 3rd example the variant array has the flexibility to allow a range to be assigned to it. I don't think you can do that with a explicitely "typed" array like in your second example. Alternatively you could use this modified version. Its better this way as you have to explicitely size both dimensions of your array, so it is more clear what you are actually doing. When assigning a range to an array, even if its only one row (or column) it is still a 2 dimensional array (or matrix for visualization purposes). Also, instead of passing the entire range in one call, you have to do it one element at a time. I did this with a small loop, which isn't really necessary in this case, but would be if you were populating the array with a large number of values. I used the UBound of the array to return the upper limit of the array. This is a common approach to identify the upper limit of the array, for purposes like this. Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. Dim i As Integer ReDim MyArray(1 To 3, 1 To 1) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i ' The follwg instr gives: "Run-time error '13': Type mismatch" 'MyArray = Range("A1:A3") MsgBox "MyArray(1, 1) = " & MyArray(1, 1) MsgBox "MyArray(2, 1) = " & MyArray(2, 1) MsgBox "MyArray(3, 1) = " & MyArray(3, 1) End Sub Thanks Ariel "MichaelDavid" wrote in message ... Greetings! This one has me totally baffled. In Sub ArrayStudies1, MyArray is dimensioned as an array of type Integer. MyArray is then ReDim'ed, Ranges A1 through A3 are set to integers, successive array locations are set to these ranges, and the first array location is successfully displayed. Sub ArrayStudies2 differs from Sub ArrayStudies1 only in that the array MyArray is set to a 3 cell range having integers. In this case, upon execution, I get the message " "Run-time error '13': Type mismatch". Sub ArrayStudies3 differs from Sub ArrayStudies2 only in that MyArray is declared as a dynamic array of type Variant rather than as type Integer. And this worked perfectly with no error messages. Why can't MyArray be declared as an array of type Integer in Sub ArrayStudies2? (At the top of the module containing these three subs is: Option Base 1) Sub ArrayStudies1() ' example patterned after Excel VBA Help on ReDim statement: ' This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1. Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray(1) = Range("A1") MyArray(2) = Range("A2") MyArray(3) = Range("A3") ' The following instruction works fine: MsgBox "MyArray(1) = " & MyArray(1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr gives: "Run-time error '13': Type mismatch" MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies3() Dim MyArray() ' Declare dynamic array of type Variant. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr works fine MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub May you have a most blessed day! Sincerely, Michael Fitzpatrick |
ReDim not working as expected. Array expert needed.
Greetings! Thanks for your kind help. I did some further research and here is
what I found: I used VarType to determine the underlying types of each of the following variables: Dim MyArray1() ' Type 8204 Dim MyArray2() As Integer ' Type 8194 Dim MyArray3 ' Type Variant but no initial value assigned (vbEmpty) Dim MyArray4 As Integer ' Type Integer (vbInteger) Dim MyArray5 As Variant ' Type Variant but no initial value assigned (vbEmpty) Dim MyArray6() As Variant ' Type 8204 Do you (or anyone else in the Excel VBA Discussion group) know what types are indicated by types 8204 and 8194? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: No. It's not unadvertised. This is well documented as the way that a variant is populated by a worksheet range. Any dimensions you declare your variable in are wiped out, and the dimensions of the range are used. When I told you how to declare your variable as a variant, I next told you not to redim it. Then I showed a few examples of how array dimensions work in VBA. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Jon! I tried your notes and could not get the Procedure ArrayStudies14 to work with ReDim, so I commented out the ReDim statement, and ran the procedure to see what would happen: Sub ArrayStudies14() ' with Jon's suggestions from Excel VBA Group Dim MyArray As Variant ' Declare nonarray variant. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray = Range("A1:A3").Value ' ReDim MyArray(3) ' Allocate 3 elements. COMMENTED OUT. MsgBox "MyArray(3) = " & MyArray(3, 1) End Sub Would you believe that it works perfectly without the ReDim? Could an unadvertised Excel VBA feature be that sometimes ReDim is not needed? This is strange, and should be brought to the attention of Microsoft Programming. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: A few notes. The variable that accepts the values in a range should be declared as a non array variant. Dim MyArray As Variant Don't redim it prior to populating it, because VBA does that anyway, using the correct dimensions. When you redim the variable using: ReDim MyArray(3) that is the same as redimming it like this: ReDim MyArray(0 to 3) because VBA assumes a lower bound of 0 (0-base) unless told otherwise. When you then assign the values in A1:A3 MyArray = Range("A1:A3").Value (use the .Value property of the range, even though it's the default) the array comes out dimensioned as MyArray(1 to 3, 1 to 1) because the worksheet range is a 2D array, and it is treated as a 1-base array (lower bound is 1 not 0). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Ariel! Thanks for your speedy reply. The original reason for my post is that the code in question typifies a lot of the code in a hugh macro which takes hours to run. Consequently, I have performed timing studies on the various possible ways to code certain tasks. For example, the instruction of the third subroutine (ArrayStudies3()) which does: MyArray = Range("A1:A3") ' Initialize array. runs much quicker than the code which does the For Next Loop of example 2 as you modified it: For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i Therefore I am looking for a way to dimension MyArray as an Integer like so: Dim MyArray() As Integer rather than as the Variant, Dim MyArray() (which results in execution without error), for even greater speed. Hopefully some "super expert" in this discussion group will come up with a way of dimensioning MyArray() As Integer such that MyArray can be set to Range("A1:A3") or an equivalent without a Type Mismatch upon execution. Again, thanks for your response. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ariel Dugan" wrote: Hi, In your 3rd example the variant array has the flexibility to allow a range to be assigned to it. I don't think you can do that with a explicitely "typed" array like in your second example. Alternatively you could use this modified version. Its better this way as you have to explicitely size both dimensions of your array, so it is more clear what you are actually doing. When assigning a range to an array, even if its only one row (or column) it is still a 2 dimensional array (or matrix for visualization purposes). Also, instead of passing the entire range in one call, you have to do it one element at a time. I did this with a small loop, which isn't really necessary in this case, but would be if you were populating the array with a large number of values. I used the UBound of the array to return the upper limit of the array. This is a common approach to identify the upper limit of the array, for purposes like this. Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. Dim i As Integer ReDim MyArray(1 To 3, 1 To 1) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i ' The follwg instr gives: "Run-time error '13': Type mismatch" 'MyArray = Range("A1:A3") MsgBox "MyArray(1, 1) = " & MyArray(1, 1) MsgBox "MyArray(2, 1) = " & MyArray(2, 1) MsgBox "MyArray(3, 1) = " & MyArray(3, 1) End Sub Thanks Ariel "MichaelDavid" wrote in message ... Greetings! This one has me totally baffled. In Sub ArrayStudies1, MyArray is dimensioned as an array of type Integer. MyArray is then ReDim'ed, Ranges A1 through A3 are set to integers, successive array locations are set to these ranges, and the first array location is successfully displayed. Sub ArrayStudies2 differs from Sub ArrayStudies1 only in that the array MyArray is set to a 3 cell range having integers. In this case, upon execution, I get the message " "Run-time error '13': Type mismatch". Sub ArrayStudies3 differs from Sub ArrayStudies2 only in that MyArray is declared as a dynamic array of type Variant rather than as type Integer. And this worked perfectly with no error messages. Why can't MyArray be declared as an array of type Integer in Sub ArrayStudies2? (At the top of the module containing these three subs is: Option Base 1) Sub ArrayStudies1() ' example patterned after Excel VBA Help on ReDim statement: ' This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1. Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray(1) = Range("A1") MyArray(2) = Range("A2") MyArray(3) = Range("A3") ' The following instruction works fine: MsgBox "MyArray(1) = " & MyArray(1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr gives: "Run-time error '13': Type mismatch" MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies3() Dim MyArray() ' Declare dynamic array of type Variant. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr works fine MyArray = Range("A1:A3") ' Initialize array. MsgBox "MyArray(1, 1) = " & MyArray(1, 1) End Sub May you have a most blessed day! Sincerely, |
ReDim not working as expected. Array expert needed.
Hi Jon:
Next I tried to determine these types using TypeName. What I found: MsgBox TypeName(MyArray1()) ' Type Variant() MsgBox TypeName(MyArray2()) ' Type Integer MsgBox TypeName(MyArray6()) ' Type Variant() -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "MichaelDavid" wrote: Greetings! Thanks for your kind help. I did some further research and here is what I found: I used VarType to determine the underlying types of each of the following variables: Dim MyArray1() ' Type 8204 Dim MyArray2() As Integer ' Type 8194 Dim MyArray3 ' Type Variant but no initial value assigned (vbEmpty) Dim MyArray4 As Integer ' Type Integer (vbInteger) Dim MyArray5 As Variant ' Type Variant but no initial value assigned (vbEmpty) Dim MyArray6() As Variant ' Type 8204 Do you (or anyone else in the Excel VBA Discussion group) know what types are indicated by types 8204 and 8194? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: No. It's not unadvertised. This is well documented as the way that a variant is populated by a worksheet range. Any dimensions you declare your variable in are wiped out, and the dimensions of the range are used. When I told you how to declare your variable as a variant, I next told you not to redim it. Then I showed a few examples of how array dimensions work in VBA. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Jon! I tried your notes and could not get the Procedure ArrayStudies14 to work with ReDim, so I commented out the ReDim statement, and ran the procedure to see what would happen: Sub ArrayStudies14() ' with Jon's suggestions from Excel VBA Group Dim MyArray As Variant ' Declare nonarray variant. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray = Range("A1:A3").Value ' ReDim MyArray(3) ' Allocate 3 elements. COMMENTED OUT. MsgBox "MyArray(3) = " & MyArray(3, 1) End Sub Would you believe that it works perfectly without the ReDim? Could an unadvertised Excel VBA feature be that sometimes ReDim is not needed? This is strange, and should be brought to the attention of Microsoft Programming. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: A few notes. The variable that accepts the values in a range should be declared as a non array variant. Dim MyArray As Variant Don't redim it prior to populating it, because VBA does that anyway, using the correct dimensions. When you redim the variable using: ReDim MyArray(3) that is the same as redimming it like this: ReDim MyArray(0 to 3) because VBA assumes a lower bound of 0 (0-base) unless told otherwise. When you then assign the values in A1:A3 MyArray = Range("A1:A3").Value (use the .Value property of the range, even though it's the default) the array comes out dimensioned as MyArray(1 to 3, 1 to 1) because the worksheet range is a 2D array, and it is treated as a 1-base array (lower bound is 1 not 0). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Ariel! Thanks for your speedy reply. The original reason for my post is that the code in question typifies a lot of the code in a hugh macro which takes hours to run. Consequently, I have performed timing studies on the various possible ways to code certain tasks. For example, the instruction of the third subroutine (ArrayStudies3()) which does: MyArray = Range("A1:A3") ' Initialize array. runs much quicker than the code which does the For Next Loop of example 2 as you modified it: For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i Therefore I am looking for a way to dimension MyArray as an Integer like so: Dim MyArray() As Integer rather than as the Variant, Dim MyArray() (which results in execution without error), for even greater speed. Hopefully some "super expert" in this discussion group will come up with a way of dimensioning MyArray() As Integer such that MyArray can be set to Range("A1:A3") or an equivalent without a Type Mismatch upon execution. Again, thanks for your response. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ariel Dugan" wrote: Hi, In your 3rd example the variant array has the flexibility to allow a range to be assigned to it. I don't think you can do that with a explicitely "typed" array like in your second example. Alternatively you could use this modified version. Its better this way as you have to explicitely size both dimensions of your array, so it is more clear what you are actually doing. When assigning a range to an array, even if its only one row (or column) it is still a 2 dimensional array (or matrix for visualization purposes). Also, instead of passing the entire range in one call, you have to do it one element at a time. I did this with a small loop, which isn't really necessary in this case, but would be if you were populating the array with a large number of values. I used the UBound of the array to return the upper limit of the array. This is a common approach to identify the upper limit of the array, for purposes like this. Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. Dim i As Integer ReDim MyArray(1 To 3, 1 To 1) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i ' The follwg instr gives: "Run-time error '13': Type mismatch" 'MyArray = Range("A1:A3") MsgBox "MyArray(1, 1) = " & MyArray(1, 1) MsgBox "MyArray(2, 1) = " & MyArray(2, 1) MsgBox "MyArray(3, 1) = " & MyArray(3, 1) End Sub Thanks Ariel "MichaelDavid" wrote in message ... Greetings! This one has me totally baffled. In Sub ArrayStudies1, MyArray is dimensioned as an array of type Integer. MyArray is then ReDim'ed, Ranges A1 through A3 are set to integers, successive array locations are set to these ranges, and the first array location is successfully displayed. Sub ArrayStudies2 differs from Sub ArrayStudies1 only in that the array MyArray is set to a 3 cell range having integers. In this case, upon execution, I get the message " "Run-time error '13': Type mismatch". Sub ArrayStudies3 differs from Sub ArrayStudies2 only in that MyArray is declared as a dynamic array of type Variant rather than as type Integer. And this worked perfectly with no error messages. Why can't MyArray be declared as an array of type Integer in Sub ArrayStudies2? (At the top of the module containing these three subs is: Option Base 1) Sub ArrayStudies1() ' example patterned after Excel VBA Help on ReDim statement: ' This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1. Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray(1) = Range("A1") MyArray(2) = Range("A2") MyArray(3) = Range("A3") ' The following instruction works fine: MsgBox "MyArray(1) = " & MyArray(1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr gives: "Run-time error '13': Type mismatch" |
ReDim not working as expected. Array expert needed.
Greetings All!
Further research shows that: 1) Type 8204 is the Array of Variants data type 2) Type 8194 is the Array of Integers data type -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "MichaelDavid" wrote: Greetings! Thanks for your kind help. I did some further research and here is what I found: I used VarType to determine the underlying types of each of the following variables: Dim MyArray1() ' Type 8204 Dim MyArray2() As Integer ' Type 8194 Dim MyArray3 ' Type Variant but no initial value assigned (vbEmpty) Dim MyArray4 As Integer ' Type Integer (vbInteger) Dim MyArray5 As Variant ' Type Variant but no initial value assigned (vbEmpty) Dim MyArray6() As Variant ' Type 8204 Do you (or anyone else in the Excel VBA Discussion group) know what types are indicated by types 8204 and 8194? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: No. It's not unadvertised. This is well documented as the way that a variant is populated by a worksheet range. Any dimensions you declare your variable in are wiped out, and the dimensions of the range are used. When I told you how to declare your variable as a variant, I next told you not to redim it. Then I showed a few examples of how array dimensions work in VBA. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Jon! I tried your notes and could not get the Procedure ArrayStudies14 to work with ReDim, so I commented out the ReDim statement, and ran the procedure to see what would happen: Sub ArrayStudies14() ' with Jon's suggestions from Excel VBA Group Dim MyArray As Variant ' Declare nonarray variant. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray = Range("A1:A3").Value ' ReDim MyArray(3) ' Allocate 3 elements. COMMENTED OUT. MsgBox "MyArray(3) = " & MyArray(3, 1) End Sub Would you believe that it works perfectly without the ReDim? Could an unadvertised Excel VBA feature be that sometimes ReDim is not needed? This is strange, and should be brought to the attention of Microsoft Programming. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: A few notes. The variable that accepts the values in a range should be declared as a non array variant. Dim MyArray As Variant Don't redim it prior to populating it, because VBA does that anyway, using the correct dimensions. When you redim the variable using: ReDim MyArray(3) that is the same as redimming it like this: ReDim MyArray(0 to 3) because VBA assumes a lower bound of 0 (0-base) unless told otherwise. When you then assign the values in A1:A3 MyArray = Range("A1:A3").Value (use the .Value property of the range, even though it's the default) the array comes out dimensioned as MyArray(1 to 3, 1 to 1) because the worksheet range is a 2D array, and it is treated as a 1-base array (lower bound is 1 not 0). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Ariel! Thanks for your speedy reply. The original reason for my post is that the code in question typifies a lot of the code in a hugh macro which takes hours to run. Consequently, I have performed timing studies on the various possible ways to code certain tasks. For example, the instruction of the third subroutine (ArrayStudies3()) which does: MyArray = Range("A1:A3") ' Initialize array. runs much quicker than the code which does the For Next Loop of example 2 as you modified it: For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i Therefore I am looking for a way to dimension MyArray as an Integer like so: Dim MyArray() As Integer rather than as the Variant, Dim MyArray() (which results in execution without error), for even greater speed. Hopefully some "super expert" in this discussion group will come up with a way of dimensioning MyArray() As Integer such that MyArray can be set to Range("A1:A3") or an equivalent without a Type Mismatch upon execution. Again, thanks for your response. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ariel Dugan" wrote: Hi, In your 3rd example the variant array has the flexibility to allow a range to be assigned to it. I don't think you can do that with a explicitely "typed" array like in your second example. Alternatively you could use this modified version. Its better this way as you have to explicitely size both dimensions of your array, so it is more clear what you are actually doing. When assigning a range to an array, even if its only one row (or column) it is still a 2 dimensional array (or matrix for visualization purposes). Also, instead of passing the entire range in one call, you have to do it one element at a time. I did this with a small loop, which isn't really necessary in this case, but would be if you were populating the array with a large number of values. I used the UBound of the array to return the upper limit of the array. This is a common approach to identify the upper limit of the array, for purposes like this. Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. Dim i As Integer ReDim MyArray(1 To 3, 1 To 1) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i ' The follwg instr gives: "Run-time error '13': Type mismatch" 'MyArray = Range("A1:A3") MsgBox "MyArray(1, 1) = " & MyArray(1, 1) MsgBox "MyArray(2, 1) = " & MyArray(2, 1) MsgBox "MyArray(3, 1) = " & MyArray(3, 1) End Sub Thanks Ariel "MichaelDavid" wrote in message ... Greetings! This one has me totally baffled. In Sub ArrayStudies1, MyArray is dimensioned as an array of type Integer. MyArray is then ReDim'ed, Ranges A1 through A3 are set to integers, successive array locations are set to these ranges, and the first array location is successfully displayed. Sub ArrayStudies2 differs from Sub ArrayStudies1 only in that the array MyArray is set to a 3 cell range having integers. In this case, upon execution, I get the message " "Run-time error '13': Type mismatch". Sub ArrayStudies3 differs from Sub ArrayStudies2 only in that MyArray is declared as a dynamic array of type Variant rather than as type Integer. And this worked perfectly with no error messages. Why can't MyArray be declared as an array of type Integer in Sub ArrayStudies2? (At the top of the module containing these three subs is: Option Base 1) Sub ArrayStudies1() ' example patterned after Excel VBA Help on ReDim statement: ' This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1. Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray(1) = Range("A1") MyArray(2) = Range("A2") MyArray(3) = Range("A3") ' The following instruction works fine: MsgBox "MyArray(1) = " & MyArray(1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr gives: "Run-time error '13': Type mismatch" |
ReDim not working as expected. Array expert needed.
Why don't you just use the approaches which have been suggested, based on
the well documented behavior of variants and arrays in Excel VBA? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Greetings All! Further research shows that: 1) Type 8204 is the Array of Variants data type 2) Type 8194 is the Array of Integers data type -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "MichaelDavid" wrote: Greetings! Thanks for your kind help. I did some further research and here is what I found: I used VarType to determine the underlying types of each of the following variables: Dim MyArray1() ' Type 8204 Dim MyArray2() As Integer ' Type 8194 Dim MyArray3 ' Type Variant but no initial value assigned (vbEmpty) Dim MyArray4 As Integer ' Type Integer (vbInteger) Dim MyArray5 As Variant ' Type Variant but no initial value assigned (vbEmpty) Dim MyArray6() As Variant ' Type 8204 Do you (or anyone else in the Excel VBA Discussion group) know what types are indicated by types 8204 and 8194? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: No. It's not unadvertised. This is well documented as the way that a variant is populated by a worksheet range. Any dimensions you declare your variable in are wiped out, and the dimensions of the range are used. When I told you how to declare your variable as a variant, I next told you not to redim it. Then I showed a few examples of how array dimensions work in VBA. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Jon! I tried your notes and could not get the Procedure ArrayStudies14 to work with ReDim, so I commented out the ReDim statement, and ran the procedure to see what would happen: Sub ArrayStudies14() ' with Jon's suggestions from Excel VBA Group Dim MyArray As Variant ' Declare nonarray variant. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray = Range("A1:A3").Value ' ReDim MyArray(3) ' Allocate 3 elements. COMMENTED OUT. MsgBox "MyArray(3) = " & MyArray(3, 1) End Sub Would you believe that it works perfectly without the ReDim? Could an unadvertised Excel VBA feature be that sometimes ReDim is not needed? This is strange, and should be brought to the attention of Microsoft Programming. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: A few notes. The variable that accepts the values in a range should be declared as a non array variant. Dim MyArray As Variant Don't redim it prior to populating it, because VBA does that anyway, using the correct dimensions. When you redim the variable using: ReDim MyArray(3) that is the same as redimming it like this: ReDim MyArray(0 to 3) because VBA assumes a lower bound of 0 (0-base) unless told otherwise. When you then assign the values in A1:A3 MyArray = Range("A1:A3").Value (use the .Value property of the range, even though it's the default) the array comes out dimensioned as MyArray(1 to 3, 1 to 1) because the worksheet range is a 2D array, and it is treated as a 1-base array (lower bound is 1 not 0). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Ariel! Thanks for your speedy reply. The original reason for my post is that the code in question typifies a lot of the code in a hugh macro which takes hours to run. Consequently, I have performed timing studies on the various possible ways to code certain tasks. For example, the instruction of the third subroutine (ArrayStudies3()) which does: MyArray = Range("A1:A3") ' Initialize array. runs much quicker than the code which does the For Next Loop of example 2 as you modified it: For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i Therefore I am looking for a way to dimension MyArray as an Integer like so: Dim MyArray() As Integer rather than as the Variant, Dim MyArray() (which results in execution without error), for even greater speed. Hopefully some "super expert" in this discussion group will come up with a way of dimensioning MyArray() As Integer such that MyArray can be set to Range("A1:A3") or an equivalent without a Type Mismatch upon execution. Again, thanks for your response. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ariel Dugan" wrote: Hi, In your 3rd example the variant array has the flexibility to allow a range to be assigned to it. I don't think you can do that with a explicitely "typed" array like in your second example. Alternatively you could use this modified version. Its better this way as you have to explicitely size both dimensions of your array, so it is more clear what you are actually doing. When assigning a range to an array, even if its only one row (or column) it is still a 2 dimensional array (or matrix for visualization purposes). Also, instead of passing the entire range in one call, you have to do it one element at a time. I did this with a small loop, which isn't really necessary in this case, but would be if you were populating the array with a large number of values. I used the UBound of the array to return the upper limit of the array. This is a common approach to identify the upper limit of the array, for purposes like this. Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. Dim i As Integer ReDim MyArray(1 To 3, 1 To 1) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i ' The follwg instr gives: "Run-time error '13': Type mismatch" 'MyArray = Range("A1:A3") MsgBox "MyArray(1, 1) = " & MyArray(1, 1) MsgBox "MyArray(2, 1) = " & MyArray(2, 1) MsgBox "MyArray(3, 1) = " & MyArray(3, 1) End Sub Thanks Ariel "MichaelDavid" wrote in message ... Greetings! This one has me totally baffled. In Sub ArrayStudies1, MyArray is dimensioned as an array of type Integer. MyArray is then ReDim'ed, Ranges A1 through A3 are set to integers, successive array locations are set to these ranges, and the first array location is successfully displayed. Sub ArrayStudies2 differs from Sub ArrayStudies1 only in that the array MyArray is set to a 3 cell range having integers. In this case, upon execution, I get the message " "Run-time error '13': Type mismatch". Sub ArrayStudies3 differs from Sub ArrayStudies2 only in that MyArray is declared as a dynamic array of type Variant rather than as type Integer. And this worked perfectly with no error messages. Why can't MyArray be declared as an array of type Integer in Sub ArrayStudies2? (At the top of the module containing these three subs is: Option Base 1) Sub ArrayStudies1() ' example patterned after Excel VBA Help on ReDim statement: ' This example uses the ReDim statement to allocate and reallocate storage space for dynamic-array variables. It assumes the Option Base is 1. Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray(1) = Range("A1") MyArray(2) = Range("A2") MyArray(3) = Range("A3") ' The following instruction works fine: MsgBox "MyArray(1) = " & MyArray(1) End Sub ------------------------------------------------------------------------------------------------- Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. ReDim MyArray(3) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 ' The follwg instr gives: "Run-time error '13': Type mismatch" |
ReDim not working as expected. Array expert needed.
Hi Jon:
Thanks again for your kind help. I followed your suggestions, and have modified my code accordingly. My program is now working great, but I am always looking for ways to cut down on processing time. Therefore, I was just hoping against hope that there was someway of setting an array of integers to a range: Dim MyArray() As Integer Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray = Range("A1:A3").Value which, if it, or something similar, could be done, would be much faster than: For i = 1 To 3 MyArray(i, 1) = Range("A" & i).Value Next i I am always looking for ways of minimizing processing time. Hopefully, Microsoft will allow MyArray = Range("A1:A3").Value someday. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: Why don't you just use the approaches which have been suggested, based on the well documented behavior of variants and arrays in Excel VBA? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Greetings All! Further research shows that: 1) Type 8204 is the Array of Variants data type 2) Type 8194 is the Array of Integers data type -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "MichaelDavid" wrote: Greetings! Thanks for your kind help. I did some further research and here is what I found: I used VarType to determine the underlying types of each of the following variables: Dim MyArray1() ' Type 8204 Dim MyArray2() As Integer ' Type 8194 Dim MyArray3 ' Type Variant but no initial value assigned (vbEmpty) Dim MyArray4 As Integer ' Type Integer (vbInteger) Dim MyArray5 As Variant ' Type Variant but no initial value assigned (vbEmpty) Dim MyArray6() As Variant ' Type 8204 Do you (or anyone else in the Excel VBA Discussion group) know what types are indicated by types 8204 and 8194? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: No. It's not unadvertised. This is well documented as the way that a variant is populated by a worksheet range. Any dimensions you declare your variable in are wiped out, and the dimensions of the range are used. When I told you how to declare your variable as a variant, I next told you not to redim it. Then I showed a few examples of how array dimensions work in VBA. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Jon! I tried your notes and could not get the Procedure ArrayStudies14 to work with ReDim, so I commented out the ReDim statement, and ran the procedure to see what would happen: Sub ArrayStudies14() ' with Jon's suggestions from Excel VBA Group Dim MyArray As Variant ' Declare nonarray variant. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray = Range("A1:A3").Value ' ReDim MyArray(3) ' Allocate 3 elements. COMMENTED OUT. MsgBox "MyArray(3) = " & MyArray(3, 1) End Sub Would you believe that it works perfectly without the ReDim? Could an unadvertised Excel VBA feature be that sometimes ReDim is not needed? This is strange, and should be brought to the attention of Microsoft Programming. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: A few notes. The variable that accepts the values in a range should be declared as a non array variant. Dim MyArray As Variant Don't redim it prior to populating it, because VBA does that anyway, using the correct dimensions. When you redim the variable using: ReDim MyArray(3) that is the same as redimming it like this: ReDim MyArray(0 to 3) because VBA assumes a lower bound of 0 (0-base) unless told otherwise. When you then assign the values in A1:A3 MyArray = Range("A1:A3").Value (use the .Value property of the range, even though it's the default) the array comes out dimensioned as MyArray(1 to 3, 1 to 1) because the worksheet range is a 2D array, and it is treated as a 1-base array (lower bound is 1 not 0). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Ariel! Thanks for your speedy reply. The original reason for my post is that the code in question typifies a lot of the code in a hugh macro which takes hours to run. Consequently, I have performed timing studies on the various possible ways to code certain tasks. For example, the instruction of the third subroutine (ArrayStudies3()) which does: MyArray = Range("A1:A3") ' Initialize array. runs much quicker than the code which does the For Next Loop of example 2 as you modified it: For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i Therefore I am looking for a way to dimension MyArray as an Integer like so: Dim MyArray() As Integer rather than as the Variant, Dim MyArray() (which results in execution without error), for even greater speed. Hopefully some "super expert" in this discussion group will come up with a way of dimensioning MyArray() As Integer such that MyArray can be set to Range("A1:A3") or an equivalent without a Type Mismatch upon execution. Again, thanks for your response. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ariel Dugan" wrote: Hi, In your 3rd example the variant array has the flexibility to allow a range to be assigned to it. I don't think you can do that with a explicitely "typed" array like in your second example. Alternatively you could use this modified version. Its better this way as you have to explicitely size both dimensions of your array, so it is more clear what you are actually doing. When assigning a range to an array, even if its only one row (or column) it is still a 2 dimensional array (or matrix for visualization purposes). Also, instead of passing the entire range in one call, you have to do it one element at a time. I did this with a small loop, which isn't really necessary in this case, but would be if you were populating the array with a large number of values. I used the UBound of the array to return the upper limit of the array. This is a common approach to identify the upper limit of the array, for purposes like this. Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. Dim i As Integer ReDim MyArray(1 To 3, 1 To 1) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i ' The follwg instr gives: "Run-time error '13': Type mismatch" 'MyArray = Range("A1:A3") MsgBox "MyArray(1, 1) = " & MyArray(1, 1) MsgBox "MyArray(2, 1) = " & MyArray(2, 1) MsgBox "MyArray(3, 1) = " & MyArray(3, 1) End Sub Thanks Ariel "MichaelDavid" wrote in message ... |
ReDim not working as expected. Array expert needed.
People obsess about different variable types. They use singles instead of
doubles, and integers instead of longs, and the joke's on them, because VB converts singles to doubles and integers to longs, does the math, then converts back. Some people refuse to use variants, but for interacting with a worksheet, and for some other tasks, there isn't any better way. I've seen people use a variant to contain the values from a worksheet range, convert this to an array of integers or doubles or whatever, then proceed, but it seems to me that once you have the variant array in the first place, the damage is done and it's not likely to get perceptibly worse. I dunno, maybe I'm dumb, but it seems to me much more time is spent by the user scratching his head wondering what he wants to type into that textbox, than by my use of Variant instead of Integer(). Not that performance isn't important, it is. But I'll Pareto it, take my 80%, and move on. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Jon: Thanks again for your kind help. I followed your suggestions, and have modified my code accordingly. My program is now working great, but I am always looking for ways to cut down on processing time. Therefore, I was just hoping against hope that there was someway of setting an array of integers to a range: Dim MyArray() As Integer Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray = Range("A1:A3").Value which, if it, or something similar, could be done, would be much faster than: For i = 1 To 3 MyArray(i, 1) = Range("A" & i).Value Next i I am always looking for ways of minimizing processing time. Hopefully, Microsoft will allow MyArray = Range("A1:A3").Value someday. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: Why don't you just use the approaches which have been suggested, based on the well documented behavior of variants and arrays in Excel VBA? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Greetings All! Further research shows that: 1) Type 8204 is the Array of Variants data type 2) Type 8194 is the Array of Integers data type -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "MichaelDavid" wrote: Greetings! Thanks for your kind help. I did some further research and here is what I found: I used VarType to determine the underlying types of each of the following variables: Dim MyArray1() ' Type 8204 Dim MyArray2() As Integer ' Type 8194 Dim MyArray3 ' Type Variant but no initial value assigned (vbEmpty) Dim MyArray4 As Integer ' Type Integer (vbInteger) Dim MyArray5 As Variant ' Type Variant but no initial value assigned (vbEmpty) Dim MyArray6() As Variant ' Type 8204 Do you (or anyone else in the Excel VBA Discussion group) know what types are indicated by types 8204 and 8194? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: No. It's not unadvertised. This is well documented as the way that a variant is populated by a worksheet range. Any dimensions you declare your variable in are wiped out, and the dimensions of the range are used. When I told you how to declare your variable as a variant, I next told you not to redim it. Then I showed a few examples of how array dimensions work in VBA. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Jon! I tried your notes and could not get the Procedure ArrayStudies14 to work with ReDim, so I commented out the ReDim statement, and ran the procedure to see what would happen: Sub ArrayStudies14() ' with Jon's suggestions from Excel VBA Group Dim MyArray As Variant ' Declare nonarray variant. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 MyArray = Range("A1:A3").Value ' ReDim MyArray(3) ' Allocate 3 elements. COMMENTED OUT. MsgBox "MyArray(3) = " & MyArray(3, 1) End Sub Would you believe that it works perfectly without the ReDim? Could an unadvertised Excel VBA feature be that sometimes ReDim is not needed? This is strange, and should be brought to the attention of Microsoft Programming. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Jon Peltier" wrote: A few notes. The variable that accepts the values in a range should be declared as a non array variant. Dim MyArray As Variant Don't redim it prior to populating it, because VBA does that anyway, using the correct dimensions. When you redim the variable using: ReDim MyArray(3) that is the same as redimming it like this: ReDim MyArray(0 to 3) because VBA assumes a lower bound of 0 (0-base) unless told otherwise. When you then assign the values in A1:A3 MyArray = Range("A1:A3").Value (use the .Value property of the range, even though it's the default) the array comes out dimensioned as MyArray(1 to 3, 1 to 1) because the worksheet range is a 2D array, and it is treated as a 1-base array (lower bound is 1 not 0). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "MichaelDavid" wrote in message ... Hi Ariel! Thanks for your speedy reply. The original reason for my post is that the code in question typifies a lot of the code in a hugh macro which takes hours to run. Consequently, I have performed timing studies on the various possible ways to code certain tasks. For example, the instruction of the third subroutine (ArrayStudies3()) which does: MyArray = Range("A1:A3") ' Initialize array. runs much quicker than the code which does the For Next Loop of example 2 as you modified it: For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i Therefore I am looking for a way to dimension MyArray as an Integer like so: Dim MyArray() As Integer rather than as the Variant, Dim MyArray() (which results in execution without error), for even greater speed. Hopefully some "super expert" in this discussion group will come up with a way of dimensioning MyArray() As Integer such that MyArray can be set to Range("A1:A3") or an equivalent without a Type Mismatch upon execution. Again, thanks for your response. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Ariel Dugan" wrote: Hi, In your 3rd example the variant array has the flexibility to allow a range to be assigned to it. I don't think you can do that with a explicitely "typed" array like in your second example. Alternatively you could use this modified version. Its better this way as you have to explicitely size both dimensions of your array, so it is more clear what you are actually doing. When assigning a range to an array, even if its only one row (or column) it is still a 2 dimensional array (or matrix for visualization purposes). Also, instead of passing the entire range in one call, you have to do it one element at a time. I did this with a small loop, which isn't really necessary in this case, but would be if you were populating the array with a large number of values. I used the UBound of the array to return the upper limit of the array. This is a common approach to identify the upper limit of the array, for purposes like this. Sub ArrayStudies2() Dim MyArray() As Integer ' Declare dynamic array of type Integer. Dim i As Integer ReDim MyArray(1 To 3, 1 To 1) ' Allocate 3 elements. Range("A1") = 1 Range("A2") = 2 Range("A3") = 3 For i = 1 To UBound(MyArray) MyArray(i, 1) = Range("A" & i).Value ' Initialize array Next i ' The follwg instr gives: "Run-time error '13': Type mismatch" 'MyArray = Range("A1:A3") MsgBox "MyArray(1, 1) = " & MyArray(1, 1) MsgBox "MyArray(2, 1) = " & MyArray(2, 1) MsgBox "MyArray(3, 1) = " & MyArray(3, 1) End Sub Thanks Ariel "MichaelDavid" wrote in message ... |
| All times are GMT +1. The time now is 02:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com