CODEWITHSUNDEEP
excelvba
Robinsper
Robinsper

Reputation: 79

VBA Use variable value across modules

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

Upvotes: 0

Views: 230

Answers (1)

YowE3K
YowE3K

Reputation: 23974

Firstly, you shouldn't be getting an "Argument not optional" error when calling OpenNewWksheet because that subroutine is not expecting arguments. You would get that error trying to call CloseWkbook without specifying a parameter, because that subroutine expects a Workbook object to be passed to it.

The easiest way to make the workbook available to all subroutines is to declare the variable with module-level scope, e.g.

Option Explicit
Dim AlphaExportBook As Workbook

Sub TestSharedVars()
    CopyCellsthenClose
    OpenNewWksheet
    CloseWkbook
End Sub

Private Sub CopyCellsthenClose()
    Dim theRows
    Dim theColumns
    With ActiveSheet.UsedRange
        theRows = .Rows.Count
        theColumns = .Columns.Count
        'Note - the following line won't do what you expect unless
        '       UsedRange starts at cell A1
        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  ' Does this need to be module-level scope too?

    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        .Show
        .Execute
        If .SelectedItems.Count = 0 Then
            MsgBox "User Cancelled Operation"
        End If
    End With
    'ActiveWorkbook.Activate ' This is redundant - the ActiveWorkbook is already active
    Set ReversionWBook = ActiveWorkbook
End Sub

Private Sub CloseWkbook()

'**********************************
'  Close Alpha Export WorkBook
'**********************************
    'You don't need to activate the workbook before you close it
    'AlphaExportBook.Activate
    Application.DisplayAlerts = False
    AlphaExportBook.Close SaveChanges:=False
    Application.DisplayAlerts = True
End Sub

Alternatively, you could pass the workbook object between subroutines as follows:

Option Explicit

Sub TestSharedVars()
    'Dimension object to have scope only within this subroutine, but we
    '  will pass a reference to this object to the other subroutines that
    '  need to reference it
    Dim AlphaExportBook As Workbook
    CopyCellsthenClose AlphaExportBook
    OpenNewWksheet
    CloseWkbook AlphaExportBook
End Sub

Private Sub CopyCellsthenClose(wb As Workbook)
    Dim theRows
    Dim theColumns
    With ActiveSheet.UsedRange
        theRows = .Rows.Count
        theColumns = .Columns.Count
        'Note - the following line won't do what you expect unless
        '       UsedRange starts at cell A1
        Range(Cells(1, 1), Cells(theRows, theColumns)).Select
    End With
    Selection.Copy

    Set wb = ActiveWorkbook
End Sub


Private Sub OpenNewWksheet()

'******************************
'    Open the File Dialog
'******************************
    Dim ReversionWBook As Workbook  ' Does this need to be module-level scope too?

    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        .Show
        .Execute
        If .SelectedItems.Count = 0 Then
            MsgBox "User Cancelled Operation"
        End If
    End With
    'ActiveWorkbook.Activate ' This is redundant - the ActiveWorkbook is already active
    Set ReversionWBook = ActiveWorkbook
End Sub

Private Sub CloseWkbook(wb As Workbook)

'**********************************
'  Close Alpha Export WorkBook
'**********************************
    'You don't need to activate the workbook before you close it
    'wb.Activate
    Application.DisplayAlerts = False
    wb.Close SaveChanges:=False
    Application.DisplayAlerts = True
End Sub

Upvotes: 1

Related Questions