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

本文介绍了第一次设置默认的公共变量，而在下一次增加它吗?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我试图在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