VBA: Pass Args back to Sub from PVT Sub

[zero269]

Hello,

I'm having some trouble figuring out how to get a Private Sub to return the values it processed received from a Public Sub. Everything works fine when the Subs are NOT Private Subs.

NOTE: I'm trying to avoid placing them all in a single module. I'm doing that already for one Sub - this is my first time creating something like this - and the below Sub is a 2nd one in progress. It serves a different purpose, but building the Message for the InputBox and Validating the choices are similar.

Running the code as shown below works when the Sub's in module 'mod_PVT' are 'Public' and not set as 'Private'.
Any help with getting the Sub's from mod_PVT to return their arguments as if they were public would be greatly appreciated.

Module Name: mod_SUB

Option Explicit
'Filter Books to Check In
Sub Books_Check_In()

    'Declarations
    Dim arrList As Variant, Message As Variant, LastChoice As Integer
    
    'Array of Student Names
    arrList = wsLists.Range("t_Students[Full Name]").Value
    
    'Get Message Box
'    Run "mod_PVT.MsgBox_Builder", arrList, Message
    Call MsgBox_Builder(arrList, Message)
    
    'Append Last Choice to Message
    LastChoice = UBound(arrList, 1) + 1
    Message = Message & LastChoice & vbTab & "Clear & Reset Table" 'Append option to reset table
    
    'Input Box
    Dim Titlebar As String: Titlebar = "Manage Check In's for..."
    Dim Choice As Integer: Choice = InputBox(Message, Titlebar)
    
    'Verify Choice
    Dim Response As Boolean 'Default is False
'    Run "mod_PVT.Validate_Choice", Choice, LastChoice, arrList, Response
    Call Validate_Choice(Choice, LastChoice, arrList, Response)

    'Validate Response
    If Response = False Then 'Exit Sub
        MsgBox "That Sucks!"
    Else
        MsgBox "Yay, it works!"
    End If
    
End Sub

Module Name: mod_PVT

Option Explicit
'Build MsgBox from an Array List
Private Sub MsgBox_Builder(arrList As Variant, Message As Variant)
    Dim NewLine As String, i As Long
    For i = LBound(arrList, 1) To UBound(arrList, 1)
        NewLine = i & vbTab & arrList(i, 1) & vbNewLine
        Message = Message + NewLine
    Next i
End Sub

'Validate Choice
Private Sub Validate_Choice(Choice As Integer, LastChoice As Integer, arrList As Variant, Response As Boolean)
    If Choice >= 1 And Choice <= LastChoice - 1 Then 'If Valid Entry
        arrList = arrList(Choice, 1)  'Store Student Name in arrList
        arrList = Array(arrList)      'Convert single Student to Array
        Response = True
    ElseIf Choice = LastChoice Then  'If Choice = last entry for 'All Names'
        Response = True
    Else 'If Invalid Entry
        Dim YNAnswer As Integer: YNAnswer = MsgBox("Invalid entry. Try again?", vbYesNo)
            If YNAnswer = vbYes Then
                MsgBox "Trying again..."
                Call Books_Check_In
            Else
                Exit Sub
            End If 'YNAnswer = vbNo
        Exit Sub
    End If 'Invalid entry
End Sub

Sample Data used to build out the Array List of Student Names:

VBA Testing.xlsm
	A	B	C	D
1	ID	First Name	Last Name	Full Name
2	65	Marty	Cortez	Marty, Cortez
3	34	Della	Whalen	Della, Whalen
4	60	Dianne	Stephens	Dianne, Stephens
5	95	Enrique	Atkins	Enrique, Atkins
6	87	Byron	Keller	Byron, Keller
7	81	Katrina	Moss	Katrina, Moss
8	86	Dwayne	Murry	Dwayne, Murry
9	84	Kathy	Kramer	Kathy, Kramer
10	74	Teri	Vazquez	Teri, Vazquez
11	67	Sarah	Farrell	Sarah, Farrell
12	38	Wes	Rowe	Wes, Rowe
13	81	Gerald	Shaffer	Gerald, Shaffer
14	37	Joanna	Grant	Joanna, Grant
15	52	Barbara	Cole	Barbara, Cole
16	92	Dena	Manning	Dena, Manning
17	93	Cary	Pugh	Cary, Pugh
18	76	Janine	Hays	Janine, Hays
19	49	Debora	Beasley	Debora, Beasley
20	74	Kendra	Roberson	Kendra, Roberson
21	56	Cora	Crawford	Cora, Crawford
Lists

Cell Formulas
Range	Formula
D2:D21	D2	=TEXTJOIN(", ",TRUE,B2:C2)

 

[RoryA]

If those subs are private, you can't Call them from the other module, so your issue is nothing to do with arguments. Why do you want them to be private when you clearly need them accessible from other modules?

 

[zero269]

If those subs are private, you can't Call them from the other module, so your issue is nothing to do with arguments. Why do you want them to be private when you clearly need them accessible from other modules?

Hi Rory,

Sorry for the late reply. I pretty much came to the conclusion that I don't fully understand how to best maximize macros and modules. Although I've seen online examples of how people work between private and public sub procedures, they never worked in my testing and were too complex for me to even understand.

My objective was to re-use sub procedures that act like a function for a variety of macros throughout a given workbook. I was also looking for a way to keep them from showing up in the list of macros when assigned to a button or Ribbon.

 

[RoryA]

Those routines won’t show up in the macros dialog anyway because they take arguments

 

[Skyybot]

You could put Public variables in your Workbook.