extend a UDF to work in different workbooks

Xalova

Board Regular
Joined
Feb 11, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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:

VBA Code:
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

VBA Code:
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 :)
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,944
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Feb 11, 2021
Messages
71
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,621
Messages
5,659,922
Members
418,536
Latest member
Tezzies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top