Using a function with part of an array

[CPerry]

I have attached a screenshot of a range of values in column A and - using a formula - the outcome I desire in column B. Upon each row, I want to know the maximum value that has come previously so using vba I have used a loop. The problem I have with the code in the screenshot is that it uses cell references for the max function line so I go from code to sheet to code to sheet etc. I want to run this solely with code so it runs quicker. My actual project uses 360,000 rows and 6/8 columns.
29469

My question, how would I use 'inarr' array in my screenshot with the worksheetfunction.max line to achieve the same values in column B?

Thank you in advance.

CPerry.

[Bob Phillips]

Come on. You have been here long enough to know that screenshots are hard work and incomplete, a sample workbook is far better.

[CPerry]

Apologies. I have not been on this forum for quite some time. I've had a lengthy hiatus from my project. I have attached the workbook.

[Bob Phillips]

Thanks, much better.

Try this.


Sub Test()
Const BASE_FORMULA = "=MAX($A$1:A1)-A1"
Dim lastrow As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("B1").Resize(lastrow)

.Formula = BASE_FORMULA

Application.Calculation = xlCalculationAutomatic
.Value = .Value
End With
End With

Application.ScreenUpdating = True
End Sub

[Paul_Hossler]

If you have lots of data, using arrays might be fast



Option Explicit




Sub Test_2()
Dim aryIn As Variant, aryOut() As Double
Dim rIn As Range, rOut As Range
Dim i As Long, j As Long
Dim dMax As Double


Set rIn = ActiveSheet.Cells(1, 1)
Set rIn = Range(rIn, rIn.End(xlDown))
Set rOut = rIn.Offset(0, 1).Resize(rIn.Rows.Count, 1)

aryIn = Application.WorksheetFunction.Transpose(rIn.Value)

ReDim aryOut(1 To UBound(aryIn))


For i = LBound(aryIn) To UBound(aryIn)
dMax = 0#
For j = 1 To i
If aryIn(j) > dMax Then dMax = aryIn(j)
Next j
aryOut(i) = dMax - aryIn(i)
Next i

rOut.Value = Application.WorksheetFunction.Transpose(aryOut)
End Sub

[CPerry]

Thank you both, I have added a slight variation of your code to my main project Bob and it worked... until:

"There isn't enough memory to complete this action.
Try using less data or closing other applications.
To increase memory available, consider using a 64-bit version of Microsoft Excel.

If you continue the task with the current selection, it can't be undone.
To be able to undo, cancel and try selecting less data or closing other applications. Continue Anyway?"

I do have lots of data Paul and I am thinking that keeping everything in arrays would be more efficient also. I have SO much to be crunched that the code is taking some time to run. My new problem means that I can't output another 360k+ extra rows. Truthfully, I only need to know the average value and the maximum value from this array so I may just output two cells.

If I want the average of the array in cell N5 and the maximum value in the array in cell Q5, what would be the most efficient way of doing this please?

[Paul_Hossler]

If you do not want the MAX for Row 1 to the current cell like in your first question, why not just use AVERAGE() and MAX() in N5 and Q5?

[CPerry]

Using the original workbook as reference, I don't want Column B to be written to the sheet as I don't have enough memory, rather I want to calculate the values for column B in an array then find the average from the values in this array and the maximum value from this array and output these two values to cells N5 and Q5. Only these two values, column B not at all.

This is because I already have several columns (each 360k+ rows) being written to the sheet already. I don't have enough memory to write column B to the sheet also and I only really need those two values as my output. What would be the most efficient way of doing this please?

[Aussiebear]

Have you considered creating a batching sequence so that you save then release memory to fulfil your process?

[snb]

Sub M_snb()
sn = Cells(1).CurrentRegion.Resize(, 3)
sn(1, 2) = sn(1, 1)
sn(1, 1) = 0


For j = 2 To UBound(sn)
sn(j, 2) = IIf(sn(j, 1) > sn(j - 1, 2), sn(j, 1), sn(j - 1, 2))
sn(j, 3) = sn(j, 2) - sn(j, 1)
sn(1, 1) = sn(1, 1) + sn(j, 3)
Next

Cells(1, 4).Resize(, 2) = Array(sn(j - 1, 2), sn(j - 1, 1) / (j - 1))
End Sub

Max in D1
Average in E1

[Paul_Hossler]

If you're that tight on memory, it might be worthwhile to reconsider your approach

However, if I'm understanding correctly, this calculates an internal-only array using your Max logic, and then puts the average and max to the worksheet

Col B is no longer written

29477




Option Explicit


Sub Test_3()
Dim aryIn As Variant, aryOut() As Double
Dim rIn As Range
Dim i As Long, j As Long
Dim dMax As Double




Set rIn = ActiveSheet.Cells(1, 1)
Set rIn = Range(rIn, rIn.End(xlDown))

aryIn = Application.WorksheetFunction.Transpose(rIn.Value)

ReDim aryOut(1 To UBound(aryIn))


dMax = 0#
For i = LBound(aryIn) To UBound(aryIn)
For j = 1 To i
If aryIn(j) > dMax Then dMax = aryIn(j)
Next j
aryOut(i) = dMax - aryIn(i)
Next i

'If I want the average of the array in cell N5 and the maximum value in the array in cell Q5,
'what would be the most efficient way of doing this please?
Range("N5").Value = Application.WorksheetFunction.Average(aryOut)
Range("Q5").Value = Application.WorksheetFunction.Max(aryOut)
End Sub

[snb]

@PH

Application.Worksheetfunction can be replaced by Application only.
Besides the Worksheetfunction can cause unpredictable errors (e.g. in lookup functions).

[Paul_Hossler]

Yes, there is a difference when using Application.WorksheetFunction.SomeFunction() and just Application.SomeFunction


1. No intellisence using Application.SomeFunction, but it's not much better using Application.WorksheetFunction.SomeFunction() either

2. When you use Application.WorksheetFunction.SomeFunction(...) with functions that may result in an error, the code stops and goes debugging mode

3. When you just use Application.SomeFunction(...), the code does not stop and go into debug mode, but instead returns the error value to a variable


So I've used both, depending on if I want/need to handle the error (#3) myself (mainly for non-programming users) or let the user sort out their code or data by opening a debug window (#2)


In this case, I decided to let CPerry sort out any data or code issues by throwing a debug error.

[CPerry]

Thank you for the help guys! I have spent the last couple of days working on separate parts of my system, I am now ready to take a look at this tomorrow (or Monday). I will examine your advice, give it a go and let you know how I have got on. Thank you for the advice so far.

[CPerry]

This is exactly what I need, thank you very much for your help!