Can VBA function be used to return value

[Andrew]

Hello,
Can a VBA function be used to return a value, such as in C? I would
like a function to do the following:

output_value = function_name(input_value)

Can someone please send me a brief example of this?

thanks

 

[Jacob Skaria]

Copy the below function to VBA module and try the formula from a cell

A1 = "asdf"
B1 = ReverseString(A1)

Function ReverseString(varTemp) As String
ReverseString = StrReverse(varTemp)
End Function

 

[Andy]

Copy the below function to VBA module and try the formula from a cell

A1 = "asdf"
B1 = ReverseString(A1)

Function ReverseString(varTemp) As String
ReverseString = StrReverse(varTemp)
End Function

This isn't exactly what I meant, although it will work. Here's an
example which isn't really code, but it describes how this function
should work.

Sub function1()
dim k as integer
dim q as integer
cells(1,1)=k
q=function2(k) ' let variable q contain the return value of function2
End sub

Sub function2(ByVal n as integer)
n=n+1

return n
End Sub

 

[Rick Rothstein]

Because you Dim'med the 'q' variable inside function1, it will not be able
to be seen outside of the procedure. The way to return a value from a
function in VB is to assign the value to the function name within the
function itself; although, for simple functions, you can treat the function
name as if it were a declared variable (and it is also a good idea to
specify the return type in the function's procedure header). You are
probably looking for something like this...

Sub Function1() As Long
Dim k As Long
k = Cells(1,1).Value
Function1 = Function2(k)
End sub

Sub Function2(ByVal n as Long) As Long
Function2 = Function2 + 1
End Sub

--
Rick (MVP - Excel)

Copy the below function to VBA module and try the formula from a cell

A1 = "asdf"
B1 = ReverseString(A1)

Function ReverseString(varTemp) As String
ReverseString = StrReverse(varTemp)
End Function

This isn't exactly what I meant, although it will work. Here's an
example which isn't really code, but it describes how this function
should work.

Sub function1()
dim k as integer
dim q as integer
cells(1,1)=k
q=function2(k) ' let variable q contain the return value of function2
End sub

Sub function2(ByVal n as integer)
n=n+1

return n
End Sub