Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with arrays (transferring values between two arrays)
XL2003 on WinXP. I have the weirdest thing going on, and can't figure it
out. I get an out of range error [9] when I get to the second sub. In Module 1, I have: Option Base 1 Public Expedite_LastRow As Integer Public ExpediteArrayShort As Variant '(1 To x rows, 1 column) Sub FirstSub 'identify how many rows are currently used Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row 'prepare to make a larger array to allow addition of new records from user form ExpediteArraySize = Expedite_LastRow + 100 'grab a range to use in the array ExpediteArrayShort = Sheet4.Range("B1:B" & CStr(ExpediteArraySize)).Value 'resize short array to 1D so Application.Match will work ReDim Preserve ExpediteArrayShort(LBound(ExpediteArrayShort, 1) To UBound(ExpediteArrayShort), 0 To 0) End sub 'In module 2, I have: Sub SecondSub 'Expedite_LastRow confirmed value as = 14 'sArray(1, i) confirmed value as = "P67G3" 'lBound(ExpediteArrayShort) confirmed value as =1 'uBound(ExpediteArrayShort) confirmed value as =114 ExpediteArrayShort(Expedite_LastRow) = sArray(1, i) '<< code dies here 'e.g. ExpediteArrayShort(14) = "P67G3" End Sub But I'm getting thisout of range [error 9]. Normally I'd think that my variable (Expediate_LastRow) or (i) were outside the range for those arrays, but sArray is returning a value, and I've confirmed that the Lbound/Ubound of the destination array is 1 and 114. I'm not using the array anywhere else in any subs, so there isn't anything else that should be affecting it. I created a simple comparison set (all within one sub/module, so not quite apples to apples) that works fine: Sub test_set() Dim Tarray(1 To 10) Dim DArray(1 To 10) Tarray(3) = "This is a test" DArray(3) = Tarray(3) MsgBox DArray(3) '<< returns "This is a test" in a messagebox End Sub Any ideas what might be wrong with my real project? Thank you very much, Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with arrays (transferring values between two arrays)
Sub FirstSub()
Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row ExpediteArraySize = Expedite_LastRow + 100 ExpediteArrayShort = Sheet4.Range("B1:B" & CStr(ExpediteArraySize)).Value End Sub Sub SecondSub() ' Test Application.Match ExpediteArrayShort(Expedite_LastRow, 1) = "P67G3" Debug.Print Application.Match("P67G3", ExpediteArrayShort, 0) ' Uncomment this ' ExpediteArrayShort(Expedite_LastRow, 1) = sArray(1, i) End Sub -- Tim Zych SF, CA "Keith R" wrote in message ... XL2003 on WinXP. I have the weirdest thing going on, and can't figure it out. I get an out of range error [9] when I get to the second sub. In Module 1, I have: Option Base 1 Public Expedite_LastRow As Integer Public ExpediteArrayShort As Variant '(1 To x rows, 1 column) Sub FirstSub 'identify how many rows are currently used Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row 'prepare to make a larger array to allow addition of new records from user form ExpediteArraySize = Expedite_LastRow + 100 'grab a range to use in the array ExpediteArrayShort = Sheet4.Range("B1:B" & CStr(ExpediteArraySize)).Value 'resize short array to 1D so Application.Match will work ReDim Preserve ExpediteArrayShort(LBound(ExpediteArrayShort, 1) To UBound(ExpediteArrayShort), 0 To 0) End sub 'In module 2, I have: Sub SecondSub 'Expedite_LastRow confirmed value as = 14 'sArray(1, i) confirmed value as = "P67G3" 'lBound(ExpediteArrayShort) confirmed value as =1 'uBound(ExpediteArrayShort) confirmed value as =114 ExpediteArrayShort(Expedite_LastRow) = sArray(1, i) '<< code dies here 'e.g. ExpediteArrayShort(14) = "P67G3" End Sub But I'm getting thisout of range [error 9]. Normally I'd think that my variable (Expediate_LastRow) or (i) were outside the range for those arrays, but sArray is returning a value, and I've confirmed that the Lbound/Ubound of the destination array is 1 and 114. I'm not using the array anywhere else in any subs, so there isn't anything else that should be affecting it. I created a simple comparison set (all within one sub/module, so not quite apples to apples) that works fine: Sub test_set() Dim Tarray(1 To 10) Dim DArray(1 To 10) Tarray(3) = "This is a test" DArray(3) = Tarray(3) MsgBox DArray(3) '<< returns "This is a test" in a messagebox End Sub Any ideas what might be wrong with my real project? Thank you very much, Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with arrays (transferring values between two arrays)
Tim- my apologies, I may need more context to understand your suggestion. It
looks like you are sticking with a 2-D array instead of my switch to a 1D (although yours is a lot less work, since you avoid the redim altogether). However, you assign the "new" array value directly, whereas my brain is jello because I can't figure out why I can't assign my new value to my existing array from the other existing array. All of my variables seem to be well within parameters (array sizes, etc.) but it still errors out on me in the second sub. I included a bunch of comments in my second sub just to show what I've looked at, but there is only one active line, which is the one where it dies. Many thanks, Keith "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote in message ... Sub FirstSub() Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row ExpediteArraySize = Expedite_LastRow + 100 ExpediteArrayShort = Sheet4.Range("B1:B" & CStr(ExpediteArraySize)).Value End Sub Sub SecondSub() ' Test Application.Match ExpediteArrayShort(Expedite_LastRow, 1) = "P67G3" Debug.Print Application.Match("P67G3", ExpediteArrayShort, 0) ' Uncomment this ' ExpediteArrayShort(Expedite_LastRow, 1) = sArray(1, i) End Sub -- Tim Zych SF, CA "Keith R" wrote in message ... XL2003 on WinXP. I have the weirdest thing going on, and can't figure it out. I get an out of range error [9] when I get to the second sub. In Module 1, I have: Option Base 1 Public Expedite_LastRow As Integer Public ExpediteArrayShort As Variant '(1 To x rows, 1 column) Sub FirstSub 'identify how many rows are currently used Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row 'prepare to make a larger array to allow addition of new records from user form ExpediteArraySize = Expedite_LastRow + 100 'grab a range to use in the array ExpediteArrayShort = Sheet4.Range("B1:B" & CStr(ExpediteArraySize)).Value 'resize short array to 1D so Application.Match will work ReDim Preserve ExpediteArrayShort(LBound(ExpediteArrayShort, 1) To UBound(ExpediteArrayShort), 0 To 0) End sub 'In module 2, I have: Sub SecondSub 'Expedite_LastRow confirmed value as = 14 'sArray(1, i) confirmed value as = "P67G3" 'lBound(ExpediteArrayShort) confirmed value as =1 'uBound(ExpediteArrayShort) confirmed value as =114 ExpediteArrayShort(Expedite_LastRow) = sArray(1, i) '<< code dies here 'e.g. ExpediteArrayShort(14) = "P67G3" End Sub But I'm getting thisout of range [error 9]. Normally I'd think that my variable (Expediate_LastRow) or (i) were outside the range for those arrays, but sArray is returning a value, and I've confirmed that the Lbound/Ubound of the destination array is 1 and 114. I'm not using the array anywhere else in any subs, so there isn't anything else that should be affecting it. I created a simple comparison set (all within one sub/module, so not quite apples to apples) that works fine: Sub test_set() Dim Tarray(1 To 10) Dim DArray(1 To 10) Tarray(3) = "This is a test" DArray(3) = Tarray(3) MsgBox DArray(3) '<< returns "This is a test" in a messagebox End Sub Any ideas what might be wrong with my real project? Thank you very much, Keith |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with arrays (transferring values between two arrays)
When it errors, what does these evaluate to? In the immediate window:
? i ? sArray(1, i) ? Expedite_LastRow ? ExpediteArrayShort(Expedite_LastRow, 1) Do any of them return errors? "Keith R" wrote in message ... Tim- my apologies, I may need more context to understand your suggestion. It looks like you are sticking with a 2-D array instead of my switch to a 1D (although yours is a lot less work, since you avoid the redim altogether). However, you assign the "new" array value directly, whereas my brain is jello because I can't figure out why I can't assign my new value to my existing array from the other existing array. All of my variables seem to be well within parameters (array sizes, etc.) but it still errors out on me in the second sub. I included a bunch of comments in my second sub just to show what I've looked at, but there is only one active line, which is the one where it dies. Many thanks, Keith "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote in message ... Sub FirstSub() Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row ExpediteArraySize = Expedite_LastRow + 100 ExpediteArrayShort = Sheet4.Range("B1:B" & CStr(ExpediteArraySize)).Value End Sub Sub SecondSub() ' Test Application.Match ExpediteArrayShort(Expedite_LastRow, 1) = "P67G3" Debug.Print Application.Match("P67G3", ExpediteArrayShort, 0) ' Uncomment this ' ExpediteArrayShort(Expedite_LastRow, 1) = sArray(1, i) End Sub -- Tim Zych SF, CA "Keith R" wrote in message ... XL2003 on WinXP. I have the weirdest thing going on, and can't figure it out. I get an out of range error [9] when I get to the second sub. In Module 1, I have: Option Base 1 Public Expedite_LastRow As Integer Public ExpediteArrayShort As Variant '(1 To x rows, 1 column) Sub FirstSub 'identify how many rows are currently used Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row 'prepare to make a larger array to allow addition of new records from user form ExpediteArraySize = Expedite_LastRow + 100 'grab a range to use in the array ExpediteArrayShort = Sheet4.Range("B1:B" & CStr(ExpediteArraySize)).Value 'resize short array to 1D so Application.Match will work ReDim Preserve ExpediteArrayShort(LBound(ExpediteArrayShort, 1) To UBound(ExpediteArrayShort), 0 To 0) End sub 'In module 2, I have: Sub SecondSub 'Expedite_LastRow confirmed value as = 14 'sArray(1, i) confirmed value as = "P67G3" 'lBound(ExpediteArrayShort) confirmed value as =1 'uBound(ExpediteArrayShort) confirmed value as =114 ExpediteArrayShort(Expedite_LastRow) = sArray(1, i) '<< code dies here 'e.g. ExpediteArrayShort(14) = "P67G3" End Sub But I'm getting thisout of range [error 9]. Normally I'd think that my variable (Expediate_LastRow) or (i) were outside the range for those arrays, but sArray is returning a value, and I've confirmed that the Lbound/Ubound of the destination array is 1 and 114. I'm not using the array anywhere else in any subs, so there isn't anything else that should be affecting it. I created a simple comparison set (all within one sub/module, so not quite apples to apples) that works fine: Sub test_set() Dim Tarray(1 To 10) Dim DArray(1 To 10) Tarray(3) = "This is a test" DArray(3) = Tarray(3) MsgBox DArray(3) '<< returns "This is a test" in a messagebox End Sub Any ideas what might be wrong with my real project? Thank you very much, Keith |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with arrays (transferring values between two arrays)
Keith R wrote:
XL2003 on WinXP. I have the weirdest thing going on, and can't figure it out. I get an out of range error [9] when I get to the second sub. . . . 'resize short array to 1D so Application.Match will work ReDim Preserve ExpediteArrayShort(LBound(ExpediteArrayShort, 1) To UBound(ExpediteArrayShort), 0 To 0) ExpediteArrayShort is a single-column 2-D array; its elements must be accessed with two index numbers, one for the "row" and one for the "column." When you try to access with just one index number you get the "Subscript out of range" error message. Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with Arrays | Excel Programming | |||
Working with ranges in arrays... or an introduction to arrays | Excel Programming | |||
Arrays - declaration, adding values to arrays and calculation | Excel Programming | |||
Trouble with Arrays / Collections | Excel Programming | |||
Transferring ranges to/from arrays | Excel Programming |