Solved

Beginner Excel custom function help - passing arguments

Posted on 2014-02-13
6
234 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

839 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