5

Trying to understand how to pass a value back from a function to a sub and display the result in a messagebox, but I can't get it. What am I missing Learning code below:-

Sub mySend()

Dim myNum As Integer

myNum = InputBox("Enter number")

myReturn (myNum)

msgbox myReturn 'would like to display result in messagebox here but no joy

End Sub
'--------------------------------------
Function myReturn(ByRef myNum As Integer)

Dim myCalc As Integer

myCalc = myNum + 10

myReturn = myCalc

End Function
Improve this question
1
  • Thank you so much for your assistance. I'm a newby to this. So what should the final code look like so I can see what goes where and where I'm coming unstuck please? Thanks again. Commented Jun 9, 2017 at 10:28

3 Answers 3

Reset to default
4

Don't use a ByRef parameter - just use a function properly.

Sub mySend()

Dim myNum As Integer

myNum = InputBox("Enter number")

myNum = myReturn(myNum) '// You need to assign (=) the value to the variable

msgbox myNum

End Sub
'--------------------------------------
Function myReturn(number As Integer) As Integer '// Note the return type after the ()

Dim myCalc As Integer

myCalc = number + 10

myReturn = myCalc

End Function

If you want to pass a variable by reference in your example, then you need to actually change the value of that same variable otherwise when you reference it again in the calling code that value will not have changed:

(This is your code amended to show the result of ByRef when used properly, I don't recommend actually using this code)

Sub mySend()

Dim myNum As Integer

myNum = InputBox("Enter number")

myReturn myNum '// No need for parentheses here

msgbox myNum

End Sub
'--------------------------------------
Function myReturn(ByRef myNum As Integer)

myNum = myNum + 10

End Function
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

You don't need to change the value of a parameter passed ByRef, but you can change it. And, in your second example, your myReturn function is passing back a Variant/Empty, which is not a good idea.
My second example is just in case the OP was trying to understand why ByRef wasn't working in their example. I'd never advocate using ByRef in VBA unless it's absolutely necessary. I literally just changed their code to help them understand why it wasn't working as-is
But it's not a good idea demonstrating using a Function that returns Empty. Encourage them to use a Sub instead if you aren't returning a proper value.
I'm not encouraging that at all - my first example shows "best practice", the second example is just the OP's code amended to show how ByRef works (note in OPs code there is no return type on the function). I've updated the wording before that snippet to clarify
2

Assign the result of the function to a variable and show the result in the messagebox:

Dim result as Integer 
result = myReturn(myNum)
Improve this answer

1 Comment

Fantastic!!! I've got it and I now understand the thing. Thank you so very much and keep well.
0

As Peter said, you need to save the result of your myReturn(myNum) call. And then show that variable:

result = myReturn(myNum)
MsgBox result

Actually you don't even need the "Dim result". Why? I don't know, but I tested it and it worked.

Improve this answer

3 Comments

With Dim result as Integer you can specify the integer data type. Else in VBA it would be a Variant data type...
Unless you use Option Explicit (which you should do!) you don't need to declare variable types in VBA - they will default to a Variant type. So the OP's function is returning a Variant/Integer because they left off the As Integer in the function declaration. And your result will become a Variant/Integer. (P.S. You can reduce your code to MsgBox myReturn(myNum) if you want to.)
related: stackoverflow.com/questions/35435368/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.