How to reuse variable value across Private modules

[Robinsper]

I'm trying to use the name of a workbook which I set in module 1, across other private modules but I'm getting different errors depending on how I set it up. I added comments in the code that explain what happens in the different scenarios.



Option Explicit

Sub TestSharedVars()

CopyCellsthenClose
OpenNewWksheet (AlphaExportBook)

' *** Like this
' OpenNewWksheet (AlphaExportBook) I get "Error Variable not defined"

' *** Like this
' OpenNewWksheet I get "Error Argument not optional"

CloseWkbook


End Sub

Private Sub CopyCellsthenClose()
Dim AlphaExportBook As Workbook
Dim theRows
Dim theColumns
With ActiveSheet.UsedRange
theRows = .Rows.Count
theColumns = .Columns.Count
Range(Cells(1, 1), Cells(theRows, theColumns)).Select
End With
Selection.Copy

Set AlphaExportBook = ActiveWorkbook


End Sub


Private Sub OpenNewWksheet()

'******************************
' Open the File Dialog
'******************************
Dim ReversionWBook As Workbook


With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
.Execute
If (.SelectedItems.Count = 0) Then
MsgBox "User Cancelled Operation"
' GoTo EndofInstructions
Else
End If
End With
ActiveWorkbook.Activate
Set ReversionWBook = ActiveWorkbook
End Sub

Private Sub CloseWkbook(AlphaExportBook As Workbook)

'**********************************
' Close Alpha Export WorkBook
'**********************************
AlphaExportBook.Activate
Application.DisplayAlerts = False
AlphaExportBook.Close SaveChanges:=False
Application.DisplayAlerts = True

End Sub

[SamT]

Excel VBA Programming For Dummies: John Walkenbach ... (https://www.amazon.com/Excel-Programming-Dummies-John-Walkenbach/dp/1119077397)

[SamT]

A Module is a "Code Page." Those are Private Procedures.

In Sub "TestSharedVars," the Variable, "AlphaExportBook" is not Declared ("Dimmed") as required by the Module Declaration "Option Explicit."


OpenNewWksheet I get "Error Argument not optional" That is a mistaken statement because the Procedure "OpenNewWksheet" does not take any arguments.

The Procedure "CloseWkbook" does require an argument and the way you use it in the first Sub will Raise the Error "Argument not optional."

[Paul_Hossler]

Little hard to follow, but since all Subs are in a single standard module, declare (Dim) variables outside the subs will scope them to subs within the standard module




Option Explicit

Dim ReversionWBook As Workbook '++++++++++++++++++++++++++
Dim AlphaExportBook As Workbook '++++++++++++++++++++++++++++++++++++++++++++++++



unless there is one of the same name within a sub, which will be scoped to only within that sub



Private Sub CopyCellsthenClose()
' Dim AlphaExportBook As Workbook ***************************************xx



so that's why I x-ed on the line





Option Explicit

Dim ReversionWBook As Workbook '++++++++++++++++++++++++++
Dim AlphaExportBook As Workbook '++++++++++++++++++++++++++++++++++++++++++++++++


Sub TestSharedVars()

CopyCellsthenClose
OpenNewWkbook ' --------------------------------

' *** Like this
' OpenNewWksheet (AlphaExportBook) I get "Error Variable not defined"

' *** Like this
' OpenNewWksheet I get "Error Argument not optional"

CloseWkbook
End Sub

Private Sub CopyCellsthenClose()
' Dim AlphaExportBook As Workbook ***************************************xx
Dim theRows
Dim theColumns
With ActiveSheet.UsedRange
theRows = .Rows.Count
theColumns = .Columns.Count
Range(Cells(1, 1), Cells(theRows, theColumns)).Select
End With
Selection.Copy

Set AlphaExportBook = ActiveWorkbook


End Sub

Private Sub OpenNewWkbook() '--------------------------------

'******************************
' Open the File Dialog
'******************************


With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
.Execute
If (.SelectedItems.Count = 0) Then
MsgBox "User Cancelled Operation"
' GoTo EndofInstructions
Else
End If
End With
' ActiveWorkbook.Activate ***************************************************xx
Set ReversionWBook = ActiveWorkbook
End Sub

Private Sub CloseWkbook() ' ---------------------------------------------------

'**********************************
' Close Alpha Export WorkBook
'**********************************
AlphaExportBook.Activate
Application.DisplayAlerts = False
AlphaExportBook.Close SaveChanges:=False
Application.DisplayAlerts = True

End Sub

[Robinsper]

Paul, we meet again, thank you for a great lesson in how to solve this one. Your explanation cleared my confusion regarding the use of variables across modules. Say, can you post a screenshot of where the "reputation" option is so I can start giving you the deserve score for the help? I asked the administrator but he didn't have a clear answer as to why I don't see the star you mentioned in the other thread. Thanks again.

[SamT]

Click the star icon under one of his posts. He deserves it.

[Robinsper]

I know SamT, that's what I meant when I said I emailed the administrator. For some reason, I don't get that star and I don't know if I need to check anything on some settings window. The Administrator said he would do some research and get back to me.
Here''s a picture of my screen:
18150

[SamT]

It might be that there is a minimum post count to get that :dunno

It is customized per post and per user. For example I see 4 items under most posts, but only two under mine and three under Paul's. I can't give myself or Paul infractions nor myself any reputation points.