Set default public variable at the first time and

Question

I tried to write VBA script in outlook to increase row of cells in excel. But I cannot set default value for this variable (I use public variable and row of cell should be started from "2").

How can I use script VBA in outlook to set second row of cell ( cell(2,1) =1) in excel at the first time, and increase row of cell for the next time

Here is my code in outlook. and when I run it, It said that "Invalid outside procedure"

Public count As Integer: count = 2

Sub test()

    Dim xlApp As Object
    Dim xlWB As Object
    Dim xlSheet As Object

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err <> 0 Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    'Check file open or not, and open it if it isn't opened
    If (IsWorkBookOpen("D:\Book1.xlsx") = True) Then
        Set xlWB = xlApp.Workbooks("Book1.xlsx")
    Else
        Set xlWB = xlApp.Workbooks.Open("D:\Book1.xlsx")
    End If

    Set xlSheet = xlWB.Sheets("Sheet1")

    'Process excel file
    xlSheet.Cells(count, 1) = 1
    count = count + 1

End Sub

Function IsWorkBookOpen(FileName As String) 'function to check excel file is open or not
    Dim ff As Long, ErrNo As Long
    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0
    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

Answer 1

In the VBA you can not assign value to a non-constant outside of a subroutine.

Public count As Integer: count = 2

You can just initialize your public variable using the Workbook_Open() event.

Private Sub Workbook_Open()
    counter = 2
End Sub

Alternatively you can use GetSettings and SaveSettingReference: MSDN GetSetting Function

Answer 2

use Static variables to preserve their value between different runs of the same Outlook session

Sub test()
    Dim xlApp As Object
    Dim xlWB As Object
    Dim xlSheet As Object

    Static count As Integer '<-- 'Static' preserves values between consecutive runs

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err <> 0 Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0

    'Check file open or not, and open it if it isn't opened
    If IsWorkBookOpen("D:\Book1.xlsx") Then
        Set xlWB = xlApp.workbooks("Book1.xlsx") '<-- 'workbooks()' acctepts only file name and extension
    Else
        Set xlWB = xlApp.workbooks.Open("D:\Book1.xlsx")
    End If

    Set xlSheet = xlWB.Sheets("Sheet1")

    If count = 0 Then count = 2 '<-- 1st time it sets count to 2
    xlSheet.Cells(count, 1) = 1
    count = count + 1 '<-- increment count for next time to be left as it is before writing to xlSheet
End Sub