2

I'm working on a macro right now and it's producing weird results. The part that is specifically not working is a Min function.

a1RowTemp1 = a1Row For i = 0 To diff1 intercept = Application.WorksheetFunction.intercept(a(),c()) LinReg1 = (slope * Cells(a1RowTemp1, 1)) + intercept difference1 = Worksheets("GF9").Cells(a1RowTemp1, 2) - LinReg1 e(i) = difference1 a1RowTemp1 = a1RowTemp1 + 1 Next i a2RowTemp2 = a2Row For i = 0 To diff2 intercept2 = Application.WorksheetFunction.intercept(b(), d()) LinReg2 = (slope2 * Cells(a2RowTemp2, 1)) + intercept2 difference2 = Worksheets("GF9").Cells(a2RowTemp2, 2) - LinReg2 f(i) = difference2 a2RowTemp2 = a2RowTemp2 + 1 Next i Worksheets("Chart").Cells(currentRow, 12) = Application.Max(e()) Worksheets("Chart").Cells(currentRow, 13) = Application.Min(e()) Worksheets("Chart").Cells(currentRow, 25) = Application.Max(f()) Worksheets("Chart").Cells(currentRow, 26) = Application.Min(f())

In the bottom of the code it stores the difference1 and difference2 values in arrays e() and f(). When I use the functions max/min the macro only outputs the correct values for the max functions. I suspect this has something to do with my incorrectly using the arrays.

Improve this question
7
  • @QHarr I had that at first and then changed it to what I have now. I found that it made no difference. Commented Jul 16, 2018 at 17:29
  • Without the () for e Commented Jul 16, 2018 at 17:29
  • Yes, I removed the (), and I am still getting the wrong results. When I first use e() to find the max, the numbers in e() are correct; however, when I use it again to find the min, the numbers in e() are incorrect. Commented Jul 16, 2018 at 17:50
  • you may need to supply some data and expected output. The syntax I have show below is fine for finding min of 1d array. Commented Jul 16, 2018 at 18:13
  • Have you inspected the contents of e? Commented Jul 16, 2018 at 18:16

2 Answers 2

Reset to default
1

If e is one dimensional array you should be able to write

Application.WorksheetFunction.Min(e)

Example:

Option Explicit Public Sub TEST() Dim e() e = Array(3, 4, 2, 5) MsgBox Application.WorksheetFunction.Min(e) End Sub

If you are still getting the wrong values you need to step though with F8 and check the values being assigned to e in the loop are the expected ones.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I suspect that e and f have more elements than diff1 and diff2. That would explain the fact that max works but min is probably returning an instantiated but unassigned value of zero. ReDim e(diff1) before the loop or ReDim Preserve(i-1) after the loop could correct this.
@jeeped That sounds logical. You should post as an answer . Gets my vote :-)
Hello, Jeeped, I actually did have ReDim e(diff1) and ReDim f(diff2) earlier in the macro.
@Jeeped Connection is a nightmare.
1

You've omitted the declaration and dimensioning of the e and f array. This was an important factor in your problem.

When you declared your e and f as long or double arrays, they were instantiated with zero values.

Dim v() As Double, i As Long ReDim v(5) '<~~ all zero values For i = LBound(v) To UBound(v) - 1 '<~~fill all but the last one v(i) = i + 10 Next i Debug.Print Application.Min(v) 'zero as v(5) is zero

If you want to ignore array elements that you have not assigned values to, declare the arrays as a variant type.

Dim v() As Variant, i As Long ReDim v(5) '<~~ all empty values For i = LBound(v) To UBound(v) - 1 '<~~fill all but the last one v(i) = i + 10 Next i Debug.Print Application.Min(v) '10 as v(5) is empty and not considered in Min

An unassigned variant array element is considered empty and is not used in the Min calculation.

Alternately, use one of two methods to remove unused array elements.

'... 'redimension before the loop to the known ubound redim e(diff1) For i = 0 To diff1 intercept = Application.WorksheetFunction.intercept(a(),c()) LinReg1 = (slope * Cells(a1RowTemp1, 1)) + intercept difference1 = Worksheets("GF9").Cells(a1RowTemp1, 2) - LinReg1 e(i) = difference1 a1RowTemp1 = a1RowTemp1 + 1 Next i '... 'or redimension after the loop with Preserve For i = 0 To diff2 intercept2 = Application.WorksheetFunction.intercept(b(), d()) LinReg2 = (slope2 * Cells(a2RowTemp2, 1)) + intercept2 difference2 = Worksheets("GF9").Cells(a2RowTemp2, 2) - LinReg2 f(i) = difference2 a2RowTemp2 = a2RowTemp2 + 1 Next i 'i exits with a value 1 greater than diff2 redim preserve f(i-1) '...
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.