Passing a row range to Excel User Define Function and assign it to an array

Question

I am trying to

pass two ranges - multiple column single row - to a user defined function in Excel 2007,

then assigning it to an array for processing.

Function MAE(actualData As Range, forecastData As Range) As Double

 Dim data
 Dim forecast
 Dim error As Double
 Dim average As Double
 Dim i As Long

 data = Application.Transpose(actualData)
 forecast = Application.Transpose(forecastData)
 average = 0
 error = 0

 For i = 1 To UBound(data)

   error = data(i) - forecast(i)
   If error < 0 Then
     error = error * -1
   End If

   average = error + average

 Next i

 MAE = average / UBound(data)

 End Function

I have posted a thread earlier in this forum, here is the link

In that thread I asked about passing a single column as Range to a user defined function. After your suggestions, I modified the code and it is working perfectly when I pass two single columns.

But when I pass two rows then it is not working. I am getting #Value error. Any suggestions about this?

Thanks KazJaw for editing, I put the spaces for code but it was not working. — MrA2Z
– MrA2Z, Commented May 16, 2013 at 21:00
You don't need a user-defined function to do this calculation. For example, if A1:A8 and B1:B8 are the ranges that you want to compare, you can calculate the mean absolute difference by entering =AVERAGE(ABS(B1:B8-A1:A8)) as an array formula in a worksheet cell (that is, by pressing Ctrl+Shift+Enter instead of just Enter after typing in the formula). — Brian Camire
– Brian Camire, Commented May 16, 2013 at 21:21
Thanks a lot for saving my time, this is a good suggestion. I will use it now but still I would try to develop code for this and see why it is not working. — MrA2Z
– MrA2Z, Commented May 16, 2013 at 21:37

David Zemens · Accepted Answer · 2013-05-17 03:27:11Z

3

THere's a problem with the way you're refering to your Variants by index.

If you review in the Locals window, you can see that the structure is a 2-dimensional array for each of data and forecast, but you are referring to them as 1-d arrays, which will cause the formula to fail.

Simply revise to:

error = data(i, 1) - forecast(i, 1)

And that should avoid the error.

Here is the Locals window, which shows the dimensions and lets you browse the arrays.

Screenshot of locals window

answered May 17, 2013 at 3:27

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

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

2 Comments

MrA2Z Over a year ago

Thanks. Isn't Application.Transpose converting it to a single dimensional array? I get this suggestion from my other thread linked in the question. A column can be assigned to an array using Application.Transpose then how can I do the same thing for row?

David Zemens Over a year ago

Try adding another Transpose, like, after forecast = Application.Transpose(forecastData) then do: forecast = Application.Transpose(forecast). This seems to work for me to convert to a 1d array, although this doesn't seem at all intuitive (or correct) based on the documentation.

Collectives™ on Stack Overflow

Passing a row range to Excel User Define Function and assign it to an array

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related