VBA pass variables between Sub and Public Function
I'm new to VBA and am trying to pass a string from a Sub to a Public Function (the Sub and Public Function are in separate modules but the same Workbook), split the string into an array in the Public Function, then pass the array back to the Sub from the Public Function.
I've searched though Stack Overflow and tried several different methods but they haven't worked. Below is the code I currently have which produces the following error:
Run-time error '9': Subscript out of range
Any help would be appreciated. Apologies for the basic question. Thank you.
Sub export() Dim testString As String Dim testValue As Variant 'testString could have any number of values testString = "TEST1, TEST2, TEST3, TEST4" 'Call the Public Function below testValue = splitText(testValue) End Sub
Which calls the following Public Function in another module:
Public Function splitText() As Variant Dim testValue As Variant 'Trying to import testString from the Sub to split it testValue = Split(testString, ",") 'Define result of the Public Function splitText = testValue End Function
1 Answers VBA pass variables between Sub and Public Function
You need to use consistent variable names and pass argument in function call
Public Sub export() Dim testString As String Dim testValue As Variant testString = "TEST1, TEST2, TEST3, TEST4" testValue = splitText(testString) '<== consistent naming and passed as argument End Sub Public Function splitText(ByVal testString As String) As Variant '<== argument referenced in function signature Dim testValue As Variant testValue = Split(testString, ",") '<== consistent naming splitText = testValue End Function