第一次设置默认的公共变量，而在下一次增加它吗?

问题描述

我试图在Outlook中编写VBA脚本，以增加Excel中的单元格行数.但是我无法为此变量设置默认值(我使用公共变量，并且单元格的行应从"2"开始).

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").

如何在Outlook中使用脚本VBA第一次在excel中设置单元格的第二行(cell(2,1)= 1)，并在下一次增加单元格的行

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

这是我在Outlook中的代码.当我运行它时，它说无效的外部程序"

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

推荐答案

使用Static变量在同一Outlook会话的不同运行之间保留其值

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

这篇关于第一次设置默认的公共变量，而在下一次增加它吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！