Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
Is there an array pointer available in VBA? I use it in other languages to
easily and quickly sort and search arrays. My problem is I have two very large spreadsheets and want to get data from one to the other based on a link and the arraypointer would make this easier in my opinion. A simple vlookup formula in the spreadsheet is very slow for a large qty of numbers so I don't really want to use it. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
Mike,
VBA does not use pointers. A better explanation of what you are doing may lead to some suggestions on ways to improve speed.... HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Is there an array pointer available in VBA? I use it in other languages to easily and quickly sort and search arrays. My problem is I have two very large spreadsheets and want to get data from one to the other based on a link and the arraypointer would make this easier in my opinion. A simple vlookup formula in the spreadsheet is very slow for a large qty of numbers so I don't really want to use it. Any ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
Here is what I do. I read into Dataarray() all my entries in a spreadsheet
(about 5000). The array is like this: Dataarray(REC#,1)=Account # DataArray(Rec#,2)=Account description if there is one (if it is a valid account). To fill in the second element I do this: Windows("Chart of Accounts.xls").Activate Sheets("Entire Chart").Select Let Counterx = 0 For YY = 1 To X Let XX = 0 Set myRange = Range("A1:A65000") flag = 0 For Each c In myRange Let XX = XX + 1 If c.Value = DataArray(YY, 1) Then flag = 1 If flag = 1 Then Exit For End If Next If flag = 1 Then DataArray(YY, 2) = Cells(XX, 2) Else Let Counterx = Counterx + 1 BadAccts(Counterx, 1) = DataArray(YY, 1) 'MsgBox ("Account " & DataArray(YY, 1) & " is not in the chart of accounts!") End If Next This code takes about 45 minutes to go through 5000 records. Any suggestions would be welcomed. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
Mike,
Not sure where your Account#/Account desc table is, but assuming that it is in columns A and B For i = LBound(DataArray) To UBound(DataArray) DataArray(i, 2) = Application.WorksheetFunction.VLookup(DataArray(i, 1), Range("A:B"), 2, False) Next i But why are you putting this into an array? You could just use the VLOOKUP whenever you want the value. HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Here is what I do. I read into Dataarray() all my entries in a spreadsheet (about 5000). The array is like this: Dataarray(REC#,1)=Account # DataArray(Rec#,2)=Account description if there is one (if it is a valid account). To fill in the second element I do this: Windows("Chart of Accounts.xls").Activate Sheets("Entire Chart").Select Let Counterx = 0 For YY = 1 To X Let XX = 0 Set myRange = Range("A1:A65000") flag = 0 For Each c In myRange Let XX = XX + 1 If c.Value = DataArray(YY, 1) Then flag = 1 If flag = 1 Then Exit For End If Next If flag = 1 Then DataArray(YY, 2) = Cells(XX, 2) Else Let Counterx = Counterx + 1 BadAccts(Counterx, 1) = DataArray(YY, 1) 'MsgBox ("Account " & DataArray(YY, 1) & " is not in the chart of accounts!") End If Next This code takes about 45 minutes to go through 5000 records. Any suggestions would be welcomed. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
I don't have to put it into an array. I just want the description for the
account in my journal entry file so that I can verify it is a valid account before I upload it. I will see how this goes. Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
Take a look at this link:
http://www.ozgrid.com/News/LookUpMatchIndexVBAFind.htm One of the FindBillyBrown() routines look close to what you want. On Fri, 7 Sep 2007 09:30:01 -0700, Mike H. wrote: Is there an array pointer available in VBA? I use it in other languages to easily and quickly sort and search arrays. My problem is I have two very large spreadsheets and want to get data from one to the other based on a link and the arraypointer would make this easier in my opinion. A simple vlookup formula in the spreadsheet is very slow for a large qty of numbers so I don't really want to use it. Any ideas? Richard -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
Bernie, This works tremendously! Instead of 45 minutes, it takes less than
45 seconds. Just one question, the LBound and UBound, does that stand for lower and upper boundary or something like that? "Bernie Deitrick" wrote: Mike, Not sure where your Account#/Account desc table is, but assuming that it is in columns A and B For i = LBound(DataArray) To UBound(DataArray) DataArray(i, 2) = Application.WorksheetFunction.VLookup(DataArray(i, 1), Range("A:B"), 2, False) Next i But why are you putting this into an array? You could just use the VLOOKUP whenever you want the value. HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Here is what I do. I read into Dataarray() all my entries in a spreadsheet (about 5000). The array is like this: Dataarray(REC#,1)=Account # DataArray(Rec#,2)=Account description if there is one (if it is a valid account). To fill in the second element I do this: Windows("Chart of Accounts.xls").Activate Sheets("Entire Chart").Select Let Counterx = 0 For YY = 1 To X Let XX = 0 Set myRange = Range("A1:A65000") flag = 0 For Each c In myRange Let XX = XX + 1 If c.Value = DataArray(YY, 1) Then flag = 1 If flag = 1 Then Exit For End If Next If flag = 1 Then DataArray(YY, 2) = Cells(XX, 2) Else Let Counterx = Counterx + 1 BadAccts(Counterx, 1) = DataArray(YY, 1) 'MsgBox ("Account " & DataArray(YY, 1) & " is not in the chart of accounts!") End If Next This code takes about 45 minutes to go through 5000 records. Any suggestions would be welcomed. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
Mike,
LBound and UBound are used to read the lower and upper bounds of arrays whose size you don't know. If you had coded Dim DataArray(1 to 5000) As Double then it would be okay to use For i = 1 To 5000 But a lot of operations return arrays of unknown size, so using LBound and UBound to read the size makes sense. There is one other complication - Option Base, which can be used to set the Lower bound of arrays automatically... HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Bernie, This works tremendously! Instead of 45 minutes, it takes less than 45 seconds. Just one question, the LBound and UBound, does that stand for lower and upper boundary or something like that? "Bernie Deitrick" wrote: Mike, Not sure where your Account#/Account desc table is, but assuming that it is in columns A and B For i = LBound(DataArray) To UBound(DataArray) DataArray(i, 2) = Application.WorksheetFunction.VLookup(DataArray(i, 1), Range("A:B"), 2, False) Next i But why are you putting this into an array? You could just use the VLOOKUP whenever you want the value. HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Here is what I do. I read into Dataarray() all my entries in a spreadsheet (about 5000). The array is like this: Dataarray(REC#,1)=Account # DataArray(Rec#,2)=Account description if there is one (if it is a valid account). To fill in the second element I do this: Windows("Chart of Accounts.xls").Activate Sheets("Entire Chart").Select Let Counterx = 0 For YY = 1 To X Let XX = 0 Set myRange = Range("A1:A65000") flag = 0 For Each c In myRange Let XX = XX + 1 If c.Value = DataArray(YY, 1) Then flag = 1 If flag = 1 Then Exit For End If Next If flag = 1 Then DataArray(YY, 2) = Cells(XX, 2) Else Let Counterx = Counterx + 1 BadAccts(Counterx, 1) = DataArray(YY, 1) 'MsgBox ("Account " & DataArray(YY, 1) & " is not in the chart of accounts!") End If Next This code takes about 45 minutes to go through 5000 records. Any suggestions would be welcomed. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
Why would the lower bound not always be one? Would not an array be
populated from 1 to x? "Bernie Deitrick" wrote: Mike, LBound and UBound are used to read the lower and upper bounds of arrays whose size you don't know. If you had coded Dim DataArray(1 to 5000) As Double then it would be okay to use For i = 1 To 5000 But a lot of operations return arrays of unknown size, so using LBound and UBound to read the size makes sense. There is one other complication - Option Base, which can be used to set the Lower bound of arrays automatically... HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Bernie, This works tremendously! Instead of 45 minutes, it takes less than 45 seconds. Just one question, the LBound and UBound, does that stand for lower and upper boundary or something like that? "Bernie Deitrick" wrote: Mike, Not sure where your Account#/Account desc table is, but assuming that it is in columns A and B For i = LBound(DataArray) To UBound(DataArray) DataArray(i, 2) = Application.WorksheetFunction.VLookup(DataArray(i, 1), Range("A:B"), 2, False) Next i But why are you putting this into an array? You could just use the VLOOKUP whenever you want the value. HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Here is what I do. I read into Dataarray() all my entries in a spreadsheet (about 5000). The array is like this: Dataarray(REC#,1)=Account # DataArray(Rec#,2)=Account description if there is one (if it is a valid account). To fill in the second element I do this: Windows("Chart of Accounts.xls").Activate Sheets("Entire Chart").Select Let Counterx = 0 For YY = 1 To X Let XX = 0 Set myRange = Range("A1:A65000") flag = 0 For Each c In myRange Let XX = XX + 1 If c.Value = DataArray(YY, 1) Then flag = 1 If flag = 1 Then Exit For End If Next If flag = 1 Then DataArray(YY, 2) = Cells(XX, 2) Else Let Counterx = Counterx + 1 BadAccts(Counterx, 1) = DataArray(YY, 1) 'MsgBox ("Account " & DataArray(YY, 1) & " is not in the chart of accounts!") End If Next This code takes about 45 minutes to go through 5000 records. Any suggestions would be welcomed. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
try this:
Sub test() Dim arr As Variant arr = Array("A", "B", "C") MsgBox arr(0) End Sub -- Gary "Mike H." wrote in message ... Why would the lower bound not always be one? Would not an array be populated from 1 to x? "Bernie Deitrick" wrote: Mike, LBound and UBound are used to read the lower and upper bounds of arrays whose size you don't know. If you had coded Dim DataArray(1 to 5000) As Double then it would be okay to use For i = 1 To 5000 But a lot of operations return arrays of unknown size, so using LBound and UBound to read the size makes sense. There is one other complication - Option Base, which can be used to set the Lower bound of arrays automatically... HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Bernie, This works tremendously! Instead of 45 minutes, it takes less than 45 seconds. Just one question, the LBound and UBound, does that stand for lower and upper boundary or something like that? "Bernie Deitrick" wrote: Mike, Not sure where your Account#/Account desc table is, but assuming that it is in columns A and B For i = LBound(DataArray) To UBound(DataArray) DataArray(i, 2) = Application.WorksheetFunction.VLookup(DataArray(i, 1), Range("A:B"), 2, False) Next i But why are you putting this into an array? You could just use the VLOOKUP whenever you want the value. HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Here is what I do. I read into Dataarray() all my entries in a spreadsheet (about 5000). The array is like this: Dataarray(REC#,1)=Account # DataArray(Rec#,2)=Account description if there is one (if it is a valid account). To fill in the second element I do this: Windows("Chart of Accounts.xls").Activate Sheets("Entire Chart").Select Let Counterx = 0 For YY = 1 To X Let XX = 0 Set myRange = Range("A1:A65000") flag = 0 For Each c In myRange Let XX = XX + 1 If c.Value = DataArray(YY, 1) Then flag = 1 If flag = 1 Then Exit For End If Next If flag = 1 Then DataArray(YY, 2) = Cells(XX, 2) Else Let Counterx = Counterx + 1 BadAccts(Counterx, 1) = DataArray(YY, 1) 'MsgBox ("Account " & DataArray(YY, 1) & " is not in the chart of accounts!") End If Next This code takes about 45 minutes to go through 5000 records. Any suggestions would be welcomed. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
Would not an array be populated from 1 to x?
No. Many arrays are 0 based by default, which is from indexing in binary (0 to 7 can be done using the same number of bits, 1 to 8 requires one more bit than that): Sub CompareArrays() Dim FileArray As Variant Dim myArray As Variant myArray = Array(4, 5, 6) FileArray = Application.GetOpenFilename(MultiSelect:=True) If IsArray(FileArray) Then MsgBox "The lower bound of the file array is " & LBound(FileArray) End If MsgBox "The lower bound of the constant array is " & LBound(myArray) End Sub But then try the same macro with this at the top of your codemodule Option Base 1 and then again with Option Base 0 And, IF you use Dim myArray(1 to 3) As Variant Then you have to use this myArray(1) = 4 myArray(2) = 5 myArray(3) = 6 instead of just estting the variant to an array. HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Why would the lower bound not always be one? Would not an array be populated from 1 to x? "Bernie Deitrick" wrote: Mike, LBound and UBound are used to read the lower and upper bounds of arrays whose size you don't know. If you had coded Dim DataArray(1 to 5000) As Double then it would be okay to use For i = 1 To 5000 But a lot of operations return arrays of unknown size, so using LBound and UBound to read the size makes sense. There is one other complication - Option Base, which can be used to set the Lower bound of arrays automatically... HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Bernie, This works tremendously! Instead of 45 minutes, it takes less than 45 seconds. Just one question, the LBound and UBound, does that stand for lower and upper boundary or something like that? "Bernie Deitrick" wrote: Mike, Not sure where your Account#/Account desc table is, but assuming that it is in columns A and B For i = LBound(DataArray) To UBound(DataArray) DataArray(i, 2) = Application.WorksheetFunction.VLookup(DataArray(i, 1), Range("A:B"), 2, False) Next i But why are you putting this into an array? You could just use the VLOOKUP whenever you want the value. HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Here is what I do. I read into Dataarray() all my entries in a spreadsheet (about 5000). The array is like this: Dataarray(REC#,1)=Account # DataArray(Rec#,2)=Account description if there is one (if it is a valid account). To fill in the second element I do this: Windows("Chart of Accounts.xls").Activate Sheets("Entire Chart").Select Let Counterx = 0 For YY = 1 To X Let XX = 0 Set myRange = Range("A1:A65000") flag = 0 For Each c In myRange Let XX = XX + 1 If c.Value = DataArray(YY, 1) Then flag = 1 If flag = 1 Then Exit For End If Next If flag = 1 Then DataArray(YY, 2) = Cells(XX, 2) Else Let Counterx = Counterx + 1 BadAccts(Counterx, 1) = DataArray(YY, 1) 'MsgBox ("Account " & DataArray(YY, 1) & " is not in the chart of accounts!") End If Next This code takes about 45 minutes to go through 5000 records. Any suggestions would be welcomed. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
On the msgbox() line, I get runtime error 9, subscript out of range.
"Gary Keramidas" wrote: try this: Sub test() Dim arr As Variant arr = Array("A", "B", "C") MsgBox arr(0) End Sub -- Gary "Mike H." wrote in message ... Why would the lower bound not always be one? Would not an array be populated from 1 to x? "Bernie Deitrick" wrote: Mike, LBound and UBound are used to read the lower and upper bounds of arrays whose size you don't know. If you had coded Dim DataArray(1 to 5000) As Double then it would be okay to use For i = 1 To 5000 But a lot of operations return arrays of unknown size, so using LBound and UBound to read the size makes sense. There is one other complication - Option Base, which can be used to set the Lower bound of arrays automatically... HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Bernie, This works tremendously! Instead of 45 minutes, it takes less than 45 seconds. Just one question, the LBound and UBound, does that stand for lower and upper boundary or something like that? "Bernie Deitrick" wrote: Mike, Not sure where your Account#/Account desc table is, but assuming that it is in columns A and B For i = LBound(DataArray) To UBound(DataArray) DataArray(i, 2) = Application.WorksheetFunction.VLookup(DataArray(i, 1), Range("A:B"), 2, False) Next i But why are you putting this into an array? You could just use the VLOOKUP whenever you want the value. HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Here is what I do. I read into Dataarray() all my entries in a spreadsheet (about 5000). The array is like this: Dataarray(REC#,1)=Account # DataArray(Rec#,2)=Account description if there is one (if it is a valid account). To fill in the second element I do this: Windows("Chart of Accounts.xls").Activate Sheets("Entire Chart").Select Let Counterx = 0 For YY = 1 To X Let XX = 0 Set myRange = Range("A1:A65000") flag = 0 For Each c In myRange Let XX = XX + 1 If c.Value = DataArray(YY, 1) Then flag = 1 If flag = 1 Then Exit For End If Next If flag = 1 Then DataArray(YY, 2) = Cells(XX, 2) Else Let Counterx = Counterx + 1 BadAccts(Counterx, 1) = DataArray(YY, 1) 'MsgBox ("Account " & DataArray(YY, 1) & " is not in the chart of accounts!") End If Next This code takes about 45 minutes to go through 5000 records. Any suggestions would be welcomed. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
even if you try it in a new workbook?
do you have option base 1 at the top of your module? try this and see if you get the same result: Option Base 0 Sub test() Dim arr As Variant arr = Array("A", "B", "C") MsgBox arr(0) End Sub -- Gary "Mike H." wrote in message ... On the msgbox() line, I get runtime error 9, subscript out of range. "Gary Keramidas" wrote: try this: Sub test() Dim arr As Variant arr = Array("A", "B", "C") MsgBox arr(0) End Sub -- Gary "Mike H." wrote in message ... Why would the lower bound not always be one? Would not an array be populated from 1 to x? "Bernie Deitrick" wrote: Mike, LBound and UBound are used to read the lower and upper bounds of arrays whose size you don't know. If you had coded Dim DataArray(1 to 5000) As Double then it would be okay to use For i = 1 To 5000 But a lot of operations return arrays of unknown size, so using LBound and UBound to read the size makes sense. There is one other complication - Option Base, which can be used to set the Lower bound of arrays automatically... HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Bernie, This works tremendously! Instead of 45 minutes, it takes less than 45 seconds. Just one question, the LBound and UBound, does that stand for lower and upper boundary or something like that? "Bernie Deitrick" wrote: Mike, Not sure where your Account#/Account desc table is, but assuming that it is in columns A and B For i = LBound(DataArray) To UBound(DataArray) DataArray(i, 2) = Application.WorksheetFunction.VLookup(DataArray(i, 1), Range("A:B"), 2, False) Next i But why are you putting this into an array? You could just use the VLOOKUP whenever you want the value. HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Here is what I do. I read into Dataarray() all my entries in a spreadsheet (about 5000). The array is like this: Dataarray(REC#,1)=Account # DataArray(Rec#,2)=Account description if there is one (if it is a valid account). To fill in the second element I do this: Windows("Chart of Accounts.xls").Activate Sheets("Entire Chart").Select Let Counterx = 0 For YY = 1 To X Let XX = 0 Set myRange = Range("A1:A65000") flag = 0 For Each c In myRange Let XX = XX + 1 If c.Value = DataArray(YY, 1) Then flag = 1 If flag = 1 Then Exit For End If Next If flag = 1 Then DataArray(YY, 2) = Cells(XX, 2) Else Let Counterx = Counterx + 1 BadAccts(Counterx, 1) = DataArray(YY, 1) 'MsgBox ("Account " & DataArray(YY, 1) & " is not in the chart of accounts!") End If Next This code takes about 45 minutes to go through 5000 records. Any suggestions would be welcomed. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
In an empty sheet, I get "A".
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
In an empty sheet, I get "A".
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
Why would the lower bound not always be one? Would not an array be
populated from 1 to x? You can have any lower and upper bound you want. Dim Arr(-100 To 100) As Long Debug.Print LBound(Arr), UBound(Arr) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Mike H." wrote in message ... Why would the lower bound not always be one? Would not an array be populated from 1 to x? "Bernie Deitrick" wrote: Mike, LBound and UBound are used to read the lower and upper bounds of arrays whose size you don't know. If you had coded Dim DataArray(1 to 5000) As Double then it would be okay to use For i = 1 To 5000 But a lot of operations return arrays of unknown size, so using LBound and UBound to read the size makes sense. There is one other complication - Option Base, which can be used to set the Lower bound of arrays automatically... HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Bernie, This works tremendously! Instead of 45 minutes, it takes less than 45 seconds. Just one question, the LBound and UBound, does that stand for lower and upper boundary or something like that? "Bernie Deitrick" wrote: Mike, Not sure where your Account#/Account desc table is, but assuming that it is in columns A and B For i = LBound(DataArray) To UBound(DataArray) DataArray(i, 2) = Application.WorksheetFunction.VLookup(DataArray(i, 1), Range("A:B"), 2, False) Next i But why are you putting this into an array? You could just use the VLOOKUP whenever you want the value. HTH, Bernie MS Excel MVP "Mike H." wrote in message ... Here is what I do. I read into Dataarray() all my entries in a spreadsheet (about 5000). The array is like this: Dataarray(REC#,1)=Account # DataArray(Rec#,2)=Account description if there is one (if it is a valid account). To fill in the second element I do this: Windows("Chart of Accounts.xls").Activate Sheets("Entire Chart").Select Let Counterx = 0 For YY = 1 To X Let XX = 0 Set myRange = Range("A1:A65000") flag = 0 For Each c In myRange Let XX = XX + 1 If c.Value = DataArray(YY, 1) Then flag = 1 If flag = 1 Then Exit For End If Next If flag = 1 Then DataArray(YY, 2) = Cells(XX, 2) Else Let Counterx = Counterx + 1 BadAccts(Counterx, 1) = DataArray(YY, 1) 'MsgBox ("Account " & DataArray(YY, 1) & " is not in the chart of accounts!") End If Next This code takes about 45 minutes to go through 5000 records. Any suggestions would be welcomed. |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Pointer
I don't have any particular lower bound. In the other language that I am
more familiar with than VBA, an array would be sized from 1 to X, but here, I now see it can be from 0 to X. No big deal, I didn't understand that before, however. "Chip Pearson" wrote: Why would the lower bound not always be one? Would not an array be populated from 1 to x? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pointer to sub | Excel Programming | |||
Pointer please | New Users to Excel | |||
pointer | Excel Discussion (Misc queries) | |||
CopyMemory - Array of UDTs referenced as a pointer to a string | Excel Programming | |||
Copy Array pointer rather than entire array | Excel Programming |