?
Solved

Excel 2010 VBA - difficulty creating a custom function

Posted on 2012-08-20
12
Medium Priority
?
339 Views
Last Modified: 2012-08-20
I'm learning how to create custom functions and have created one successfully and the second one is not working.  ... can someone give me the syntax fix for what I'm trying to do? ... I'm trying to get used to the syntax/grammar of VBA and creating functions.  Not a real enterprise application ... yet.  ¿

This is working perfectly:
Function discount_amount(quantity, price)

    discount_amount = quantity * price

End Function

This is not working:

Function addup_numbers(firstnum, lastnum)

    addup_numbers = sum(firstnum : lastnum)    

End Function
0
Comment
Question by:brothertruffle880
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38312924
Function addup_numbers(firstnum, lastnum)

    addup_numbers = firstnum + lastnum  

End Function

Note the + rather than the :
0
 
LVL 39

Expert Comment

by:nutsch
ID: 38312931
or just

Function addup_numbers(firstnum, lastnum)

    addup_numbers = firstnum + lastnum

End Function
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38312950
Basically in user defined functions you usually (always?) need to use VBA code rather than sheet formulas.
0
Independent Software Vendors: 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!

 
LVL 24

Expert Comment

by:Steve
ID: 38313000
OK, UDF, my favorite topic, love 'em.

Start by declaring variables in the function

E.G:

Function addup_numbers(firstnum as double, lastnum as double) as double

    addup_numbers = firstnum + lastnum

End Function 

Open in new window


I am guessing that you are trying to sum all cells between a range not just the first and last cell? or?
0
 
LVL 24

Expert Comment

by:Steve
ID: 38313010
As you have used the colon ':' I am guessing you are trying to do a range:

Function addup_numbers(allnums as range) as double
 
for each c in allnums
if isnumeric(c) then
  addup_numbers = addup_numbers + c.value
end if
next
End Function

Open in new window

0
 
LVL 24

Expert Comment

by:Steve
ID: 38313034
NOTE:
if you want to add up numbers between two cells such as StartCell and EndCell and then use VBA to call the remaining cells... I wouldn't...
Try to avoid a function which does not include the range or cells involved in the formula in brackets, as if you change the cells that are referenced but not in the brackets then the result of the formula will not calculate automatically.
0
 

Author Comment

by:brothertruffle880
ID: 38313449
Barman:
Thank you.
What I'm trying to understand is the VBA syntax used in implementing the sum function within my user-created function.  
I'm trying to add the following numbers:
The range a1 up to and including a10.  
Just a simple sum function to add up a contiguous range.  
My purpose in posting the question was I wanted to know how come it rejected my use of the colon!  I know that there may be faster and better ways of getting this job done but I want to make my solution work.  I want to understand the syntax of my use of the SUM function within a UDF.  This is purely a learning exercise.
I do not merely want to add firstnum  and lastnum together.  I should have more accurately stated my arguments as first_num_in_range and last_num_in_range
0
 
LVL 24

Expert Comment

by:Steve
ID: 38313497
As I have stated in earlier posts, it is not good practice to do this as the function result will not calculate automatically if any of the values between first and last number in the range change.
However to do what you wish:

Function addup_numbers(first_num_in_range As Range, last_num_in_range As Range) As Double
Dim allnums As Range

Set allnums = Range(first_num_in_range, last_num_in_range)
Debug.Print allnums.Address

For Each c In allnums
If IsNumeric(c) Then
  addup_numbers = addup_numbers + c.Value
End If
Next

End Function

Open in new window

0
 

Author Comment

by:brothertruffle880
ID: 38313572
Thanks Barman.
Very useful solution.  It works perfectly.
Question:  How can I make the sum function calculate a range in a UDF
Where do I put quotes or ampersands, etc.  
That's all I want.
I know this is a sucky solution to a simple problem but I'm just trying to understand syntax of VBA i.e. how and where to place quote marks. ampersands, etc.

Function addup_numbers(first_in_range,  last_in_range)

    addup_numbers = sum(first_in_range : last_in_range)    

End Function
0
 
LVL 24

Accepted Solution

by:
Steve earned 2000 total points
ID: 38313684
To use Sum you need to call the WorkSheetFunction

Function addup_numbers(first_num_in_range As Range, last_num_in_range As Range) As Double

addup_numbers = WorksheetFunction.Sum(Range(first_num_in_range, last_num_in_range))

End Function

Open in new window

0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38313686
You don't.

Sum is a worksheet function.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38313717
Function addup_numbers(first_num_in_range As Range, last_num_in_range As Range) As Double

addup_numbers = WorksheetFunction.Sum(Range(first_num_in_range, last_num_in_range))

End Function
Nice.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

807 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