OFFSET in Excel VBA Function

[Floyd]

I am attempting to move an Excel formula from a worksheet and place it
in an Excel VBA function.

Naively, I thought that I could simply take the worksheet equation,
pass it variables, and add "Application." to all worksheet
function. I took the "Application." out of the VBA function to make it
easier to read.

Unfortunately this does not work for the OFFSET function, but I have
not been able to figure out just why.

Excel Worksheet Function:
=SUMPRODUCT(OFFSET(CI9,-MIN(CF9-Year_First,Fac_Depr),0,MIN(CF9-Year_First+1,Fac_Depr+1),1),
OFFSET(FirstInvFacTangDrillDepr,-MIN(CF9-Year_First,Fac_Depr),0,MIN(CF9-Year_First+1,Fac_Depr+1),1))

CF9,CI9 and named cells are single cells as opposed to multiple ones
such as in a range

Attempted Excel VBA Function:
Function FirstYearDepreciation(Current_Year, Year_First, Fac_Depr,
FirstInvFacTangDrillDepr, Eligible_Depr)
Dim Current_Year As Double
Dim Year_First As Double
Dim Fac_Depr As Integer
Dim FirstInvFacTangDrillDepr As Double
Dim Eligible_Depr As Variant

FirstYearDepreciation = SumProduct( _
Offset(Eligible_Depr, -Min(Current_Year - Year_First, Fac_Depr), 0,
Min(Current_Year - Year_First + 1, Fac_Depr + 1), 1), _
Offset(FirstInvFacTangDrillDepr, -Min(Current_Year - Year_First,
Fac_Depr), 0, Min(Current_Year - Year_First + 1, Fac_Depr + 1), 1))

End Function


Can anyone shed some light on this?

Thanks in advance.

Floyd

 

[RB Smissaert]

Your code didn't even compile.
Always useful to do Debug, Compile VBA project.
In this case it would have shown you the syntax errors and some more errors.

Try this:

Function FirstYearDepreciation(Current_Year As Double, _
Year_First As Double, _
Fac_Depr As Integer, _
FirstInvFacTangDrillDepr As Double, _
Eligible_Depr As Variant)

FirstYearDepreciation = _
WorksheetFunction.SumProduct(WorksheetFunction.Offset(Eligible_Depr, _
WorksheetFunction.Min(Current_Year -
Year_First, _
Fac_Depr), _
0, _
WorksheetFunction.Min(Current_Year -
Year_First + 1, _
Fac_Depr + 1), 1), _
WorksheetFunction.Offset(FirstInvFacTangDrillDepr,
_
WorksheetFunction.Min(Current_Year
- Year_First, _

Fac_Depr), _
0, _
WorksheetFunction.Min(Current_Year
- Year_First + 1, _

Fac_Depr + 1), 1))

End Function


RBS

 

[Floyd]

Thanks so much for the assistance and certainly the tips on how to
Debug.

I pasted the code in and ran it. I am still getting the same error
message, #VALUE!, but at least it is compiling.

I noticed in another post today that the Mr. Ogilvy recommended using
EVALUATE for SUMPRODUCT. I too tried this with no success.

Cheers.

 

[RB Smissaert]

OK, if you can give us 5 values for the 5 arguments and tell us what result
you expect
we might be able to figure out what the problem is.

RBS

 

[Floyd]

Certainly.

CI9 - 15.00
CF9 - 2005
Fac_Depr - 7.0


Current_Year As Double - 2005
Year_First As Double - 2005
Fac_Depr As Integer - 7
FirstInvFacTangDrillDepr As Double - 14.29%
Eligible_Depr As Variant - 15.00

Expected Value : 2.14 (0.1429*15)

Thank you.

 

[Tom Ogilvy]

That is when sumproduct is used in an array formula mode.

 

[RB Smissaert]

Although it compiles I think the problem is with Application.Offset
Not sure if this can work. I thought that Offset had to do with the Range
object
and I don't see any ranges in your code.
This bit for example:

MsgBox WorksheetFunction.Offset(14, WorksheetFunction.Min(2005, 7), _
0, _
WorksheetFunction.Min(2005 - 2005 + 1, 7
+ 1), 1)

gives an error:
object doesn't support this property or method.

You will have to pass the ranges to the function and the whole thing needs a
bit of a work-around.


RBS

 

[Floyd]

RBS,

Thank you.

There are no ranges to pass, which as you have figured out is causing
problems.

Cheers.

 

[Floyd]

I am still lost. Can you provide some explicit suggestions that I
might try. I have lots of energy around this and will spend whatever
time is necessary to solve this.

The function in the Excel worksheet works just fine. Can someone offer
any things that I might try to pass a single cell into the function,
such that the OFFSET function will work.

Thanks for everyone's assistance yesterday.

Cheers.