vba 升级到 Office 2013 后 Excel 宏不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16323324/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel macro not working after upgrading to Office 2013
提问by user2340287
My company uses excel to produce network configs that go out to all of our stores. the configs are pretty much same with the exception of a few variables that get inputted into a variable sheet in Excel. User then click a command button and voila, the variables are put into the config and a new sheet is created in the workbook. This was working for us for years. However, we recently upgraded to Office 2013 and now none of our config templates work. There isn't much to the code so this could be relatively easy but I am not a programmer. Here is the code:
我的公司使用 excel 来制作我们所有商店的网络配置。除了一些被输入到 Excel 变量表中的变量外，配置几乎相同。用户然后单击一个命令按钮，瞧，变量被放入配置中，并在工作簿中创建一个新工作表。这为我们工作了多年。但是，我们最近升级到 Office 2013，现在我们的配置模板都不起作用。代码不多，所以这可能相对容易，但我不是程序员。这是代码：
Public Sub ReplaceValues(OriginalSheetName As String, VariableSheetName As String, NewSheetName As String)
Dim iVariableRowCounter As String
Dim sSearchValue As String
Dim sReplacementValue As String
Dim iControlCounter As Integer
ThisWorkbook.Sheets(OriginalSheetName).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = NewSheetName
For iControlCounter = ThisWorkbook.Sheets(NewSheetName).Shapes.Count To 1 Step -1
If ThisWorkbook.Sheets(NewSheetName).Shapes(iControlCounter).Type = msoOLEControlObject Then
ThisWorkbook.Sheets(NewSheetName).Shapes(iControlCounter).Delete
End If
Next
iVariableRowCounter = 2
While ThisWorkbook.Sheets(VariableSheetName).Cells(iVariableRowCounter, 1).Value <> ""
sSearchValue = ThisWorkbook.Sheets(VariableSheetName).Cells(iVariableRowCounter, 1).Value
sReplacementValue = ThisWorkbook.Sheets(VariableSheetName).Cells(iVariableRowCounter, 2).Value
ThisWorkbook.Sheets(NewSheetName).UsedRange.Replace what:=sSearchValue, replacement:=sReplacementValue, searchorder:=xlByColumns, MatchCase:=False, lookat:=xlPart
iVariableRowCounter = iVariableRowCounter + 1
Wend
End Sub
Public Function GenerateNewWorksheetName(OriginalSheetName As String) As String
Dim sNewSheetName As String
Dim iIncrement As Integer
Dim iSheetCounter As Integer
Dim bGoodName As Boolean
Dim bSheetFound As Boolean
iIncrement = 1
bGoodName = False
While Not bGoodName
sNewSheetName = OriginalSheetName & " - " & iIncrement
bSheetFound = False
For iSheetCounter = 1 To ThisWorkbook.Sheets.Count
If ThisWorkbook.Sheets(iSheetCounter).Name = sNewSheetName Then
bSheetFound = True
Exit For
End If
Next
If Not bSheetFound Then
bGoodName = True
End If
iIncrement = iIncrement + 1
Wend
GenerateNewWorksheetName = sNewSheetName
End Function
Any help is greatly appreciated. Thank you.
任何帮助是极大的赞赏。谢谢你。
回答by Charles Wood
Getting an error when you upgrade makes me think that your code might be using early binding (although the only references I see are constants like msoOLEControlObjectand xlByColumns, and I don't know why those would cause Copy to fail). You might need to check the references in VBA, or look into converting to late binding. Unfortunately, those are a little complex to cover here.
升级时出现错误让我认为您的代码可能使用了早期绑定（尽管我看到的唯一引用是msoOLEControlObject和 之类的常量xlByColumns，我不知道为什么这些会导致 Copy 失败）。您可能需要检查 VBA 中的引用，或考虑转换为后期绑定。不幸的是，在这里介绍这些内容有点复杂。
The other possibility is that Copy has changed in the new version of VBA (still on 2007 myself). You should be able to press F1 while the cursor is on the word Copy to find out how it is supposed to work.
另一种可能性是 Copy 在新版本的 VBA 中发生了变化（我自己还是在 2007 年）。当光标位于单词 Copy 上时，您应该能够按 F1 以了解它应该如何工作。
Either way you're probably learning a little programming! ;)
无论哪种方式，您都可能正在学习一些编程！;)
