VBA UDF在从Excel与VBA调用时提供不同的答案 [英] VBA UDF gives different answer when called from Excel vs VBA

问题描述

以下VBA函数计算给定范围内包含公式的单元格数。当从VBA子进行调用时，它正常工作。从Excel调用时，返回范围内的单元格总数。

来自Excel的调用是 = CountFormulas（A1：C7），即使只有两个单元格公式在范围内。

是什么导致这种差异？

 公共函数CountFormulas（ByRef rng As Range）As Long 
 CountFormulas = rng.SpecialCells（xlCellTypeFormulas）.Count 
结束函数
 
公共子CountFormulasFromSub（）
 Dim rng As Range 
 Dim res As Integer 
设置rng = Sheet1.Range（a1：c7）
 res = CountFormulas（rng）
 End Sub 
  




解决方案

这是不可能的。以下链接的内容不会在UDF内部运行。

这里 - http://support.microsoft.com/kb/170787

编辑：手动计数工作方式。

 公共函数CountFormulas（rng As Range）As Integer 
 Dim i As Integer 
 Dim cell As Range 
 
对于每个单元格在rng 
如果cell.HasFormula然后
i = i + 1 
结束如果
下一个
 
 CountFormulas = i 
结束函数
  

将整数更改为 Long 如果你认为它会超过32767。

The following VBA function counts the number of cells containing formulas in a given range. It works correctly when called from a VBA sub. When called from Excel, it returns the total number of cells in the range.

The call from Excel is =CountFormulas(A1:C7), which returns 21 even though only two cells with formulas are in the range.

What is causing this discrepancy?

Public Function CountFormulas(ByRef rng As Range) As Long
    CountFormulas = rng.SpecialCells(xlCellTypeFormulas).Count
End Function

Public Sub CountFormulasFromSub()
    Dim rng As Range
    Dim res As Integer
    Set rng = Sheet1.Range("a1:c7")
    res = CountFormulas(rng)
End Sub

解决方案

This isn't possible. The following link has the things that won't work inside of a UDF.
Here - http://support.microsoft.com/kb/170787

EDIT: A manual way of counting works though.

Public Function CountFormulas(rng As Range) As Integer
Dim i As Integer
Dim cell As Range

For Each cell In rng
    If cell.HasFormula Then
        i = i + 1
    End If
Next

CountFormulas = i
End Function

Change Integer to Long if you think it will exceed 32767.

这篇关于VBA UDF在从Excel与VBA调用时提供不同的答案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！