Robinsper
01-23-2017, 11:24 PM
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
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