CODEWITHSUNDEEP
excelvba
user7000656
user7000656

Reputation:

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:

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

Upvotes: 4

Views: 7320

Answers (1)

QHarr
QHarr

Reputation: 84465

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

Upvotes: 4

Related Questions