Excel: pass workbook object from sub to userform

Question

I want to pass two objects (one open workbook and other a table) from sub procedure to userform. I have made both of them global before the sub.

When I write just the two public declarations before Sub (they are set inside Sub),works good within Sub but when the userform initializes, it throws the error "Object variable or with block variable not set"

Public N As Workbook
Public E As ListObject
Sub...

I tried to set these two objects before Sub, the Sub doesn't run, throws compile error, "Invalid outside procedure"

Public N As Workbook
Set N = Workbooks.Open(ThisWorkbook.Sheets("New").Range("A1").Text)
Public E As ListObject
Set E = N.Sheets(1).ListObjects(1)
Sub...

How to pass object variables from Sub procedures to Userforms? Currently the only solution I see is to set the objects in both the Sub procedure and the Userform.

Is what you show to us the way you really tried? If yes, it is wrong... A Public variable, for the scope you described, must be declared on top of a standard module (in the declarations area). Then, it must be set in a Sub. After having a value it can be called from the UserForm code. — FaneDuru
Yes, if I understood you correctly, that's exactly what I tried in the first way shown, I declared those two object before the Sub and then set them inside the Sub. It worked inside the Sub but when the userform initializes, it throws the error "Object variable or with block variable not set" — Repairer
This can not be possible if you declared the variable as you say in a standard module. Not in the form or in a sheet module. There are a lot of other ways to use an object. You can declare as Private at the user form module level and define it during the form initialization Set wb = Workbooks("MyWorkbook"). You can also create public variables in the form and initialized them from the standard module Sub (MyForm.wb = Workbooks("MyWorkbook"). But if you need the specific workbook in many places, the way you tried looks the most appropriate. — FaneDuru
It will also be good to check if it has a value and if not, to appropriately set it: If N is Nothing then set N = Workbooks("MyWorkbook.xlsx"). Or make a Public Sub in a standard module and call it: Sub VarInitialization() Set N = Workbooks(ThisWorkbook.Sheets("New").Range("A1").Text) Set E = N.Sheets(1).ListObjects(1) End Sub. And use this variant in the next way: If N is Nothing then VarInitialization. — FaneDuru

Nacorid Nacorid · Accepted Answer · 2020-10-08T09:01:12

Add a Sub to your UserForm to set your Workbook and ListObject objects.

Userform:

Private pWB As Workbook
Private pLO As ListObject

Public Sub SetObjects(ByVal wb As Workbook, ByVal lo As ListObject)
    Set pWB = wb
    Set pLO = lo
End Sub

In your calling code:

Sub CallingCode()
    Dim UF As UserForm1 ' Don't use the global UserForm1 object
    Set UF = New UserForm1
    UF.SetObjects N, E
    UF.Show
End Sub

Excel: pass workbook object from sub to userform

1 Answers