Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

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:

=left(a2,find(" ",a2)-1)

Open in new window


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

Open in new window


If I type the function into a cell on my worksheet as
=GetFirstWord(A2," ")

Open in new window

, 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.
0
Shannon Mollenhauer
Asked:
Shannon Mollenhauer
  • 3
  • 2
1 Solution
 
Rgonzo1971Commented:
HI,

the problem was thje Instr was not correctly used (start  was missing)

Function GetFirstWord(Source As String, WordBreak As String) As String
    GetFirstWord = Left(Source, InStr(1, Source, WordBreak) - 1)
End Function

Open in new window

Regards
0
 
Shannon MollenhauerAuthor Commented:
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?
0
 
Ken ButtersCommented:
The first argument "Start" is optional.

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

Open in new window


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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Shannon MollenhauerAuthor Commented:
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:

=GetFirstWord(A2," ")

Function GetFirstWord(Source, WordBreak)
    GetFirstWord = Left(Source, InStr(1, Source, WordBreak) - 1)
End Function

Open in new window


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?
0
 
Ken ButtersCommented:
Seems to work ok for me with a blank...

I did make one more tweak... specified input parameters as strings and specified return value as string.

Have attached workbook where it seems to work...

Please review attachment and see if that works for you as well.
Book21.xlsm
0
 
Shannon MollenhauerAuthor Commented:
Sorry for the slow reply. This looks like a winner. Thank you.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now