Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I am attempting to Construct an array (testSheets) that holds a file name and a file path as strings. I then wish to contain this array inside of another array (shtsTmpl). Then, I want to pass the strings within the nested array into a function's parameters (copySheets) to simply return the combined file path in a msgbox.

This first bit of code successfully returns "TSTSheets.xlsx" in a msg box.

Sub prepSheets()
 Dim testSheets() As Variant
 ReDim testSheets(0)
 testSheets(0) = "TSTSheets.xlsx"

 Dim shtsTmpl() As Variant
 shtsTmpl = Array(testSheets)

 copySheets (shtsTmpl(0)(0))

End Sub

Function copySheets(srcName As String)
 MsgBox (srcName)
End Function

This second bit of code returns a compile error that says "Expected: =" at the line that calls the copySheets function and I do not understand why.

Sub testSheets()
 Dim testSheets() As Variant
 ReDim testSheets(1)
 testSheets(0) = "TSTSheets.xlsx"
 testSheets(1) = "C:\"

 Dim shtsTmpl() As Variant
 shtsTmpl = Array(testSheets)

 copySheets (shtsTmpl(0)(0),shtsTmpl(0)(1))
End Sub

Function copySheets(srcName As String, srcPath As String)
 MsgBox (srcPath & srcName)
End Function

Can someone please explain to me why the code compiles correctly when the function has one parameter and incorrectly when the function has two parameters?

Please let me know if you need further explanation.

Thank you.

share|improve this question
    
Try adding a Call keyword just before the copySheets call, or remove the outer parentheses around the parameter list. –  Robert Harvey Jan 21 at 22:40
    
Both changes (no parentheses and Call keyword) result in a new compile error: "ByRef argument type mismatch". I will have to investigate this. –  user3091873 Jan 21 at 22:46
    
Is copySheets a Function or a Sub ? If a Function, does it return a value? –  Tim Williams Jan 21 at 22:46
    
copySheets is a Function. It does not return a value. I found the soultion, given Robert Harvey's suggestion. I simply needed to remove the Parentheses and add a ByVal before both of the parameters defined in the function. Though, I can't say I'm sure as to why this works. –  user3091873 Jan 21 at 22:51
    
Strings are probably treated as values in vba, while arrays (or any part of them) are probably treated as references. Nobody ever accused vba of being logically consistent, and this seems to be a quirk of the language, given that a single parameter works OK. –  Robert Harvey Jan 21 at 22:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.