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:

Please Login or Register  to view this content.

(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:

Please Login or Register  to view this content.

But this gives an error (424) on the line

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

[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...

Please Login or Register  to view this content.

[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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.

[blue headphones]

And why a function for column letter...

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.