Re: [ExcelVBA] my first try with a function

 

What you've posted here is missing a parameter.

You use =deptName(k2) in your cell. I.e. you are passing the cell k2 as a
parameter in your call to deptName.

This means that the definition of the function deptName needs to include a
declaration for this parameter.

From your code, it looks as though the value is supposed to end up in "dn"
and that you want this to be a number. So

> Function deptName(ByVal dn As Integer) As String

Excel will then pass the value of the cell in the function call in the cell
and call it "dn" and you can use it in your case statement.

BUT ... you are then assigning string values to "dn", which will try to
overwrite the number that the Excel put there when the function was called.

I suspect that you actually want to return that string from the function.
If so, then you assign the value to the name of the function, e.g.

> Select Case dn
> Case 1
> deptName = "beads"
> Case 2
> deptName = "belt"

etc.

This should all have been picked up by Excel, during compilation, but it
looks as though you have some of the checking turned off. In a line right
at the top of your module, insert

Option Explicit

which will cause Excel to check your variable/parameter definitions more
rigorously, and especially tell it to find instances when you haven't
declared a variable or parameter.

Regards, Dave S

PS For functions that are called from formulas, Excel will not produce
error messages if something goes wrong. Instead it will terminate the
function and simply return #Value, which isn't particularly helpful.

----- Original Message -----
From: "Pam" <pstryk24@gmail.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Thursday, October 03, 2013 7:40 AM
Subject: [ExcelVBA] my first try with a function

> I've been doing cell formulas in excel for years but this is the first
> time
> I have tried to do a function. Of course, it did not work.
>
> Here is my situation.
>
> Column K, rows 2:100, have a number, mostly between 1 and 25 in it. If the
> cell has anything else in it I want it to be ignored. Perhaps the problem
> is
> in the way I tried to call the function.
>
> The function I tried:
>
> Function deptName() As String
> Select Case dn
> Case 1
> dn = "beads"
> Case 2
> dn = "belt"
> Case 3
> dn = "bolo"
> Case 4
> dn = "clock"
> Case 6
> dn = "concho"
> Case 7
> dn = "cords"
> Case 8
> dn = "dolls"
> Case 9
> dn = "floral"
> Case 10
> dn = "general"
> Case 12
> dn = "holiday"
> Case 14
> dn = "jewelry"
> Case 15
> dn = "kits"
> Case 16
> dn = "light"
> Case 17
> dn = "pattern"
> Case 18
> dn = "miniatures"
> Case 19
> dn = "music"
> Case 20
> dn = "pc"
> Case 21
> dn = "rhinestones"
> Case 22
> dn = "sequin"
> Case 23
> dn = "sewing"
> Case 24
> dn = "chimes"
> Case 25
> dn = "wood"
>
> End Select
>
> End Function
>
>
> In column L, I tried calling it with =deptName(k2)
> I got a #value! error.
>
> I looked around online but didn't find anything that could help me clearly
> understand how to call the function.
>
> Would someone have a moment to steer me straight.
>
> Thanks!
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1432 / Virus Database: 3222/6215 - Release Date: 10/01/13
>

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (76)

Recent Activity:

• New Members 1

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___