How to write a VBA function which returns an integer

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

Hello,
I could use some help on the syntax of writing a function file in VBA
which takes two integers as input arguments and returns one integer.
Let's say my function was called TEST, and TEST took the average of
two integers.

It seems that the code should look like this:

AVG=TEST(2,8)

But all function files that i have seen in VBA use Call to execute the
function. Can someone help me out with this?

thanks
Andy
 
Hello,
I could use some help on the syntax of writing a function file in VBA
which takes two integers as input arguments and returns one integer.
Let's say my function was called TEST, and TEST took the average of
two integers.

It seems that the code should look like this:

AVG=TEST(2,8)

But all function files that i have seen in VBA use Call to execute the
function.  Can someone help me out with this?

thanks
Andy
Click to expand...

Function mavg(x, y)
mavg = (x + y) / 2
End Function
But why not just use =avg
 
Function mavg(x, y)
 mavg = (x + y) / 2
 End Function
But why not just use =avg
Click to expand...

I'm not trying to calculate the average, I'm trying to get an example
of how to make a function. So, consider this code where one
subroutine calls a function. This won't work. But how do I make it
work?

Sub fill_cells()
for k=1 to 10
cells(k,1)=mavg(k,5)
next
End Sub

Function mavg(x,y)
mavg=(x+y)/2
End Function
 
I'm not trying to calculate the average, I'm trying to get an example
of how to make a function.  So, consider this code where one
subroutine calls a function.  This won't work.  But how do I make it
work?

Sub fill_cells()
for k=1 to 10
cells(k,1)=mavg(k,5)
next
End Sub

Function mavg(x,y)
mavg=(x+y)/2
End Function- Hide quoted text -

- Show quoted text -
Click to expand...

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
 
"If desired, send your file to dguillett  @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
Click to expand...

In many programming languages having a function return a value is a
very fundamental construct. It is apparently not common in VBA,
otherwise it would not require an explanation any more than would an
assignment or a declaration. Thanks for your offer.
 
In many programming languages having a function return a value is a
very fundamental construct.  It is apparently not common in VBA,
otherwise it would not require an explanation any more than would an
assignment or a declaration.   Thanks for your offer.
Click to expand...

I don't think this can be done with one line, but two lines of code
will do it. Here's how this will work. Obviously this is a very
simple function, but this will work for more complicated functions as
well.

' Subroutine to call function
' ===================
Sub fill_cells()
for k=1 to 10 '
Call mavg(k,5) ' enter values of k and 5 to average function
mavg
X=mavg(k,5) ' X is the returned value. X has to be a
global declaration
next
End Sub

' Function to average two numbers
' ========================
Function mavg(x,y)
X = (x+y)/2
End Function
 
Back
Top