• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

Excel 2010 VBA - difficulty creating a custom function

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
brothertruffle880
Asked:
brothertruffle880
  • 5
  • 4
  • 2
  • +1
1 Solution
 
Martin LissOlder than dirtCommented:
Function addup_numbers(firstnum, lastnum)

    addup_numbers = firstnum + lastnum  

End Function

Note the + rather than the :
0
 
nutschCommented:
or just

Function addup_numbers(firstnum, lastnum)

    addup_numbers = firstnum + lastnum

End Function
0
 
Martin LissOlder than dirtCommented:
Basically in user defined functions you usually (always?) need to use VBA code rather than sheet formulas.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
SteveCommented:
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
 
SteveCommented:
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
 
SteveCommented:
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
 
brothertruffle880Author Commented:
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
 
SteveCommented:
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
 
brothertruffle880Author Commented:
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
 
SteveCommented:
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
 
Martin LissOlder than dirtCommented:
You don't.

Sum is a worksheet function.
0
 
Martin LissOlder than dirtCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now