Set VBA Variable to Result from Function which is String

Question

I've read a bunch about functions today and they all seem to deal with math/numbers. I'm trying to use a function which returns a string and capture that as a variable in the "main sub" but I can't get it to work. Can anybody point out what I'm doing wrong?

Eg:

Function:

Public Function Test(var) As Variant
 Bar = var & "World"
 MsgBox Bar
End Function

Sub:

Public Bar As Variant
Public Z As Variant
Sub testing()
 Call Test("Hello") ' This displays "HelloWorld" from MsgBox in Function 
 Test ("Hello")     ' This displays "HelloWorld" from MsgBox in Function 
 Z = Test("Hello")  ' This displays "HelloWorld" from MsgBox in Function 
 MsgBox Z           ' This displays an empty MsgBox :*(
End Sub

Tim Stack · Accepted Answer · 2019-04-09 07:15:27Z

4

If you want the function to return a value, populate the function variable and return that in the main sub, like so

Public Function Test(var As String) As String
    Test = var & " World"
End Function

Sub testing()
    returnstr  = Test("Hello")
    MsgBox returnstr
End Sub

edited Apr 9, 2019 at 7:15

answered Apr 9, 2019 at 6:00

Tim Stack

3,2483 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael · Accepted Answer · 2019-04-09 10:23:08Z

3

You aren't returning a value from the function. Also, functions should only be used to return values, not to perform actions that change things (other than variables) or display pop-ups. It also gets confusing having global variables and passing variables to a function. You generally pass local variables to a function. Here's a cleaner example (with your main function first, as is normal convention):

Sub Testing()
    Dim Z As String
    Z = Test("Hello")
    MsgBox Z
    MsgBox Test("Hello")
End Sub

Public Function Test(ByRef var As String) As String
    Test = var & "World"
End Function

edited Apr 9, 2019 at 10:23

answered Apr 9, 2019 at 5:59

Michael

4,9032 gold badges13 silver badges28 bronze badges

8 Comments

FreeSoftwareServers Over a year ago

I used the MsgBox for debug only, but I'm beginning to see my logic error thanks!

Michael Over a year ago

You're welcome. If an answer has answered your question sufficiently, please mark it as correct using the tick to the left. Otherwise you can leave a comment if you want more detail.

FreeSoftwareServers Over a year ago

@TimStack, I accept an answer on 99% of my questions just not at work till tomorrow to test and verify. I'm leaning towards Error because he used a global variable but you code was also easy to read and helped me understand my issue. Thank you all rarely do I have such a hard time deciding between good answers!

Michael Over a year ago

Error used a function to modify a global variable, still without returning a value from the function. That's not how functions are supposed to be used. Functions are for returning values. If you only want to modify a global variable, then just use a Sub.

Michael Over a year ago

Functions absolutely can modify variables, both variables passed by reference and global variables, but if it isn't returning a value, then you just use a Sub.

|

Collectives™ on Stack Overflow

Set VBA Variable to Result from Function which is String

2 Answers 2

Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related