Make a function out of a part of VBA Script

[blue headphones]

Hi there,

In one of my macros, there a part which I need several times. Until now, this part is copy pasted in the code everytime it is needed. This is not very elegant. Here the code we talk about, it's about looking up specific words in an Excel sheet and spitting out in which column they are:

' Variables
Dim Words As Variant
Dim WordsRange As Excel.Range
Dim i As Integer

' define search terms
Words = Array("Input 1", "Input 2", "Input 3")

' Look for each input
For i = LBound(Words) To UBound(Words)
    Dim WordsRange As Excel.Range
    Set WordsRange = xlWS.Cells.Find(Words(i), LookIn:=xlValues, LookAt:=xlWhole)

    If Not WordsRange Is Nothing Then
        WordsColumn = ConvertToLetter(WordsRange.Cells.Column)
        Exit For
    End If
Next i

(note: "ConvertToLetter" is another function, converting the number of the column into the corresponding letter - column 1 = A and so on. This function works fine)
I tried like this, basically wrapping the whole block around a Function:

Function SearchWords(Words As Variant) As String

'define search terms
Dim WordsRange As Excel.Range
Dim WordsColumn As Integer
Dim i As Integer

' Look for each input
For i = LBound(Words) To UBound(Words)
    Set WordsRange = xlWS.Cells.Find(Words(i), LookIn:=xlValues, LookAt:=xlWhole)
    If Not WordsRange Is Nothing Then
        WordsColumn = ConvertToLetter(WordsRange.Cells.Column)
        Exit For
    End If
Next i

End Function

But this gives an error (424) on the line

Set WordsRange = xlWS.Cells.Find(Words(s), LookIn:=xlValues, LookAt:=xlWhole)

Any idea how to solve this?
Thanks a lot

[zbor]

Sheet name xlWS is not defined under Function.
You need either to declare it globally or within a function

[romperstomper]

Or pass it as an argument:

Function SearchWords(Words As Variant, xlWS as Worksheet) As String

'define search terms
Dim WordsRange As Excel.Range
Dim i As Integer

' Look for each input
For i = LBound(Words) To UBound(Words)
    Set WordsRange = xlWS.Cells.Find(Words(i), LookIn:=xlValues, LookAt:=xlWhole)
    If Not WordsRange Is Nothing Then
        SearchWords = ConvertToLetter(WordsRange.Cells.Column)
        Exit For
    End If
Next i

End Function

[PrizeGotti]

I feel like everyone is misinterpreting the use of the word function in the title. Seems to me like he wants to add his macro to his personal.xlsb file so he can user this macro on multiple sheets without having to copy and paste it every time.

Also doesn't a function require something to return?

[romperstomper]

I'm not really sure how you get that interpretation from "In one of my macros, there a part which I need several times. Until now, this part is copy pasted in the code everytime it is needed". But I'm sure the OP will clarify.

[Sintek]

Something tells me there is more to meets the eye...
Perhaps @blue headphones can give a bit more information about the actual use of this...I am sure there is a simplification...
Why the need for column letter and not just column number...Why the need to use this scenario multiple times, why exiting when 1 is found...
And why a function for column letter...

WordsColumn = Split(Cells(1, WordsRange.Column).Address, "$")(1)

[blue headphones]

And why a function for column letter...

WordsColumn = Split(Cells(1, WordsRange.Column).Address, "$")(1)

Because I'm new to VBA and I think I make things more complicated than they are. I tried to have something that works for all columns, also after column Z :D I'll try out your suggestion as soon as I can, thank you!

Column letter is needed because I need to read out the values in that column

Dim lastRow As Long
lastRow = xlWS.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastRow ' Loop over the column
        
    strWord1 = xlWS.Range(ColumnWord1 & i).Value ' Value in the Word 1 column
    strWord2 = xlWS.Range(ColumnWord2 & i).Value ' Value in the Word 2 column
    strWord3 = xlWS.Range(ColumnWord3 & i).Value ' Value in the Word 3 column

[blue headphones]

Thank you for the answers!

PrizeGotti: No, I actually only want to make my macro shorter by replacing a code block appearing several times by a function. As the macro is working correctly now (without the function), it's more about learning how to make it more efficient. You hint that a function needs something to return might show that I'm just trying to use the wrong concept for what I want to achieve.

romperstomper, zbor: that's right, I missed that xlWS was not defined. However that's not a solution, because the worksheet needs the workbook, which in turn need the application, which needs the path. I thought passing xlWS as argument would make the trick, but there is still an error, though the number changed (error 91).
Currently it looks like this:

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim Path As Variant
Dim fd As Office.FileDialog

Dim objUndo As UndoRecord
Set objUndo = Application.UndoRecord



'Choose an Excel file. The path to the file is saved for later use.
Set xlApp = New Excel.Application
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .Title = "Choose an Excel file"
    .InitialFileName = "\\path\to\the\initial\folder\"
    .Filters.Clear
    .Filters.Add "Excel files", "*.xl*"
    .AllowMultiSelect = False
    .ButtonName = "OK"
    If .Show <> -1 Then
        MsgBox "No file chosen"
        Exit Sub
    End If

Path = .SelectedItems(1)
Set xlWB = xlApp.Workbooks.Open(Path)
Set xlWS = xlWB.Sheets(1)

Then the macro looks for specific words in the selected Excelfile, as posted at the beginning of the thread. As there are several words to look for, this part of code is pasted around 10 times in the macro. After this process, a box is displayed, indicating in which column the words are located:

Dim Agree As VbMsgBoxResult
Agree = vbNo
Do While Agree = vbNo

Dim msg As String
    msg = "the following words were found" & vbNewLine & _
        "Word 1: " & ColumnWord1 & vbNewLine & _
        "Word 2: " & ColumnWord2 & vbNewLine & _
        "Word 3: " & ColumnWord3 & vbNewLine & _
        "Do you agree?"
    Agree = MsgBox(msg, vbQuestion + vbYesNoCancel, "Words and columns")

If Agree = vbNo Then
        Dim WordsGroup As String
        WordsGroup = InputBox("Please select the word that is not correctly assigned" & vbNewLine & _
                              "Word 1, Word 2, Word 3")

        ' Correction of column assignment
        Select Case WordsGroup
            Case "Word 1"
                ColumnWord1 = InputBox("Please type the column for Word 1")
            Case "Word 2"
                ColumnWord2 = InputBox("Please type the column for Word 2")
            Case "Word 3"
                ColumnWord3 = InputBox("Please type the column for Word 3")
            End Select
        
    ElseIf Agree = vbCancel Then
        ' Clean up
        Set objMail = Nothing
        Set objOutlook = Nothing
        ' Close the Excel file
        xlWB.Close SaveChanges:=False
        xlApp.Quit
        Set xlWS = Nothing
        Set xlWB = Nothing
        Set xlApp = Nothing
        Set Path = Nothing
        Exit Sub
        
    Else ' User agrees with column assignement
        Agree = False
    End If
Loop

I have to say I have a bit of experience in other programing languages (Python), but VBA is very new to me. It's a process of googling, asking, copy-pasting, learning by doing I think the variable names are not 100% consistent in the example (shortened for the forum), but I hope it's still understandable.

[romperstomper]

I thought passing xlWS as argument would make the trick, but there is still an error, though the number changed (error 91).

Then you did it wrong. You haven't showed us how you are actually using the function, so it's hard to comment on what you did.