Solved

Beginner Excel custom function help - passing arguments

Posted on 2014-02-13
6
235 Views
Last Modified: 2014-02-26
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
Comment
Question by:Shannon Mollenhauer
  • 3
  • 2
6 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 39856846
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
 

Author Comment

by:Shannon Mollenhauer
ID: 39856908
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
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39856984
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Shannon Mollenhauer
ID: 39857140
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
 
LVL 19

Accepted Solution

by:
Ken Butters earned 250 total points
ID: 39857230
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
 

Author Closing Comment

by:Shannon Mollenhauer
ID: 39890335
Sorry for the slow reply. This looks like a winner. Thank you.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question