Passing ParamArray Not Working

[hheydinger]

Can't get ParamArray to work. Have looked at numerous examples online. But nothing sees to work. When I select Debug (F8) for the procedure TableRequest, I get an alarm(ding), but none of the VBA statements get highlighted, not even the Sub line.
VBA code is below. I would really appreciate a solution that works since I'm "tearing my hair out". Thanks.

  Sub Test()
  ' This macro should work for Excel 2003-2013.
  Dim sTable1 as String
  Dim sTable2 as String
    
Call Sub TableRequest(sTable1, sTable2)
  
Debug.Print “Tables are “ & sTable1 & “, ” sTable2
  
End Sub
  
***************
  
Sub TableRequest(ByRef Y() As String)
  
' Prompt user for all applicable table names.
  
' This macro should work for Excel 2003-2013.
  
Dim sPrompt As String
  sPrompt = "Enter table name #1." 

  
Y(1)  = InputBox(sPrompt, "GET TABLE NAME")
  sPrompt = "Enter table name #2."

  
Y(2) = InputBox(sPrompt, "GET TABLE NAME")
  
End Sub

 

[snb_]

This is how you can use paramarray:

Sub Test()
  TableRequest "Table_1", "Table_2"
End Sub

Sub TableRequest(ParamArray sn())
   MsgBox "The first " & Join(sn, vbLf & "The second ")
End Sub

 

[GTO]

Just another example:


In a Standard Module:

Option Explicit
  
Sub CallAnotherSubOrFunction()
Dim strMsg    As String
Dim bolStatus As Boolean
  
  bolStatus = False
  
  Call CalledSub(bolStatus, strMsg)
  
  If bolStatus Then
    strMsg = "Item List:" & String(2, vbLf) & strMsg
    MsgBox strMsg
  End If
  
  Call CalledSub(bolStatus, strMsg, "Item 1", "Item 2")
  
  If bolStatus Then
    strMsg = "Item List:" & String(2, vbLf) & strMsg
    MsgBox strMsg
  End If
    
End Sub
  
Sub CalledSub(ByRef Status As Boolean, ByRef MsgString As String, ParamArray OptArgs())
Dim n As Long
  
  If LBound(OptArgs) <= UBound(OptArgs) Then
    
    Status = True
    
    MsgString = vbNullString
    
    For n = 0 To UBound(OptArgs)
      MsgString = MsgString & OptArgs(n) & vbLf
    Next
    
    MsgString = Left$(MsgString, Len(MsgString) - 1)
    
  End If
  
End Sub

Do those help at all?

Mark

 

[hheydinger]

Mark -- thanks much for the quick response. That helped. Now I'm having trouble with syntax to pass an array of 10 strings from called routine back to calling routine w/o having to list them out in the Call and Sub statements. Proper dimensioning (10, not just 1) seems to be an issue.

 

[GTO]

Hi hheydinger,

Sorry for the slow response, I am afraid I've been busy and finding out some rather "unusual" expectations of me elsewhere. Anyways, if I am understanding your question, try stepping through this example with the Locals window open to see the values change and the array get passed back.

Option Explicit
    
    
Sub CallingSubOrFunction()
Dim arr_strFruitCombos(1 To 3)  As String
Dim strMsgText                  As String
Dim n                           As Long
  
  
  arr_strFruitCombos(1) = "Oranges and "
  arr_strFruitCombos(2) = "Bananas and "
  arr_strFruitCombos(3) = "Pears and "
  
  
  strMsgText = "I have these fruit combos" & vbLf & vbLf
  
  
  For n = 1 To 3
    strMsgText = strMsgText & vbTab & arr_strFruitCombos(n) & vbLf
  Next
  
  
  MsgBox strMsgText, vbOKOnly, vbNullString
  
  
  strMsgText = "I have these fruit combos" & vbLf & vbLf
  GetSecondFruit arr_strFruitCombos
  
  
  For n = 1 To 3
    strMsgText = strMsgText & vbTab & arr_strFruitCombos(n) & vbLf
  Next
  
  
  MsgBox strMsgText, vbOKOnly, vbNullString
  
  
End Sub
  
  
Sub GetSecondFruit(ByRef PassedArray() As String)
Dim n As Long
  
  
  For n = LBound(PassedArray) To UBound(PassedArray)
    PassedArray(n) = PassedArray(n) & " Apples"
  Next
  
  
End Sub

Does that help show how the array is passed (actually a pointer to the array) by reference, and thus can be changed?

Mark

 

[hheydinger]

Mark - Sorry about not getting back sooner; I've been on the road a lot. Your example helped and I have the variables passing back/forth just fine. Now I have a new problem. I'm trying to save the name of a workbook when that file first gets opened. (I can't predict what other files the user may need to open and don't want to poll him for the filename.) So I declared Public sFileName as String in ThisWorkbook. Then in ThisWorkbook I have Private Sub Workbook_Open(), set sFileName = ActiveWorkbook.Name, and End Sub which is supposed to save the workbook name for later use . Later, when user is ready to populate about 5000 cells in the sFileName workbook, I display a userform progress meter. The userform seems to only work from code in a Module, but sFileName does not seem to be accessible from the Module code. Thoughts?

 

[GTO]

Now I have a new problem. I'm trying to save the name of a workbook when that file first gets opened. (I can't predict what other files the user may need to open and don't want to poll him for the filename.)

So I declared Public sFileName as String in ThisWorkbook.

You will want to start reading the VBA Help topics as well as examples on the web about Object and Class Modules (vs. Standard Modules). Variables (module-level) held in Object/Class modules are only really in existence as long as an instance of the object exists. To keep it simple for the moment though, while both the ThisWorkbook and sundry worksheet modules are Object Modules, they are accessible at any time, as of course a sheet's module must exist for the sheet to exist, and ThisWorkbook must exist for the workbook to exist.

Confused yet? It's not as complicated as it seems, but I wanted to mention for later if you try a Class and/or start trying to access a variable in a Userform's module (also an Object Module).

Anyways, a Public variable is accessible in ThisWorkbook, you have to qualify the call, like ThisWorkbook.MyVariable

There is no reason to though. Just plunk any needed Public variables into a Standard Module.

Then in ThisWorkbook I have Private Sub Workbook_Open(), set sFileName = ActiveWorkbook.Name, and End Sub which is supposed to save the workbook name for later use .

Use ThisWorkbook.Name instead of ActiveWorkbook.Name. Whilst the latter should never fail in the way being used, I would suggest this as at least IMO, it is just good habit. ThisWorkbook always refers to the workbook the code is running in, and like using sheets' CodeName(s), it is more specific (or explicit). Anytime you can refer to a specific Object (Workbook in our case, but Worksheet, a Range, etc) rather than the Active Object, we are better ensuring results.

Hope that helps,

Mark