VBA function returns wrong value

Question

I am trying to assign a value to a variable by calling a function in my VBA code.

My subroutine body looks like this:

Sub testSubroutine()
Dim iVal As Integer
iVal = readSolOverviewTable("TOT_MAPS")
Debug.Print "Function readSolOverviewTable returned: " & iVal
End sub

The readSolOverviewTable function is returning correct value with in the function itself. I have verified it by debugging the function code.

But it seems there is some problem with the subroutine code as the following statement:

iVal = readSolOverviewTable("TOT_MAPS")

Always returns 0.

Am I doing something wrong?

Updated:
====================================

my function looked like this:

Function readSolOverviewTable(searchVal As String) As Integer
    Dim retVal As Integer
    retVal = -99

    ' Do some code and assign value to the variable retVal
    retVal = 100
    Debug.Print "retval is: " & retVal
End Function

As expected; this correctly shows the debug output as 100 However doesn't reflect in the subroutine call.

But changing the code to this made everything all right:

Function readSolOverviewTable(searchVal As String) As Integer
    Dim retVal As Integer
    retVal = -99

    ' Do some code and assign value to the variable retVal
    retVal = 100
    Debug.Print "retval is: " & retVal

    readSolOverviewTable = retVal

End Function

Now that I have resolved the problem (I hope), what is the reason and why this weird kind of syntax?

What do you mean by `with in the function itself`? Can you post `readSolOverviewTable` content? (at least the relevant part) — smagnan, Aug 29 '14 at 07:03
You may want to check [THIS ANSWER](http://stackoverflow.com/questions/2781689/how-to-return-from-a-vba-function) — , Aug 29 '14 at 07:05
@smagnan I just checked some more posts.. And got it to work by adding one more line just before the end function statement. Updated the question with the details. — Ayusman, Aug 29 '14 at 07:05
Thanks @mehow yes, I got the answer. I am new to VBA and coming from java world the VBA syntax kind of throws me off a bit. — Ayusman, Aug 29 '14 at 07:16

score 2 · Accepted Answer · answered Aug 29 '14 at 09:55

The problem is simple - you weren't returning a value. In order to return a value from a Function in VBA, you need to say function name = value at the end of your code (it's like saying "return xxx"). Since you were not returning a value from the function it defaulted to 0.

VBA function returns wrong value

1 Answers1