Hello All,

I'm hoping to find a Win32 API which will provide me the memory size
of a safe array dimmed in VBA.

Here's what I am trying to solve...

I currently calculate a moving average of the last x elements stored
within an array. Step one is to find the last element used within the
array, for which I currently use a self-written binary search. Next,
I use a Win32 api to copy the segment of memory (CopyMemory)
containing the elements I want to average into another array so that I
can execute:

Avg = Application.Average(arrayTwo)

The slowest part of this oppoeration is the binary search. So, I was
hoping to get the size of the memory used by the array and then devide
the returned value by the number of bytes per element to determin the
number of the last element used within the array. (Please note,
Ubound only gives me the upper bound of the array, not the last used
element in the array).

Any ideas??

Thanks!

Trip

Start with UBOUND position and iterate backwards to find the last used
element?

--
Jim
"Trip" <(E-Mail Removed)> wrote in message
news:8bcedc6d-11e4-4416-9edb-(E-Mail Removed)...
| Hello All,
|
| I'm hoping to find a Win32 API which will provide me the memory size
| of a safe array dimmed in VBA.
|
| Here's what I am trying to solve...
|
| I currently calculate a moving average of the last x elements stored
| within an array. Step one is to find the last element used within the
| array, for which I currently use a self-written binary search. Next,
| I use a Win32 api to copy the segment of memory (CopyMemory)
| containing the elements I want to average into another array so that I
| can execute:
|
| Avg = Application.Average(arrayTwo)
|
| The slowest part of this oppoeration is the binary search. So, I was
| hoping to get the size of the memory used by the array and then devide
| the returned value by the number of bytes per element to determin the
| number of the last element used within the array. (Please note,
| Ubound only gives me the upper bound of the array, not the last used
| element in the array).
|
| Any ideas??
|
| Thanks!
|
| Trip

Binary search on a sorted array should be faster than using
Application.Average, so I suspect there is something else happening.

Also its probably faster to just directly compute the average by looping on
the array rather than passing a subset to an Excel worksheet function.
The time taken to do both these operations will usually be insignificant
compared to the time taken to get the data into a variant array in the first
place.

Can you post some code, along with an indication of how many microseconds
you need this to take?

regards
Charles
_________________________________________
FastExcel 2.3
Name Manager 4.0
http://www.DecisionModels.com

"Trip" <(E-Mail Removed)> wrote in message
news:8bcedc6d-11e4-4416-9edb-(E-Mail Removed)...
> Hello All,
>
> I'm hoping to find a Win32 API which will provide me the memory size
> of a safe array dimmed in VBA.
>
> Here's what I am trying to solve...
>
> I currently calculate a moving average of the last x elements stored
> within an array. Step one is to find the last element used within the
> array, for which I currently use a self-written binary search. Next,
> I use a Win32 api to copy the segment of memory (CopyMemory)
> containing the elements I want to average into another array so that I
> can execute:
>
> Avg = Application.Average(arrayTwo)
>
> The slowest part of this oppoeration is the binary search. So, I was
> hoping to get the size of the memory used by the array and then devide
> the returned value by the number of bytes per element to determin the
> number of the last element used within the array. (Please note,
> Ubound only gives me the upper bound of the array, not the last used
> element in the array).
>
> Any ideas??
>
> Thanks!
>
> Trip

Trip wrote:. . .
> I currently calculate a moving average of the last x elements stored
> within an array. Step one is to find the last element used within the
> array, for which I currently use a self-written binary search. Next,
> I use a Win32 api to copy the segment of memory (CopyMemory)
> containing the elements I want to average into another array so that I
> can execute:
>
> Avg = Application.Average(arrayTwo)
>
> The slowest part of this oppoeration is the binary search. . . .
>
> Any ideas??
>

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you can
get the index number of the last used element with

UBound(arrayOne) - LBound(arrayOne) + 1 - ArrayCountIf(arrayOne)

I have no idea how it compares in speed to your binary search.

Alan Beban

How come you don't know the last used element in the array?
What/who is filling that array?
If it is your code you should be able to keep track of that.

RBS


"Trip" <(E-Mail Removed)> wrote in message
news:8bcedc6d-11e4-4416-9edb-(E-Mail Removed)...
> Hello All,
>
> I'm hoping to find a Win32 API which will provide me the memory size
> of a safe array dimmed in VBA.
>
> Here's what I am trying to solve...
>
> I currently calculate a moving average of the last x elements stored
> within an array. Step one is to find the last element used within the
> array, for which I currently use a self-written binary search. Next,
> I use a Win32 api to copy the segment of memory (CopyMemory)
> containing the elements I want to average into another array so that I
> can execute:
>
> Avg = Application.Average(arrayTwo)
>
> The slowest part of this oppoeration is the binary search. So, I was
> hoping to get the size of the memory used by the array and then devide
> the returned value by the number of bytes per element to determin the
> number of the last element used within the array. (Please note,
> Ubound only gives me the upper bound of the array, not the last used
> element in the array).
>
> Any ideas??
>
> Thanks!
>
> Trip

Ahhh, beautiful! Thanks!

On Dec 20, 2:41 pm, Alan Beban <unavaila...@no.com> wrote:
> Trip wrote:. . .
> > I currently calculate a moving average of the last x elements stored
> > within anarray. Step one is to find the last element used within the
> >array, for which I currently use a self-written binary search. Next,
> > I use a Win32 api to copy the segment ofmemory(CopyMemory)
> > containing the elements I want to average into anotherarrayso that I
> > can execute:
>
> > Avg = Application.Average(arrayTwo)
>
> > The slowest part of this oppoeration is the binary search. . . .
>
> > Any ideas??
>
> If the functions in the freely downloadable file athttp://home.pacbell.net/bebanare available to your workbook, you can
> get the index number of the last used element with
>
> UBound(arrayOne) - LBound(arrayOne) + 1 - ArrayCountIf(arrayOne)
>
> I have no idea how it compares in speed to your binary search.
>
> Alan Beban

Ooops, I spoke too soon. I reviewed the code of ArrayCountIf and it
actually loops through the entire array. This would be much slower
than a binary search. Good idea though. Thanks!

Trip

On Dec 20, 7:43 pm, Trip <t...@consultant.com> wrote:
> Ahhh, beautiful! Thanks!
>
> On Dec 20, 2:41 pm, Alan Beban <unavaila...@no.com> wrote:
>
>
>
> > Trip wrote:. . .
> > > I currently calculate a moving average of the last x elements stored
> > > within anarray. Step one is to find the last element used within the
> > >array, for which I currently use a self-written binary search. Next,
> > > I use a Win32 api to copy the segment ofmemory(CopyMemory)
> > > containing the elements I want to average into anotherarrayso that I
> > > can execute:
>
> > > Avg = Application.Average(arrayTwo)
>
> > > The slowest part of this oppoeration is the binary search. . . .
>
> > > Any ideas??
>
> > If the functions in the freely downloadable file athttp://home.pacbell.net/bebanareavailable to your workbook, you can
> > get the index number of the last used element with
>
> > UBound(arrayOne) - LBound(arrayOne) + 1 - ArrayCountIf(arrayOne)
>
> > I have no idea how it compares in speed to your binary search.
>
> > Alan Beban- Hide quoted text -
>
> - Show quoted text -

Too bad your array is not a String array... getting the index of the last
used element is a one-liner for that type of array.

LastUsedIndex = UBound(Split(Trim(Replace(Replace(Join(MyArray, _
Chr$(0)), " ", Chr$(1)), Chr$(0), " "))))

Unfortunately, you can't use the same trick on a numeric array.

Rick


"Trip" <(E-Mail Removed)> wrote in message
news:8bcedc6d-11e4-4416-9edb-(E-Mail Removed)...
> Hello All,
>
> I'm hoping to find a Win32 API which will provide me the memory size
> of a safe array dimmed in VBA.
>
> Here's what I am trying to solve...
>
> I currently calculate a moving average of the last x elements stored
> within an array. Step one is to find the last element used within the
> array, for which I currently use a self-written binary search. Next,
> I use a Win32 api to copy the segment of memory (CopyMemory)
> containing the elements I want to average into another array so that I
> can execute:
>
> Avg = Application.Average(arrayTwo)
>
> The slowest part of this oppoeration is the binary search. So, I was
> hoping to get the size of the memory used by the array and then devide
> the returned value by the number of bytes per element to determin the
> number of the last element used within the array. (Please note,
> Ubound only gives me the upper bound of the array, not the last used
> element in the array).
>
> Any ideas??
>
> Thanks!
>
> Trip