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 example. — TheEngineer
– TheEngineer, Commented Dec 17, 2014 at 17:00
Thanks! Now I just need to figure out how to set up the function. =) — Michael Downey
– Michael Downey, Commented Dec 17, 2014 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
– Alexander Bell, Commented Dec 17, 2014 at 18:04

Alexander Bell · Accepted Answer · 2014-12-17 18:33:51Z

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, 2014 at 18:33

answered Dec 17, 2014 at 18:11

Alexander Bell

7,9263 gold badges29 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Downey Over a year ago

Fixed that but my issue is still how to pass the argument.

Collectives™ on Stack Overflow

Passing an integer argument in VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related