Shannon Mollenhauer
asked on
Beginner Excel custom function help - passing arguments
I find myself writing some of the same text manipulation formulas all the time, so it seems like a good thing to turn into a function. My first example is taking this formula and "functionizing" it:
This would take everything up to the first space and place it in the cell. My VBA function looks like this:
If I type the function into a cell on my worksheet as
=left(a2,find(" ",a2)-1)
This would take everything up to the first space and place it in the cell. My VBA function looks like this:
Function GetFirstWord(Source, WordBreak)
GetFirstWord = Left(Source, InStr(WordBreak, Source) - 1)
End Function
If I type the function into a cell on my worksheet as
=GetFirstWord(A2," ")
, how do I get the argument WordBreak value " " to be interpreted correctly in the InStr function. I just don't know enough about delimiters, punctuation, string formatting, etc. in VBA yet. Should be an easy one for you Experts.
ASKER
That didn't solve it, but I now get a different error. "A value used in the formula is of the wrong data type." Do I need to declare or dim something? Is there a problem with my function including the string " " in the arguments? Does VBA know how to keep the quotation marks in the function?
The first argument "Start" is optional.
In the instr you have your source / wordbreak swapped.
Try this:
In effect you were looking for the first occurence of the larger string from the cell in the string " "... so it was always returning zero as the first "Position" of that string.
In the instr you have your source / wordbreak swapped.
Try this:
Function GetFirstWord(Source As String, WordBreak As String)
GetFirstWord = Left(Source, InStr(Source, WordBreak) - 1)
End Function
In effect you were looking for the first occurence of the larger string from the cell in the string " "... so it was always returning zero as the first "Position" of that string.
ASKER
Ken:
Good attention to detail. I noticed the source/wordbreak swap when I got the first comment and switched them. Here is the formula in the worksheet and the function in VBA:
I think the error must be occurring due to the use of the space character in the wordbreak because it works if I put a different character in. Any ideas how to make sure the WordBreak argument is passed and evaluated as a text string with quotes around it?
Good attention to detail. I noticed the source/wordbreak swap when I got the first comment and switched them. Here is the formula in the worksheet and the function in VBA:
=GetFirstWord(A2," ")
Function GetFirstWord(Source, WordBreak)
GetFirstWord = Left(Source, InStr(1, Source, WordBreak) - 1)
End Function
I think the error must be occurring due to the use of the space character in the wordbreak because it works if I put a different character in. Any ideas how to make sure the WordBreak argument is passed and evaluated as a text string with quotes around it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for the slow reply. This looks like a winner. Thank you.
the problem was thje Instr was not correctly used (start was missing)
Open in new window
Regards