Using nested Excel functions as WorksheetFunction in VBA code

0

The following excel function works well in excel formula bar

=CORREL(INDEX(INDIRECT(inRng),INDIRECT(f1Rng),1):INDEX(INDIRECT(inRng),INDIRECT(l1Rng),1), INDEX(INDIRECT(inRng),INDIRECT(f2Rng),2):INDEX(INDIRECT(inRng),INDIRECT(l2Rng),2))

Please can anyone help me implementing the above function using WorksheetFunction in VBA code?

Answer
Discuss

Answers

0

Wow! That was a tought nut.
Please find below a UDF which can replace the worksheet function in column K.

Function Correl_UDF(ByVal R As Long)
    ' 19 Jul 2018
    
    Const F1 As String = "G"
    Const F2 As String = "I"
    Const L1 As String = "H"
    Const L2 As String = "J"
    
    Dim inRng As Range
    Dim Rng1 As Range, Rng2 As Range
    Dim Rr As Long                          ' conversion factor: range row to sheet row
    
    With Worksheets("Arkusz1")
        Set inRng = .Range("B2:C853")
        Rr = inRng.Row - 1
        Set Rng1 = Range(.Cells(.Cells(R, F1).Value + Rr, inRng.Columns(1).Column), _
                         .Cells(.Cells(R, L1).Value + Rr, inRng.Columns(1).Column))
        Set Rng2 = Range(.Cells(.Cells(R, F2).Value + Rr, inRng.Columns(2).Column), _
                         .Cells(.Cells(R, L2).Value + Rr, inRng.Columns(2).Column))
        Correl_UDF = Application.WorksheetFunction.Correl(Rng1, Rng2)
    End With
End Function

To call the function enter =Correl_UDF(ROW()) in the worksheet cell.

For some reason I was unable to reference the named ranges in your workbook. Therefore I recreated the range inRng in the function. Perhaps you can get the original to work. That would be better - in a way. In the sheet, row numbers are entered as range row numbers instead of sheet row numbers. That makes things rather convoluted now that you don't need the ranges anymore. For all the single column ranges I just declared constants and then adjusted the row numbers. Observe the functionality of the variable Rr in this regard.

Discuss

Discussion

Thank you very much, the above function works very well.
aabousetta Jul 23, '18 at 8:10 am
Hello Abousetta,
I'm glad the function works for you. It was a tough nut for me to crack, made me learn a lot of things. Please award points for my effort. You do so by marking the answer as "Selected". It helps other visitors too, as they would disregard answers not so marked.
Regards
Variatus (rep: 2603) Jul 23, '18 at 8:20 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login