Passing an integer argument in VBA

Question

What I am trying to do is pass an interger argument between Auto Update & Update so that each time For RowNumber goes up by one that it stores that value in Auto Update and then closes and reopens Update which then continues counting RowNumber where it left off. This is what I have so far. How do I get the Panel.xls to open and close?

    Public RowNumber As Integer
Public LoopCount As Integer
    Sub auto_open()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.PrintCommunication = False
    Dim PanelFilePath As String
    Dim PanelFileName As String
    Dim PanelLocation As String
    Dim PanelWB As Workbook
        PanelFilePath = "D:\umc\UMC Production Files\Automation Files\"
        PanelFileName = "Panel.xls"
        PanelLocation = PanelFilePath & Dir$(PanelFilePath & PanelFileName)
        RowNumber = 0
        For LoopCount = 0 To 7
        If LoopCount < 7 Then
         Set PanelWB = Workbooks.Open(Filename:=PanelLocation, UpdateLinks:=3)
             PanelWB.RunAutoMacros Which:=xlAutoOpen
             Application.Run "Panel.xls!Update"
             PanelWB.Close
        End If
         Next LoopCount
        Call Shell("D:\umc\UMC Production Files\Automation Files\Auto.bat", vbNormalFocus)
    Application.Quit
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.PrintCommunication = True
    End Sub

    Function Update(LoopCount As Integer)
    getRowNumber = LoopCount
    End Function

Panel.xls!Update

 Sub Update()
Dim AutoUpdateTargetFile As String
Dim AutoUpdateWB As Workbook
For RowNumber = 1 To (Range("AutoUpdate.File").Rows.Count - 1)
    If (Range("AutoUpdate.File").Rows(RowNumber) <> "") Then
        AutoUpdateTargetFile = Range("Sys.Path") & Range("Client.Path").Rows(RowNumber) & Range("AutoUpdate.Path ").Rows(RowNumber) & Range("AutoUpdate.File").Rows(RowNumber)
        Set AutoUpdateWB = Workbooks.Open(Filename:=AutoUpdateTargetFile, UpdateLinks:=3)
            AutoUpdateWB.RunAutoMacros Which:=xlAutoOpen
            Application.Run "Auto_Update.xls!Flat"
            AutoUpdateWB.Close
    End If
    Next RowNumber
End Sub

Sorry, I meant a `Public` variable. You could make RowNumber a Public variable and then reference it in your other workbook. See [this](http://stackoverflow.com/questions/3777446/vba-global-variables-multiple-workbooks) example. — TheEngineer, Dec 17 '14 at 17:00
Thanks! Now I just need to figure out how to set up the function. =) — Michael Downey, Dec 17 '14 at 17:08
The question is a bit misleading, because the core issue is not about passing the integer argument, but proper use of Public var. Best regards, — Alexander Bell, Dec 17 '14 at 18:04

Alexander Bell · Accepted Answer · 2014-12-17T18:33:51.770

1

1). You have declared Public var:

Public RowNumber As Integer

so remove the local declaration of the same var in Sub

Dim RowNumber As Integer 'remove

2). Regarding your second issue on 'how to pass the argument", refer to the following example demonstrating two options of passing arguments to Sub ByVal or ByRef:

 Sub Example(ByVal Num1 As Integer, ByRef Num2 As Integer)
 'code
 End Sub

Hope this will help. Best regards,

edited Dec 17 '14 at 18:33

answered Dec 17 '14 at 18:11

Alexander Bell

7,842
3
26
42

Fixed that but my issue is still how to pass the argument. – Michael Downey Dec 17 '14 at 18:28

Passing an integer argument in VBA

1 Answers1