New to VBA and trying to create a new function with offset reference

Question

Using Excel 2019

I want to create a function that one on the arguments is one cell below one of the selection, I have no idea how to do that.

I Want a function that that multiply A and B , AND offset of one row below the selection of cell of B

My attempt:

Function MYFUNCTION(Num_1 As Integer, Num_2 As Integer) As Integer

Dim Num_3 As Integer
Num_3 = Range("Num_2").Offset(1, 0).Value

MYFUNCTION = Num_1 * Num_2 * Num_3

End Function

Seems like the code is compiling successfully, but function returns #VALUE!.

Obviously... Not working.

Other then few videos on how to create you "own" A*B function , there is nothing in terms of videos on creating advance function with VBA (To clarify , I'm not talking about subroutines)

Answer 1

Use Range parameters, not Integers:

Public Function MyFunction(ByVal Num1 As Range, ByVal Num2 As Range) As Integer
    Application.Volatile
    MyFunction = Num1.Value * Num2.Value * Num2.Offset(1).Value
End Function

The Application.Volatile is (unfortunately) necessary to recalculate the formula when Num2.Offset(1)'s value changes.

It's likely not a good idea to use Integer here. I'd suggest Double, or at least Long.

It's likely a good idea to just use three parameters to begin with.

Answer 2

Two other variants of this function:

Public Function MyFunction1(Num1 As Variant, Num2 As Range)
    'remember to use two-cell range as a second argument
    MyFunction1 = Num1 * Num2(1).Value * Num2(2).Value
End Function

Public Function MyFunction2(Num1 As Variant, Num2 As Variant)
    'remember to use two-cell range as a second argument
     MyFunction2 = WorksheetFunction.Product(Num1, Num2)
End Function  

Use: =MyFunction1(B5, C2:C3) or =MyFunction2(5, C2:C3)
The first argument may be a reference or a value.

In both cases the last two factors are referenced as one argument: two-cell range. This avoids the use of Volatile statement. The difference between the two variants is in consequences of the absence or change of the third factor.

In MyFunction1 if the second reference is to one cell only, the result is correct, however it is not recalculated in case of change of the third factor. If the cell for the third factor is empty, the result is 0.

In MyFunction2 if the second reference is to one cell only, or the cell for the third factor is empty, the result is a product of two factors.