当我试图遵循最佳实践时,我将Option Explicit
我的代码置于顶部,并声明我的所有变量.但这导致了与另一种使用命名常量的最佳实践的冲突.我该怎么办?
第一个问题是xlFalse
; 我对任何其他xl
变量都没有任何问题,所以这看起来像一个独立的案例.但我并不是真的担心它,因为我可以放入False
并且代码可以工作.(它有效,对吧?)
Sub GetEOP() 'Executes when the user clicks the "Import Data" button, imports the EOP data GlobalDeclarations 'Open Open dialog, assigns selected file path to wordFileName wordFileName = Application.GetOpenFilename("Rich Text Files (*.rtf), *.rtf," & "Word Files (*.doc;*.docx), *.doc;*.docx", , "EOP Table File?") 'Stop code if the user clicks Cancel (otherwise, wordFileName would be assigned a value of "False") If wordFileName = False Then Exit Sub 'Open the selected file in Word Set wordDoc = GetObject(wordFileName) 'Copy the values of the first table in the Word document to Excel With wordDoc.Tables(1) For wordRow = 1 To .Rows.Count For wordCol = 1 To .Columns.Count EOP.Cells(wordRow, wordCol).Value = WorksheetFunction.Clean(.cell(wordRow, wordCol).Range.Text) Next wordCol Next wordRow End With 'Close Word Set wordDoc = Nothing 'Find the last row and column of the EOP sheet. rw2 = EOP.Cells.Find("*", after:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=xlFalse).Row 'ERROR: Variable not defined -- xlFalse cl2 = EOP.Cells.Find("*", after:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=xlFalse).Column 'ERROR: Variable not defined -- xlFalse 'Format EOP worksheet EOP.Cells(1, 1).RowHeight = 15 EOP.Cells(2, 1).RowHeight = 15 EOP.Cells(1, 1).Resize(50, 12).Interior.Color = clrbg EOP.Cells(1, 1).Resize(1, cl2).Interior.Color = clr2 EOP.Cells(2, 1).Resize(rw2 - 1, cl2).Interior.Color = clr1 End Sub
但更严重的问题是当我从Excel运行Word代码时,就像在wordDoc.Close savechanges:=wdDoNotSaveChanges
.我可以使用wdDOnotSaveChanges= 0
而不用担心它,但我认为可能有更好的方法来处理这种情况.
这里有大量的代码; 抛出错误的行一直在底部,在Closes Word Document
代码块中.
Sub DrillFolder(Folder) Dim SubFolder For Each SubFolder In Folder.SubFolders 'Skip Archive and Old folders If InStr(UCase(SubFolder.Name), UCase("Archive")) > 0 Or _ InStr(UCase(SubFolder.Name), UCase("Old")) > 0 Or _ InStr(UCase(SubFolder.Name), UCase("do not use")) > 0 Then GoTo SkipFolder DrillFolder SubFolder SkipFolder: Next Dim File Dim sortFile As Worksheet For Each File In Folder.Files 'Checks if the file name has any of these time points in it. If InStr(UCase(File.Name), UCase("bl")) > 0 Or _ InStr(UCase(File.Name), UCase("eop")) > 0 Or _ InStr(UCase(File.Name), UCase("t2")) > 0 Or _ InStr(UCase(File.Name), UCase("t3")) > 0 Then 'Only look at RTFs If File.Type = "Rich Text Format" Then 'Sorts the file according to the time point in the file name. If InStr(UCase(File.Name), UCase("bl")) > 0 Then Set sortFile = Worksheets("Base") If InStr(UCase(File.Name), UCase("eop")) > 0 Then Set sortFile = Worksheets("EOP") If InStr(UCase(File.Name), UCase("t2")) > 0 Then Set sortFile = Worksheets("T2") If InStr(UCase(File.Name), UCase("t3")) > 0 Then Set sortFile = Worksheets("T3") 'sortTotalRow is used to make sure that entries in one worksheet don't skew the alignment of other worksheets Dim sortTotalRow As Integer Select Case sortFile.Name Case "Base" sortTotalRow = baseTotalRow Case "EOP" sortTotalRow = eopTotalRow Case "T2" sortTotalRow = t2TotalRow Case "T3" sortTotalRow = t3TotalRow Case Else sortTotalRow = 0 End Select 'Opens the selected file in Word Set wordDoc = wordApp.Documents.Open(File.Path) 'Adds file information to the Import worksheet With Import.Cells(docCount + 2, 1) .Value = wordDoc .Offset(0, 1).Value = wordDoc.Tables(1).Rows.Count .Offset(0, 2).Value = sortFile.Name .Offset(0, 3).Value = wordDoc.FullName End With 'Copies values of first table in document to Excel If wordDoc.Tables.Count > 0 Then Restart: With wordDoc.Tables(1) 'Combine Birth Month and Birth Year columns, if they're separate Dim i As Integer For i = 1 To .Columns.Count If InStr(UCase(WorksheetFunction.Clean(.cell(1, i).Range.Text)), UCase("Birth year")) = 1 Then 'Change Birth Month column to Birth Month and Year column Dim k As Long For k = 1 To .Rows.Count .cell(k, i - 1).Range.Text = .cell(k, i - 1).Range.Text & .cell(k, i).Range.Text Next k 'Then delete the Birth Year column, so it doesn't get copied twice .Columns(i).Delete GoTo Restart End If 'Delete "describe yourself" and "Where are you taking this survey?" columns If InStr(UCase(WorksheetFunction.Clean(.cell(1, i).Range.Text)), UCase("describe")) > 0 Then .Columns(i).Delete GoTo Restart End If If InStr(UCase(WorksheetFunction.Clean(.cell(1, i).Range.Text)), UCase("taking this survey")) > 0 Then .Columns(i).Delete GoTo Restart End If Next i 'Account for Grandma code by putting in dummy values for non-Gcode rounds Dim gmaFind As Boolean 'Check for Gma in the table With wordDoc.Range.Find .Text = "Grandmother" .MatchCase = False gmaFind = .Execute End With 'If you don't find it, add a column and fill it with XXX If gmaFind = True Then Else .Columns.Add For i = 1 To .Rows.Count .cell(i, .Columns.Count).Range.Text = "XXX" Next i End If For wordRow = 1 To .Rows.Count For wordCol = 1 To .Columns.Count 'Takes off headers for the tables after the first If sortTotalRow <> 0 And wordRow = 1 Then GoTo Skip 'Skip "How do you describe yourself?" column If InStr(WorksheetFunction.Clean(.cell(1, wordCol).Range.Text), "describe") > 0 Then GoTo Skip 'Skip "Please tell us the place where you are taking this survey." column If InStr(WorksheetFunction.Clean(.cell(1, wordCol).Range.Text), "taking this survey") > 0 Then GoTo Skip 'If you're in the Obs column, also add in the name of the round. If wordCol = 1 Then sortFile.Cells(wordRow + sortTotalRow, wordCol).Value = Left(wordDoc.Name, 8) & " " & WorksheetFunction.Clean(.cell(wordRow, wordCol).Range.Text) Else sortFile.Cells(wordRow + sortTotalRow, wordCol).Value = WorksheetFunction.Clean(.cell(wordRow, wordCol).Range.Text) End If 'This is a flag 'to tell GoTo 'where to go. Skip: Next wordCol Next wordRow 'Adds number of records to totalRow, so the next table appears under the previous. Select Case sortFile.Name Case "Base" 'The -1 is accounting for the headers of the tables that have been omitted '(otherwise it would be a blank row) baseTotalRow = baseTotalRow + .Rows.Count - 1 Case "EOP" eopTotalRow = eopTotalRow + .Rows.Count - 1 Case "T2" t2TotalRow = t2TotalRow + .Rows.Count - 1 Case "T3" t3TotalRow = t3TotalRow + .Rows.Count - 1 Case Else End Select End With End If 'At the end of importing, docCount is used as "Debug.print "Time taken to import docCount documents"" docCount = docCount + 1 'Closes Word Document 'Const wdDoNotSaveChanges As Integer = 0 wordDoc.Close savechanges:=wdDoNotSaveChanges 'ERROR: Variable not defined -- wdDoNotSaveChanges End If End If Next End Sub
谢谢您的帮助!
如果我理解正确,你会发现如何Option Explicit
从非常严重的错误中拯救你!
如果你的代码因为wdDoNotSaveChanges
未定义而拒绝编译,那是因为没有引用Microsoft Word对象库(即你可能会对它进行后期绑定).
没有Option Explicit
,VBA愉快地编译,并且未定义的标识符在执行时被动态定义为隐藏的Variant
局部变量,其中包含任何Variant
变量的默认值:Empty
...,当传递给接受的函数/过程/方法时整数类型,无论您将其命名为什么0
,都会以静默方式转换为a .
如果您对Word对象模型进行后期绑定,则不会加载Word对象库中定义的常量,因此为了使用命名常量并避免幻数(您指的是最佳实践),您需要定义自己的 - 所以你点击MSDN(或临时引用对象模型并点击F2找到你需要的值)并在你自己的代码中重现这些值:
Private Enum WdSaveOptions wdDOnotSaveChanges= 0 ' equivalent to Boolean value False wdPromptToSaveChanges = -2 wdSaveChanges = -1 ' equivalent to Boolean value True End Enum
现在不仅Option Explicit
不会干扰,您的代码也将正常工作!