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?

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." <(E-Mail Removed)> wrote in message
news:780F35DB-BED2-48C0-B687-(E-Mail Removed)...
> 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?

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.

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." <(E-Mail Removed)> wrote in message
news:29A0ED4B-B8B1-4B75-8910-(E-Mail Removed)...
> 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.
>

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.

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

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." <(E-Mail Removed)> wrote in message
> news:29A0ED4B-B8B1-4B75-8910-(E-Mail Removed)...
> > 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.
> >
>
>
>

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." <(E-Mail Removed)> wrote in message
news:EF4F2B0F-A606-4D83-8737-(E-Mail Removed)...
> 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." <(E-Mail Removed)> wrote in message
>> news:29A0ED4B-B8B1-4B75-8910-(E-Mail Removed)...
>> > 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.
>> >
>>
>>
>>

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." <(E-Mail Removed)> wrote in message
> news:EF4F2B0F-A606-4D83-8737-(E-Mail Removed)...
> > 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." <(E-Mail Removed)> wrote in message
> >> news:29A0ED4B-B8B1-4B75-8910-(E-Mail Removed)...
> >> > 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.
> >> >
> >>
> >>
> >>
>
>
>

try this:
Sub test()
Dim arr As Variant
arr = Array("A", "B", "C")
MsgBox arr(0)
End Sub

--


Gary


"Mike H." <(E-Mail Removed)> wrote in message
news:C8939C3B-A74A-4608-8A79-(E-Mail Removed)...
> 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." <(E-Mail Removed)> wrote in message
>> news:EF4F2B0F-A606-4D83-8737-(E-Mail Removed)...
>> > 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." <(E-Mail Removed)> wrote in message
>> >> news:29A0ED4B-B8B1-4B75-8910-(E-Mail Removed)...
>> >> > 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.
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>