0

I apologize for the length here, but my VBA knowledge is very limited and I want to be clear.

Attempting to shorten and speed Excel VBA Function where one of the calculations sums a series of "N" number absolute differences between a price value ("Y0") and a prior price value ("Y1"). The arithmetic formula looks like this: 

Sum( ABS(Y0 - Y1) + ABS(Y1 - Y2) + ABS(Y2 - Y3) + . . . )

or 

Sum( ABS(Price - Price.Offset(-1, 0)) + Abs(Price.Offset(-1, 0) + . . . )

Coding the individually referenced price changes is cumbersome and slow. In Excel the following array formula calculates the correct result for one cell in a N = 10 Array:

{ =Sum(Abs($I15:$I24-($I14:$I23))) }

This formula calculates the sum of the absolute differences in the Price series in column I for N = 10 periods. The two ranges in the array are offset by 1 period.

The value from this calculation is the denominator in a ratio used in the Function calculation. I would like VBA to calculate the array sum of absolute differences rather than the arithmetic series.

I have been able to return the addresses for both ranges for N periods in the array, but have not been successful in getting the array calculation of the sum of absolute differences.

It is clear I am not using array calculation properly, if at all. I can get the sum of the arrays but not much beyond and not the sum of the absolute differences as needed. Any help would be greatly appreciated.

'Volatility Formula { =SUM(ABS(Y:Yn)-(Y1:Yn1))) }

RRange = Range(RAddress)

R1Range = Range(R1Address)

R = Application.WorksheetFunction.Sum(Abs(RRange - R1Range))

I would like to calculate the following Excel array Formula and an array in a VBA Function.

{ =SUM(ABS($I15:$I24-($I14:$I23))) }
Improve this question
4
  • Thank you for the information and thank you all for the assistance. Have been working on the problem. On the first response from Mr. Williams, the formula works when entered directly into the spreadsheet (just as mine does) but not from a VBA function. On the second response from TM, still working on it but not much luck yet. Again, thank you. Commented May 6, 2019 at 23:17
  • The problem gets down to getting a VBA function to take a variable containing the range address of one array and subtract from it a 1 period lagged range address of a second variable - just as if this were entered into the spreadsheet as an array formula: { $A$2:$A$10 - $A$1:$A$9 } Commented May 6, 2019 at 23:20
  • Edited my original answer due to your comments above; hopefully I understood your issue :-) @wsn3 Commented May 22, 2019 at 16:07
  • Successfully incorporated a response from R. Roe on May 14th. It works as very nicely and as advertised. This is to thank everyone who responded. All of the comments and responses were most helpful, and all of your efforts are greatly appreciated. Thank you. Commented May 24, 2019 at 13:24

2 Answers 2

Reset to default
2

You can use Evaluate for this - it works fine for array formulas:

Dim result
result = Sheet1.Evaluate("SUM(ABS($I15:$I24-($I14:$I23)))")

Where Sheet1 is a reference to the worksheet which has the data. Do not use the Application.Evaluate form, which will evaluate for formula in the context of the ActiveSheet.

enter image description here

enter image description here

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

4 Comments

Thank you for the input but no go. Produced same #Value! error whether using reference to named ranges or the absolute reference sum(abs($I15:$I24-$I14:$I23)). Not sure why the problem. Thank you though.
Works for me - see screenshots above.
Posted a VBA alternative to Tim's valid & fast solution+1 :-) @wsn3
Successfully incorporated a response from R. Roe on May 14th. It works as very nicely and as advertised. This is to thank everyone who responded. All of the comments and responses were most helpful, and all of your efforts are greatly appreciated. Thank you.
0

VBA solution

In addition to @TimWilliam's absolutely valid solution I demonstrate a VBA array approach as wanted in your post (using the same range references for better comparison). You should be in the position to transform this example code to a function. BTW it's faster to loop through an array instead cells by means of VBA only. 

Option Explicit                            ' declaration head of code module

Sub GetAbsDiffSum()
  Dim rng As Range, v(), i&, newVal#, oldVal#
  Set rng = Sheet1.Range("A1:A10")         ' range reference via worksheet's code name
' create 1-based 2-dim datafield array and transpose it to 1-dim "flat" array
  v = Application.Transpose(rng)
' remember first value for next comparison
  oldVal = v(1): v(1) = ""                ' [0](delete first value)
' calculate via array loop                ' --- important: don't change order of assignments!
  For i = 2 To UBound(v)                  '     start loop from 2nd row (1-based array: equals LBound(v) + 1)
      newVal = Abs(v(i) - oldVal)         ' [1] calculate absolute difference using remembered value
      oldVal = v(i)                       ' [2] remember current value for next comparison
      v(i) = newVal                       ' [3] overwrite current array value with absolute difference
  Next i
  Debug.Print "result = " & Application.Sum(v), _
              "equals sum of " & Join(v, ", ")
End Sub

Side note

As you mention a #Value!" error in your comment to Tim's approach, you could study the following MS support site, try to detect a possible cell reference to an error cell and correct the issue before code execution: How to correct a value error.

Edit due to comments/5-6 2019

Apparently your issue is to create a user defined function including a flexible range argument which could be called from a cell in your worksheet (e.g. =AbsDiffs(A1:A10) or =AbsDiffs(Sheet1!A1:A10)).

The passed rng argument needs no further qualification within the function ...and don't forget to return the function result (see section [4])

Take care of including all values to be compared into the range definition. Assuming a given range of A1:A10 the function would start to calculate Abs(A2-A1) first, then Abs(A3-A2), … ending with Abs(A10-A9) and a total summation.

Option Explicit                      ' declaration head of your code module

Function AbsDiffs(rng As Range) As Double
  Dim v(), i&, newVal#, oldVal#
' create 1-based 2-dim datafield array and transpose it to 1-dim "flat" array
  v = Application.Transpose(rng)
' remember first value for next comparison
  oldVal = v(1): v(1) = ""                ' [0](delete first value)
' calculate via array loop                ' --- important: don't change order of assignments!
  For i = 2 To UBound(v)                  '     start loop from 2nd row (1-based array: equals LBound(v) + 1)
      newVal = Abs(v(i) - oldVal)         ' [1] calculate absolute difference using remembered value
      oldVal = v(i)                       ' [2] remember current value for next comparison
      v(i) = newVal                       ' [3] overwrite current array value with absolute difference
  Next i
  AbsDiffs = Application.Sum(v)           ' [4] return function result
End Function
Improve this answer

2 Comments

@wsn3 - edited my answer to show an Excel function usable via sheet formula, e.g. =AbsDiffs(Sheet1!A1:A10)
Successfully incorporated a response from R. Roe on May 14th. It works as very nicely and as advertised. This is to thank everyone who responded. All of the comments and responses were most helpful, and all of your efforts are greatly appreciated. Thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.