Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default How can I calculate the minimum value of a VBA array?

I have an array of numbers that I have calculated by multiplying the contents
of two other VBA arrays. I need to calculate the minimum value of the final
calculated array, and I can't get the worksheetfunction.min to work (although
it works for arrays read directly into VBA from a worksheet). According to
the error there is a type mismatch, but no matter what I do with definitions,
I can't get it to work. Here is a cut-out section of the code with the
offending line at the bottom. Thanks

Dim a, b, c, Counta, Countb, MaxData, MinimumValue
Dim CombinedData, CombinedRelativeFrequency


Cells(9, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Counta = Application.WorksheetFunction.Count(Selection)

Cells(9, 5).Select
Range(Selection, Selection.End(xlDown)).Select
Countb = Application.WorksheetFunction.Count(Selection)

Dim A3(1 To 1000, 1 To 2)
Dim A4(1 To 1000, 1 To 2)
Dim A5(1 To 1000000, 1 To 2)
Dim A6(1 To 1000, 1 To 2)
Dim A7(1 To 1000000, 1 To 2)
Dim A8(1 To 1000000)
Dim A9(1 To 1000000, 1 To 2)
Dim A10(1 To 1000000)

c = 0

For a = 1 To Counta

A3(a, 1) = Cells(8 + a, 1).Value
A3(a, 2) = Cells(8 + a, 2).Value

For b = 1 To Countb

A4(b, 1) = Cells(8 + b, 5).Value
A4(b, 2) = Cells(8 + b, 6).Value

c = c + 1

CombinedData = A3(a, 1) * A4(b, 1)
CombinedRelativeFrequency = A3(a, 2) * A4(b, 2)

If CombinedRelativeFrequency 0 Then
A5(c, 1) = CombinedData
A8(c) = CombinedData
A5(c, 2) = CombinedRelativeFrequency
Else
c = c - 1
End If

Next b

Next a

For d = 1 To Countd
MinimumValue = Application.WorksheetFunction.Min(A8())
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 747
Default How can I calculate the minimum value of a VBA array?

Point 1:
I would forget all the selecting and use the VBA's Count property instead of
the call to the Excel worksheet function (Count). When VBA makes a call to
Excel to calculate something, which is the case when you resort to calling
worksheet functions, there is a large loss of efficiency. Try this instead. I
have changed the var names to cntA and cntB and added r and r2.

Set r = Range(Cells(9, 1), Cells(9, 1).end(xlDown))
cntA = r.Count
Set r2 = Range(Cells(9, 5), Cells(9, 5).end(xlDown))
cntB = r2.Count

The above is clearer, but alternatively forget r and r2 and just go with:

cntA = Range(Cells(9, 1), Cells(9, 1).end(xlDown)).Count
cntB = Range(Cells(9, 5), Cells(9, 5).end(xlDown)).Count


Point 2:
Granted, I haven't seen all the code, but from what I see, there's no need
for the arrays. You can just read directly from the cell ranges themselves
iterating through each cell and do the data crunching and forget populating
and reading from the arrays.

Point 3:
In your internal loop, you first increment c by 1 (c = c + 1) but then
offset this if it turns out that CombinedRelativeFrequency is not greater
than zero (c = c - 1). Why not just increment c if CombinedRelativeFrequency
is greater than zero. Then you don't have to offset it if it turns out it is
not:
If CombinedRelativeFrequency 0 Then
c = c + 1
A5(c, 1) = CombinedData
A8(c) = CombinedData
A5(c, 2) = CombinedRelativeFrequency
End If

Point 4:
(I substitute MinVal for MinimumValue)
To answer your question, when populating the A8 array inside the inner loop,
I would just compare A8(c) (i.e. each new addition to the array) to the
current value of MinVal. If and only if A8(c) is less than MinVal then change
MinVal to A8(c). After populating A8, MinVal will be the smallest value in
the array. One cunnundrum is that the default value of MinVal is zero. It
needs to be equated to the first value in the array on the first iteration of
the inner loop or it may never be changed from zero:

A8(c) = CombinedData
If c = 1 Then MinVal = A8(c) Else MinVal = IIF(A8(c) < MinVal, A8(c), MinVal)

Just a quick-and-dirty assessment with no testing. Hope it was on track.

Greg


"DJMF" wrote:

I have an array of numbers that I have calculated by multiplying the contents
of two other VBA arrays. I need to calculate the minimum value of the final
calculated array, and I can't get the worksheetfunction.min to work (although
it works for arrays read directly into VBA from a worksheet). According to
the error there is a type mismatch, but no matter what I do with definitions,
I can't get it to work. Here is a cut-out section of the code with the
offending line at the bottom. Thanks

Dim a, b, c, Counta, Countb, MaxData, MinimumValue
Dim CombinedData, CombinedRelativeFrequency


Cells(9, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Counta = Application.WorksheetFunction.Count(Selection)

Cells(9, 5).Select
Range(Selection, Selection.End(xlDown)).Select
Countb = Application.WorksheetFunction.Count(Selection)

Dim A3(1 To 1000, 1 To 2)
Dim A4(1 To 1000, 1 To 2)
Dim A5(1 To 1000000, 1 To 2)
Dim A6(1 To 1000, 1 To 2)
Dim A7(1 To 1000000, 1 To 2)
Dim A8(1 To 1000000)
Dim A9(1 To 1000000, 1 To 2)
Dim A10(1 To 1000000)

c = 0

For a = 1 To Counta

A3(a, 1) = Cells(8 + a, 1).Value
A3(a, 2) = Cells(8 + a, 2).Value

For b = 1 To Countb

A4(b, 1) = Cells(8 + b, 5).Value
A4(b, 2) = Cells(8 + b, 6).Value

c = c + 1

CombinedData = A3(a, 1) * A4(b, 1)
CombinedRelativeFrequency = A3(a, 2) * A4(b, 2)

If CombinedRelativeFrequency 0 Then
A5(c, 1) = CombinedData
A8(c) = CombinedData
A5(c, 2) = CombinedRelativeFrequency
Else
c = c - 1
End If

Next b

Next a

For d = 1 To Countd
MinimumValue = Application.WorksheetFunction.Min(A8())

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10,593
Default How can I calculate the minimum value of a VBA array?

It is very hard to see what is wrong without seeing the data. Min works fine
in itself even in a VBA created and loaded array.

Can you post an example of the data, in text form, that shows the problem.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"DJMF" wrote in message
...
I have an array of numbers that I have calculated by multiplying the
contents
of two other VBA arrays. I need to calculate the minimum value of the
final
calculated array, and I can't get the worksheetfunction.min to work
(although
it works for arrays read directly into VBA from a worksheet). According
to
the error there is a type mismatch, but no matter what I do with
definitions,
I can't get it to work. Here is a cut-out section of the code with the
offending line at the bottom. Thanks

Dim a, b, c, Counta, Countb, MaxData, MinimumValue
Dim CombinedData, CombinedRelativeFrequency


Cells(9, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Counta = Application.WorksheetFunction.Count(Selection)

Cells(9, 5).Select
Range(Selection, Selection.End(xlDown)).Select
Countb = Application.WorksheetFunction.Count(Selection)

Dim A3(1 To 1000, 1 To 2)
Dim A4(1 To 1000, 1 To 2)
Dim A5(1 To 1000000, 1 To 2)
Dim A6(1 To 1000, 1 To 2)
Dim A7(1 To 1000000, 1 To 2)
Dim A8(1 To 1000000)
Dim A9(1 To 1000000, 1 To 2)
Dim A10(1 To 1000000)

c = 0

For a = 1 To Counta

A3(a, 1) = Cells(8 + a, 1).Value
A3(a, 2) = Cells(8 + a, 2).Value

For b = 1 To Countb

A4(b, 1) = Cells(8 + b, 5).Value
A4(b, 2) = Cells(8 + b, 6).Value

c = c + 1

CombinedData = A3(a, 1) * A4(b, 1)
CombinedRelativeFrequency = A3(a, 2) * A4(b, 2)

If CombinedRelativeFrequency 0 Then
A5(c, 1) = CombinedData
A8(c) = CombinedData
A5(c, 2) = CombinedRelativeFrequency
Else
c = c - 1
End If

Next b

Next a

For d = 1 To Countd
MinimumValue = Application.WorksheetFunction.Min(A8())



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,582
Default How can I calculate the minimum value of a VBA array?

Greg -

Point 2:
Granted, I haven't seen all the code, but from what I see, there's no need
for the arrays. You can just read directly from the cell ranges themselves
iterating through each cell and do the data crunching and forget
populating
and reading from the arrays.


This would be *much* more inefficient than calling WorksheetFunction.
Transferring data across the VBA-Excel interface multiple times per cell is
much worse than calling the function once per array.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Greg Wilson" wrote in message
...
Point 1:
I would forget all the selecting and use the VBA's Count property instead
of
the call to the Excel worksheet function (Count). When VBA makes a call to
Excel to calculate something, which is the case when you resort to calling
worksheet functions, there is a large loss of efficiency. Try this
instead. I
have changed the var names to cntA and cntB and added r and r2.

Set r = Range(Cells(9, 1), Cells(9, 1).end(xlDown))
cntA = r.Count
Set r2 = Range(Cells(9, 5), Cells(9, 5).end(xlDown))
cntB = r2.Count

The above is clearer, but alternatively forget r and r2 and just go with:

cntA = Range(Cells(9, 1), Cells(9, 1).end(xlDown)).Count
cntB = Range(Cells(9, 5), Cells(9, 5).end(xlDown)).Count


Point 2:
Granted, I haven't seen all the code, but from what I see, there's no need
for the arrays. You can just read directly from the cell ranges themselves
iterating through each cell and do the data crunching and forget
populating
and reading from the arrays.

Point 3:
In your internal loop, you first increment c by 1 (c = c + 1) but then
offset this if it turns out that CombinedRelativeFrequency is not greater
than zero (c = c - 1). Why not just increment c if
CombinedRelativeFrequency
is greater than zero. Then you don't have to offset it if it turns out it
is
not:
If CombinedRelativeFrequency 0 Then
c = c + 1
A5(c, 1) = CombinedData
A8(c) = CombinedData
A5(c, 2) = CombinedRelativeFrequency
End If

Point 4:
(I substitute MinVal for MinimumValue)
To answer your question, when populating the A8 array inside the inner
loop,
I would just compare A8(c) (i.e. each new addition to the array) to the
current value of MinVal. If and only if A8(c) is less than MinVal then
change
MinVal to A8(c). After populating A8, MinVal will be the smallest value in
the array. One cunnundrum is that the default value of MinVal is zero. It
needs to be equated to the first value in the array on the first iteration
of
the inner loop or it may never be changed from zero:

A8(c) = CombinedData
If c = 1 Then MinVal = A8(c) Else MinVal = IIF(A8(c) < MinVal, A8(c),
MinVal)

Just a quick-and-dirty assessment with no testing. Hope it was on track.

Greg


"DJMF" wrote:

I have an array of numbers that I have calculated by multiplying the
contents
of two other VBA arrays. I need to calculate the minimum value of the
final
calculated array, and I can't get the worksheetfunction.min to work
(although
it works for arrays read directly into VBA from a worksheet). According
to
the error there is a type mismatch, but no matter what I do with
definitions,
I can't get it to work. Here is a cut-out section of the code with the
offending line at the bottom. Thanks

Dim a, b, c, Counta, Countb, MaxData, MinimumValue
Dim CombinedData, CombinedRelativeFrequency


Cells(9, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Counta = Application.WorksheetFunction.Count(Selection)

Cells(9, 5).Select
Range(Selection, Selection.End(xlDown)).Select
Countb = Application.WorksheetFunction.Count(Selection)

Dim A3(1 To 1000, 1 To 2)
Dim A4(1 To 1000, 1 To 2)
Dim A5(1 To 1000000, 1 To 2)
Dim A6(1 To 1000, 1 To 2)
Dim A7(1 To 1000000, 1 To 2)
Dim A8(1 To 1000000)
Dim A9(1 To 1000000, 1 To 2)
Dim A10(1 To 1000000)

c = 0

For a = 1 To Counta

A3(a, 1) = Cells(8 + a, 1).Value
A3(a, 2) = Cells(8 + a, 2).Value

For b = 1 To Countb

A4(b, 1) = Cells(8 + b, 5).Value
A4(b, 2) = Cells(8 + b, 6).Value

c = c + 1

CombinedData = A3(a, 1) * A4(b, 1)
CombinedRelativeFrequency = A3(a, 2) * A4(b, 2)

If CombinedRelativeFrequency 0 Then
A5(c, 1) = CombinedData
A8(c) = CombinedData
A5(c, 2) = CombinedRelativeFrequency
Else
c = c - 1
End If

Next b

Next a

For d = 1 To Countd
MinimumValue = Application.WorksheetFunction.Min(A8())



  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,582
Default How can I calculate the minimum value of a VBA array?

You've declared your arrays with no specific data type, so they are by
default variants. Variants could contain strings, numbers, or errors, and
only an array of numbers can have a valid minimum. What types of values do
the arrays contain? You need to check each array element to be certain.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"DJMF" wrote in message
...
I have an array of numbers that I have calculated by multiplying the
contents
of two other VBA arrays. I need to calculate the minimum value of the
final
calculated array, and I can't get the worksheetfunction.min to work
(although
it works for arrays read directly into VBA from a worksheet). According
to
the error there is a type mismatch, but no matter what I do with
definitions,
I can't get it to work. Here is a cut-out section of the code with the
offending line at the bottom. Thanks

Dim a, b, c, Counta, Countb, MaxData, MinimumValue
Dim CombinedData, CombinedRelativeFrequency


Cells(9, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Counta = Application.WorksheetFunction.Count(Selection)

Cells(9, 5).Select
Range(Selection, Selection.End(xlDown)).Select
Countb = Application.WorksheetFunction.Count(Selection)

Dim A3(1 To 1000, 1 To 2)
Dim A4(1 To 1000, 1 To 2)
Dim A5(1 To 1000000, 1 To 2)
Dim A6(1 To 1000, 1 To 2)
Dim A7(1 To 1000000, 1 To 2)
Dim A8(1 To 1000000)
Dim A9(1 To 1000000, 1 To 2)
Dim A10(1 To 1000000)

c = 0

For a = 1 To Counta

A3(a, 1) = Cells(8 + a, 1).Value
A3(a, 2) = Cells(8 + a, 2).Value

For b = 1 To Countb

A4(b, 1) = Cells(8 + b, 5).Value
A4(b, 2) = Cells(8 + b, 6).Value

c = c + 1

CombinedData = A3(a, 1) * A4(b, 1)
CombinedRelativeFrequency = A3(a, 2) * A4(b, 2)

If CombinedRelativeFrequency 0 Then
A5(c, 1) = CombinedData
A8(c) = CombinedData
A5(c, 2) = CombinedRelativeFrequency
Else
c = c - 1
End If

Next b

Next a

For d = 1 To Countd
MinimumValue = Application.WorksheetFunction.Min(A8())



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
calculate cell value with a minimum to be displayed Dearoledad Excel Discussion (Misc queries) 3 October 17th 09 01:10 AM
need help with if statement that calculate minimum figure KateZed Excel Worksheet Functions 2 November 17th 08 07:41 PM
How to calculate non-zero minimum value? yhoy Excel Discussion (Misc queries) 5 February 24th 08 02:41 AM
How to calculate minimum distance between three coordinate with ex Jonathan[_2_] Excel Discussion (Misc queries) 2 November 29th 07 04:12 AM
Calculate Minimum numbers Pete Excel Worksheet Functions 2 May 12th 05 04:31 PM


All times are GMT +1. The time now is 12:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"