MS Excel 2010和MS Excel 2013之间的宏处理差异 [英] Macro handling difference between MS Excel 2010 and MS Excel 2013

问题描述

我当前正在修改一个在2005年初制作并在早期版本的Excel上运行的宏。最近，它已在MS Excel 2010中使用，但我正在使用MS Excel 2013修改代码。是否会有任何影响或兼容性问题？

I am currently modifying a Macro that was made early 2005 and running on previous versions of Excel. Recently it is being used in MS Excel 2010, but I am using MS Excel 2013 to modify the codes. Will there be any effects or compatibility issues?

一个非常有用的链接说明了在不同版本的Excel上处理宏的方法。谢谢。

A link explaining the handling of macros on different version of Excels will be very much appreciated. Thank you.

推荐答案

这里是一个值得关注的陷阱：Microsoft在Excel 2007中引入了大网格。这是可用行和列数量的巨大飞跃，您的用户可能已经开始利用这些行和列。

Here's one "gotcha" to be on the look-out for: with Excel 2007, Microsoft introduced the "Big Grid". This was a huge jump in the number of available rows and columns, which your users have potentially started leveraging.

也许您有一个有远见的小组在2005年为您的企业编写VBA，但在我的旧工作中，以下是确定最后一行的现状：

Perhaps you had a forward-thinking group writing VBA for your business in 2005, but at my old gig the following was status quo for identifying the last row:

LastRow = Range("A65536").End(xlUp).Row

如果2007、2010或2013工作表的填充行超过65,536行，则以上内容将无法返回预期的结果，并且旧脚本将失败，或者，

If a 2007, 2010 or 2013 worksheet has more than 65,536 populated rows, the above is not going to return the expected result, and your old script will fail, or, worse, will return an incorrect result.

您应该像上面那样嗅出任何最后一行（或最后一列）标识，并使用@SiddharthRout描述的技术对其进行重构。有关找到最后一行的权威文章：在VBA中找到最后使用的单元错误 ...简而言之：

You should sniff out any last row (or last column) identification like the above and refactor it using the techniques described by @SiddharthRout in the definitive post on finding the last row: Error in finding last used cell in VBA... In a nutshell:

查找列中的最后一行（在下面查找列A中的最后一行）在Sheet1上）

Find the last row in a column (below looks for the last occupied row in column A on Sheet1)

With Sheets("Sheet1")
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

在工作表中找到最后一行（下面使用 CountA 来防止空的Sheet1）

Find the last row in a sheet (below uses CountA to protect against an empty Sheet1)

With Sheets("Sheet1")
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastrow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        lastrow = 1
    End If
End With

这篇关于MS Excel 2010和MS Excel 2013之间的宏处理差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！