Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate cell value with a minimum to be displayed | Excel Discussion (Misc queries) | |||
need help with if statement that calculate minimum figure | Excel Worksheet Functions | |||
How to calculate non-zero minimum value? | Excel Discussion (Misc queries) | |||
How to calculate minimum distance between three coordinate with ex | Excel Discussion (Misc queries) | |||
Calculate Minimum numbers | Excel Worksheet Functions |