?
Solved

Beginner Excel custom function help - passing arguments

Posted on 2014-02-13
6
Medium Priority
?
241 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 52

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
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!

 

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 1000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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 …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

764 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