Using Range Variables

[kingofaces]

I've run into the issue where UDF's I write return errors when I try to use range variables fairly regularly. I imagine it's just a simple solution that I haven't come across yet, so here's a simple example that should hopefully solve my other formulas:

Function MSPE(Observed As Range, Predicted As Range)

MSPE = WorksheetFunction.Sum(Observed - Predicted)

End Function

I'll enter the formula =MSPE(C69:C74,G69:G74) in a cell where each cell in the range has a value. The intent in this case is that (C69-G69)+(C70-G70) . . . is summed. Basically I want the first set of values in each range (will always be in the same row here) to be calculated, added to a total, second set calculated and added, etc. It seems like it should be simple, but I guess I'm not entirely sure how range variables work so that you can enter a range into the formula, and have the function work with specific values within that range. Any ideas on solving this formula, or range variables in general? If there is a way to see how formulas within excel are written that deal with range variables like Sum, Average, etc. that would probably be the most helpful for me, but I don't think there is a way to view those is there?

 

[Andrew Poulsom]

This worked for me:

Function MSPE(Observed As Range, Predicted As Range)
    MSPE = Evaluate("=Sum('" & Observed.Parent.Name & "'!" & Observed.Address & "-'" & Predicted.Parent.Name & "'!" & Predicted.Address & ")")
End Function

But why can't you use an array formula?

=SUM(C69:C74-G69:G74)

confirmed with Ctrl+Shift+Enter.

 

[kingofaces]

I could just use an array like that if that was all I was doing. The calculations I gave here were only a small part of the formula, but I narrowed it down to that section where the range variables in the calculations were causing problems. It would get pretty cluttered if I tried to do everything by writing out the formula manually in the cell, so I've been trying to write a UDF that simplifies things. This definitely has potential since it looks like it solved the one problem I've been having for awhile, so I'm going to need to figure out how this works with everything now. Thanks.