Re: [ExcelVBA] my first try with a function

 

Dear Pam

A better explanation than I can do is here :- http://www.cpearson.com/excel/writingfunctionsinvba.aspx

As I said earlier :-

First you need something within the brackets that the function is evaluating. 

So :- Function DeptName(vDeptName As Variant) As String,  not Function deptName() As String

Then you need to give the function a return value.

So :-  DeptName = sReturnValue

Your function errored because there was no return value.

David Smart made a very good observation about Option Explicit. You should always have this as the first line of any module and you can force VBA to do this automatically in Tools, Options, Editor, then tick Require Variable Declaration. 

NEVER EVER work without Option Explicit..

David's other comment :-

"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."

is incorrect in this specific context, i.e. when using Excel User Defined Functions, but nevertheless very good programming advice and you should take note of this early in your coding career, and this is not trivial advice as this issue often causes serious headaches even to experienced programmers..

VBA defaults to ByRef in function declarations which means that if you alter a variable within a function it will be passed back to the caller in its changed state with often disastrous consequences. 

Although the keyword ByVal may be used in place of ByRef to prevent the modified value being passed back, this is not a good idea because ByVal causes VB to make a complete copy of the parameter in memory, while ByRef merely passes a few bytes indicating the position of the parameter in memory, and this copying has serious performance issues when you are passing arrays, collections and other Windows objects to functions. 

The best practice therefore is never to modify a sent parameter unless you deliberately want the parameter to be returned to the caller in its modified state. 

Regards

Derek Turner

England +++

---

From: Pam <pstryk24@gmail.com>
To: ExcelVBA@yahoogroups.com
Sent: Thursday, 3 October 2013, 16:15
Subject: RE: [ExcelVBA] my first try with a function

 



Thank you all, David Smart, David Grugeon, and Derek Turner!

 

I want to learn and understand what I am doing so I tried each of your suggestions. The only one that I could get to work is Derek's.

 

Derek, your solution is so similar to what I had, with the exception of the variable names, that I am having trouble understanding what mine lacked. Can you help me understand?

 

Pam

 

-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]On Behalf Of Derek Turner
Sent: Wednesday, October 02, 2013 5:30 PM
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] my first try with a function

Dear Pam

First you need something within the brackets that the function is evaluating. 

Then you need to give the function a return value.

So :-

Function DeptName(vDeptName As Variant) As String

Dim sReturnValue As String

    Select Case vDeptName

        Case 1: sReturnValue = "beads"

        Case 2: sReturnValue = "belt"

        ' Case etc

        Case Else: sReturnValue = "not found"

    End Select

    DeptName = sReturnValue

End Function

Let me know how you get on with this.

Regards

Derek Turner

England

+++

---

From: Pam <pstryk24@gmail.com>
To: ExcelVBA@yahoogroups.com
Sent: Wednesday, 2 October 2013, 22:40
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!

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (80)

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

.

__,_._,___