Solved

Beginner Excel custom function help - passing arguments

Posted on 2014-02-13
6
229 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 48

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now