WorksheetFunction.CountA-升级到Office 2010后无法正常工作

问题描述

以下代码段可在2010年之前的Excel中运行:

The following piece of code works in Excel prior to 2010:

myRange = Range("A:A")
NumRows = Application.CountA(myRange)

A列中有38个包含文本/值的单元格.在Excel 2007中运行该代码时，NumRows正确计算为38，但是(错误地)在Excel 2010中计算为65,536.

There are 38 cells containing text/values in column A. When the code is run in Excel 2007, NumRows correctly evaluates to 38, however, it (wrongly) evaluates to 65,536 in Excel 2010.

在两个版本中都可以在单元格中输入CountA函数.

Entering the CountA function in-cell works OK in both versions.

类似的线程是问题16696891 ，但是没有答案，我的建议是红色鲱鱼...

Similar thread is question 16696891, but there was no answer and the suggestions were, I think, red herrings...

有什么想法吗?

推荐答案

我不确定您的问题到底是什么，因为我无法让您的代码按编写的方式工作.似乎有两件事:

I'm not sure exactly what your problem is, because I cannot get your code to work as written. Two things seem evident:

1. 您似乎在依靠VBA来确定变量类型并进行相应的修改.如果您不小心，可能会造成混淆，因为VBA可能会分配您不想要的变量类型.在您的代码中，应将Range类型分配给myRange.由于Range类型是VBA中的对象，因此必须为Set，如下所示:Set myRange = Range("A:A")
2. 您对工作表函数CountA()的使用应通过.WorksheetFunction
调用

1. It appears you are relying on VBA to determine variable types and modify accordingly. This can get confusing if you are not careful, because VBA may assign a variable type you did not intend. In your code, a type of Range should be assigned to myRange. Since a Range type is an object in VBA it needs to be Set, like this: Set myRange = Range("A:A")
2. Your use of the worksheet function CountA() should be called with .WorksheetFunction

如果您还没有这样做，请考虑使用模块顶部的Option Explicit选项，并像下面所做的那样使用Dim语句键入变量.

If you are not doing it already, consider using the Option Explicit option at the top of your module, and typing your variables with Dim statements, as I have done below.

以下代码在2010年对我有用.希望它对您也有用:

The following code works for me in 2010. Hopefully it works for you too:

Dim myRange As Range
Dim NumRows As Integer

Set myRange = Range("A:A")
NumRows = Application.WorksheetFunction.CountA(myRange)

祝你好运.

这篇关于WorksheetFunction.CountA-升级到Office 2010后无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！