How to add function or method in .xlam file

[AmitSingh]

Hi All,

Need to know how to add function or method in .xlam file and call that function from any excel file. Below is code which i am using. I have also attached the output file and .xlam.
I used below 2 function or method in xlam file but not able to call this. Please create the .xlam file as website not allowing me to attache this format.

I need below result in column Y with a combination of column W and A. Result X:802422055:600124663

Method 1:-

Function ReportingStructure(UIN As String, UINRng As Range, LMUINRng As Range) As String
Dim c As Variant
Dim LMUIN As String
Set c = UINRng.Find(What:=UIN, LookIn:=xlValues, LookAt:=xlWhole)
If c Is Nothing Then
    ReportingStructure = "X:" & UIN
Else
    LMUIN = Application.Intersect(c.EntireRow, LMUINRng).Value
    ReportingStructure = ReportingStructure(LMUIN, UINRng, LMUINRng) & ":" & UIN
End If
End Function

Method 2:-

Sub ReportingStructure()
    With Application
        cm = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    With ActiveSheet
        y = 2
        Do
            .Cells(y, 25) = "x:" & .Cells(y, 23) & ":" & .Cells(y, 1)
            y = y + 1
        Loop Until .Cells(y, 1) = Empty
        .Range("Y1").Activate
    End With
    With Application
        .Calculation = cm
        .ScreenUpdating = True
    End With

End Sub

Kindly help me as it is little urgent. Thanks in advance

Thank you!!

Warm Regards,
Amit Singh

--------------------------------------------------------------------------------------------
MOD EDIT: Added code tag

 

[Chihiro]

Please use code tag when posting codes (<> icon in the menu).

 

[AmitSingh]

Please use code tag when posting codes (<> icon in the menu).

sure, next time i will keep in mind while posting any post. Can you help me on this.

 

[Chihiro]

Assume that we know nothing of your data. You need to give example of how your UDF is used in the worksheet (with arguments filled).

 

[AmitSingh]

Assume that we know nothing of your data. You need to give example of how your UDF is used in the worksheet (with arguments filled).

Sure, It takes three parameter, UIN of column A, UINRng range of column A and LMUINRng range of column W.

If we call this function in excel of column Y like below
=ReportingStructure([@UIN],A:A,W:W)
It display result X:802422055:600124663 with combination of column W of Line Manager and column A of UIN.

Let me know if have any query.

Thank you!!

Warm Regards,
Amit Singh

 

[Chihiro]

Try adding Public in front of Function.

I saved your file as xlam and tested. Worked fine on my end.