extend a UDF to work in different workbooks

[Xalova]

Okay. So i have copy and pasted a UDF that lets me find the value from the selected cell in a table located in another worksheet. Now what i need is for it to work in a different workbook too.

here is the UDF:

Function FindValueInTable(MyWorksheetName As String, MyValue As Variant, Optional MyTableIndex As Long = 1) As String
    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-find/
    
    'This UDF:
        '(1) Accepts 3 arguments: MyWorksheetName, MyValue and MyTableIndex
        '(2) Finds a value passed as argument (MyValue) in an Excel Table stored in a worksheet whose name is passed as argument (MyWorksheetName). The index number of the Excel Table is either:
            '(1) Passed as an argument (MyTableIndex); or
            '(2) Assumed to be 1 (if MyTableIndex is omitted)
        '(3) Returns the address (as an A1-style relative reference) of the first cell in the Excel Table (stored in the MyWorksheetName worksheet and whose index is MyTableIndex) where the value (MyValue) is found
    
    With ThisWorkbook.Worksheets(MyWorksheetName).ListObjects(MyTableIndex).DataBodyRange
        FindValueInTable = .Find(What:=MyValue, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    End With
    
End Function

and here is the macro that i use the UDF in

Sub MyFind()

    Dim rng As String
    On Error GoTo Errorhandler
        If ActiveCell.Value = "" Then
       
            Exit Sub
           
        Else
   
   
'   Look up value from active cell and get cell address it is located in
    rng = FindValueInTable("Datenbank", ActiveCell.Value, 1)
   
'   Select cell on "G-Nummern" sheet
    Sheets("Datenbank").Activate
    Range(rng).Select
   
Errorhandler:
    Exit Sub
   
    End If
End Sub

like i said. I need the macro to work in the workbook "Verbracuhsmaterialien Datenbank.xlsm" but i do not know how i add that information there, ias i have little to no experience in UDF's

thanks in advance

 

[maabadi]

If you add this macro (or save them) at Personal Macro workbook. You can use Both.
if you don't see Personal macro workbook at Top-Left Section of VBA window, Back to Excel
1. At excel window , Go to Developer Tab & Select Record Macro
2. At the Store macro in Section, Select Personal Macro Workbook & press OK
3. Then At The Developer Tab Press Stop Recording.
4. Now go to VBA window and you should see Personal Macro Workbook Section.
5. if your file that have UDF Not Open, Open it.
6. Drag UDF & Macro from Excel file to Personal macro Workbook at Top-Left Section of VBA Window.
7. Now you can use UDF at all Excel files at your PC.

 

[Xalova]

If you add this macro (or save them) at Personal Macro workbook. You can use Both.
if you don't see Personal macro workbook at Top-Left Section of VBA window, Back to Excel
1. At excel window , Go to Developer Tab & Select Record Macro
2. At the Store macro in Section, Select Personal Macro Workbook & press OK
3. Then At The Developer Tab Press Stop Recording.
4. Now go to VBA window and you should see Personal Macro Workbook Section.
5. if your file that have UDF Not Open, Open it.
6. Drag UDF & Macro from Excel file to Personal macro Workbook at Top-Left Section of VBA Window.
7. Now you can use UDF at all Excel files at your PC.

sadly thats not what i meant. It might be that it could not be understood right, that i said.

What i mean is that this UDF currently only searches a table in the same workbook. But i need it to search for that value in a table in a different workbook.

Is that understandable?

 

[maabadi]

Then I think this post very good for you

application inputbox - selecting a different workbook/sheet​

OR

Inputbox in [excel] [vba] to select range from a different workbook​