Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
楼主: vbamaster

Application.SumIf和WorksheetFunction.SumIf之间的区别

  [复制链接]
 楼主| 发表于 2012-4-29 20:07 | 显示全部楼层
那么的帅 发表于 2012-4-29 19:59
因为SUMIF 第2个参数使用了常量数组,导致出错
arr1 = Application.WorksheetFunction.SumIf(Range("a2: ...

arr = Application.SumIf(Range("a2:a7"), Array("B", "C", "G", "R"), Range("B2:B7"))

这样写的话,sumif的第二个参数也是常量数组,为什么就可以运行?

arr = Application.WorkSheetFunction.SumIf(Range("a2:a7"), Array("B", "C", "G", "R"), Range("B2:B7"))

这两种写法不是等价的吗:Application.SumIf 和 Application.WorkSheetFunction.SumIf,为什么前者能运行,后者就不行?
回复

使用道具 举报

发表于 2012-4-29 20:12 | 显示全部楼层
vbamaster 发表于 2012-4-29 20:07
arr = Application.SumIf(Range("a2:a7"), Array("B", "C", "G", "R"), Range("B2:B7"))

这样写的话, ...


加上WorkSheetFunction这个就是指定了这个属性,而这个属性是不支持返回数组的,所以出错
而不加上 WorkSheetFunction ,就没有指定这个属性,就可以返回数组或者常量,所以没错
个人理解。没找到官方解释。

EXCEL 或者 VBA 的很多用法都找不到解释,只能靠我们在实际中具体分析了。

回复

使用道具 举报

 楼主| 发表于 2012-4-29 20:30 | 显示全部楼层
那么的帅 发表于 2012-4-29 20:12
加上WorkSheetFunction这个就是指定了这个属性,而这个属性是不支持返回数组的,所以出错
而不加上 Wo ...

谢谢解答,也只能这么理解了,如果没有更好的答案,这个答案就设为最佳答案了。
回复

使用道具 举报

发表于 2012-4-29 21:19 | 显示全部楼层
本帖最后由 adders 于 2012-4-29 13:29 编辑

这个问题很有意思.我也不算真正懂,但忍不住也想加两句,说得不好请别介意.

首先,Worksheetfunciton.(excel自带的Function)与Application.Worksheetfunction.(excel自带的Function)在EXCEL环境下效果是一样的,也就是说Application可以省略,因为默认是EXCEL.

如果是在其他的OFFICE程序中调用EXCEL函数时需要加上Application,比如在Word中用VBA调用EXCEL函数,微软有这样的示例:

Dim xlApp As Excel.Application
Dim x, y As Integer
Set xlApp = New Excel.Application
x = ActiveDocument.ContentControls(1).Range.Text
y = ActiveDocument.ContentControls(2).Range.Text
ActiveDocument.ContentControls(3).Range.Text = _
    xlApp.WorksheetFunction.Sum(x, y)

(以下部分的讨论均只考虑EXCEL环境,不涉及其他任何程序,所以这里要再强调一下,我同意楼主这里的观点,在用Worksheetfunction时,前面这个Application.是可以省略掉的)

好,其次我们来看一下Application.(excel自带Function)与Application.Worksheetfunction.(excel自带Function)有什么不同,其实在绝大多数情况下它们在使用上是一样的.网上通常认为主要区别在于对弹出错误的处理上.比如在用VLookup时:

程序1:
Sub WFMethod()
    Dim x As String
   
    x = Application.WorksheetFunction.VLookup("hello", Range("A1:B100"), 2, False)
   
    Debug.Print x
   
End Sub
如果在A1:A100没有对应"hello"值,以上运行时会出现Run Time Error,所以容错处理上通常用On Error Resume Next, If Err.Number <> 0这样的结构.

程序2:
Sub AppMethod()
    Dim y As Variant
   
    x = Application.VLookup("hello", Range("A1:B100"), 2, False)
   
    Debug.Print y
   
End Sub
如果在A1:A100没有对应"hello"值,以上不会出现Run Time Error,会看到y值是: Error 2042. 在预想程序找不到到相应结果的"容错"处理上通常只要用: If IsError(y) = False 这样的语句就可以了.

另外,网上有人还发现过一个例子,是关于RoundDown函数结合声明Currency类型出现的:

Sub foo()
  Const CashIn As Currency = 53.4
  Const CashDue As Currency = 54.175
  Dim SheetCredit As Currency
    SheetCredit = CashIn - CashDue
    Debug.Print "SheetCredit not Rounded", , SheetCredit
    Debug.Print "WorksheetFunction.RoundDown:", _
        WorksheetFunction.RoundDown(SheetCredit, 2)
    Debug.Print "Application.RoundDown:", , _
        Application.RoundDown(SheetCredit, 2)
End Sub

SheetCredit not Rounded             -0.775
WorksheetFunction.RoundDown:        -0.77
Application.RoundDown:              -0.78


理论上Rounddown的结果都应该是-0.77,但实际上Application.RoundDown结果为-0.78

奇妙的是,如果在声明变量时类型定义SheetCredit为Double类型,Application和Worksheetfunction的结果就是一样的了,都是0.77

SheetCredit not Rounded                   -0.774999999999999
WorksheetFunction.RoundDown:              -0.77
Application.RoundDown:                    -0.77


据我所知,除了数值范围外,Currency和Double最主要的区别是,前者不用浮点计算器,后者用,所以精度是不一样的(从0.775与0.7749999999999的区别上也可看出),但为什么Application和Worksheetfunction在处理对待-0.775上有不同的结果?更奇怪的是为什么Application.RoundDown是-0.78?事实上,我试了Application.Round, Application.RoundUp,结果都是-0.78!

也许是微软的另一个bug?

(待续,先出门一下)

回复

使用道具 举报

发表于 2012-4-29 21:22 | 显示全部楼层
这个官方也没有什么解释,application.函数好象是绕过Worksheetfunction对象,直接调用工作表函数。Worksheetfunction对象是为调用工作表函数而专意设置的对象。application调用函数好象更能原来工作表函数更多的功能,而至于Worksheetfunction对象调用函数和工作表函数用法有什么区别和限制,也滑官方的解释。无从找寻找案。
回复

使用道具 举报

 楼主| 发表于 2012-4-29 22:40 | 显示全部楼层
感谢各位老师的回答,今天有些晚了,先标记下,等有时间再细研究。
回复

使用道具 举报

发表于 2012-4-30 05:01 | 显示全部楼层
本帖最后由 adders 于 2012-4-29 16:03 编辑
vbamaster 发表于 2012-4-29 09:40
感谢各位老师的回答,今天有些晚了,先标记下,等有时间再细研究。


好了,我接着15楼说.

前面做了这么多的铺垫,只是想说明一下,Application.与Worksheetfunction.(或Application.Worksheetfunction.)在VBA中的用法还是有区别的,虽然在绝大多数情形下我们似乎不需要在意,所以如果你看到某本VBA的书上说,以下三种写法可以互通,也不算什么错:

Application.(excel自带Function)

Application.Worksheetfunction.(excel自带Function)

Worksheetfucntion.(excel自带Function)

当然,这个主题的要求是为了探讨下两种用法的区别,那么我们就继续看还有哪些不同点.

我曾经看过一位微软MVP的观点,他讲了两点:

一是微软认为比较成熟的语法用法,会将它档案化(中文可能翻译得不到位,英文是称为Documentation),如果没有档案化,是因为微软工程师们可能会在将来的版本中继续改进或增强或完善或抛弃它的用法.一般档案化的用法微软在后期很少会改动,所以这位MVP建议大家在用EXCEL函数或VBA时,如果有可能,尽量用档案化过的东西(微软的这个档案化的列表在哪里,他没有说,我也不清楚),因为这些是微软自认为比较成熟的用法.

二是,微软在新推出Worksheetfunction的时候(我不知道是哪一年了),曾经表明将来会以这个为主,将来会淘汰对Application.(excel自带Function)这种用法的支持,要强制用户使用Application.Worksheetfunction.(excel自带Function),或者Worksheetfucntion.(excel自带Function),最关键的,是它把Worksheetfunction用法档案化了,而Application直接带EXCEL Function的用法一直没有档案化.所以为了避免将来因为版本升级造成代码多处改动的麻烦,这位MVP建议大家现在就养成用Worksheetfunction的习惯!

不过,事实上,直到2010版本为止,Application和Worksheetfunction的使用还在并存着,绝大多数用户也将它们混为一谈,微软什么时候会终止Application的直接调用,或者什么时候改变了想法,将Application的直接调用也档案化,只有它自己知道了(?).

好了,啰嗦了半天,言归正传,现在来看一看楼主的问题.

我自己做了试验,我的配置是WIN7 + EXCEL2003 (英文版),在楼主的3种写法中(实际就两种,因为写法2和写法3是一样的):

1) arr = Application.SumIf(Range("a2:a10000"), Array("B", "C", "G", "R"), Range("B2:B10000"))
我试验的结果是不出现Run Time Error但是也没有得到结果

2) arr = WorksheetFunction.SumIf(Range("a2:a10000"), Array("B", "C", "G", "R"), Range("B2:B10000"))
我试验的结果是Run Time Error 13, 类型不匹配

这个试验在预料之中.类似于我在15楼中的Vlookup例子.

接下来,我稍稍改动代码如下:
3) arr = Application.Sum(Application.SumIf(Range("a2:a10000"), Array("B", "C", "G", "R"), Range("B2:B10000")))
这一次我得到了正确的结果

我再改动一下:
4) arr = WorksheetFunction.Sum(Application.SumIf(Range("a2:a10000"), Array("B", "C", "G", "R"), Range("B2:B10000")))
我得到了跟3)一样的正确结果

我再改动一下:
5) arr = WorksheetFunction.Sum(WorksheetFunction.SumIf(Range("a2:a10000"), Array("B", "C", "G", "R"), Range("B2:B10000")))
结果,Run Time Error 13, 类型不匹配

3,4,5这三个代码说明了什么,我的粗浅结论是:

a) 在SumIf这个函数上,Worksheetfunction是不支持数组条件的,就象前面有网友指出的,如果将条件Array("B", "C", "G", "R")改为"B",是可以得到结果的

b) 在SumIf这个函数上,用Application直接调用,是支持数组条件的

c) 在Sum这个函数上,Application与Worksheetfunction的调用没有区别,原因是Sum函数不涉及数组条件

d) 我猜Worksheetfunction不支持SumIf数组条件的可能原因是,因为Worksheetfunction相当于在Excel表格中直接输入,所以当在Excel表格中直接输入数组公式时,因为需要Ctrl + Shift + Enter三键并按才能得到结果,然后到VBA中用Worksheetfunction这个Object调用的时候,无法模拟CSE三键齐按,所以出现Run Time Error,而用Application直接调用,可能在程序后台有另外的内嵌计算方法直接支持数组模式,所以就避免了出错,这一点仍然与前面的Vlookup一例有相通之处.

结论是,Application.{excel function}与Worksheetfunction.{excel function}区别不大,但如果在你的代码里出现区别时,请不要太惊讶!

我个人的建议是保持代码习惯前后一致,如果用Application就一直用,如果用Worksheetfunction也就一直用,不要在一段代码中混用这两种方法.
回复

使用道具 举报

发表于 2012-4-30 12:16 | 显示全部楼层
Excel中实现了SumIf函数,只实现了一次,其定义是
[helpcontext(0x00014159)]
HRESULT _stdcall SumIf(
                [in] Range* Arg1,
                [in] VARIANT Arg2,
                [in, optional] VARIANT Arg3,
                [out, retval] double* RHS);
从其定义可以知道,它包含三个参数,
1. Arg1, Range* 类型
2. Arg2, VARIANT类型
3. Arg3, VARIANT类型,可选参数
返回类型为double

a. WorksheetFunction一个全局成员,所以可以使用WorksheetFunction.SumIf的形式
b. 另外,Application也是一个全局成员,而Application有一个成员WorksheetFunction,所以可以使用Application.WorksheetFunction.SumIf的形式。
c. Application.SumIf这种用法倒是有点怪,感觉是Application.WorksheetFunction.SumIf的省略。
d. 在a和b两种情况下的WorksheetFunction,虽然名字相同,但不是同一个成员。但我认为他们调用了同一个函数SumIf
e. 在c的情况下,SumIf本身并不是Application的成员,而是其成员WorksheetFunction的成员,但最终可以直接在Application这级直接引用其子成员的成员,我不知道这是一种什么机制,但这种机制我想至少造成了两个现象:
一个是自动列出成员功能,就是在我们输入完Application后,加上一个点后,出来的成员表中并没有SumIf,要想使用SumIf,就要手功输入。
二个便与本贴有关(当然,这是我的理解),是关于错误检查的,对于a和b两种情形,由于SumIf明确是WorksheetFunction的成员,所以Excel进行了明确的类型检查,而对于c这种情形,由于不是直接成员,Excel没有或者没办法进行类型检查。这样最终的表现就是a和b两种情形会报错,而c不会报错,出来了结果。

为了佐证我的观点,我加了一句On Error Resume Next,三个语句都正常运行了,并且得到了同样的结果

Sub SumIfDemo()
    Dim arr
   
    arr = Application.SumIf(Range("a2:a10000"), Array("B", "C", "G", "R"), Range("B2:B10000"))
   
    On Error Resume Next
    ' 下面两句要想忽略类型检查,要在前面加忽略错误语句
    ' 其结果与Application后带SumIf结果相同
    arr = WorksheetFunction.SumIf(Range("a2:a10000"), Array("B", "C", "G", "R"), Range("B2:B10000"))
    arr = Application.WorksheetFunction.SumIf(Range("a2:a10000"), Array("B", "C", "G", "R"), Range("B2:B10000"))
   
End Sub

回复

使用道具 举报

 楼主| 发表于 2012-4-30 21:00 | 显示全部楼层
adders 发表于 2012-4-30 05:01
好了,我接着15楼说.

前面做了这么多的铺垫,只是想说明一下,Application.与Worksheetfunction.(或App ...

arr = Application.SumIf(Range("a2:a10000"), Array("B", "C", "G", "R"), Range("B2:B10000"))
我用的是XP+EXCEL2003,以上代码可以正确得到结果,请adders老师再次测试确认一下。
Sub t5()
       Dim arr
       arr = Application.SumIf(Range("a2:a10000"), Array("B", "C", "G", "R"), Range("B2:B10000"))
       Stop
End Sub
回复

使用道具 举报

 楼主| 发表于 2012-4-30 21:06 | 显示全部楼层
吕?布 发表于 2012-4-30 12:16
Excel中实现了SumIf函数,只实现了一次,其定义是
[helpcontext(0x00014159)]
HRESULT _stdcall SumIf(

Sub SumIfDemo()
    Dim arr
   
    arr = Application.SumIf(Range("a2:a10000"), Array("B", "C", "G", "R"), Range("B2:B10000"))
   
    On Error Resume Next
    ' 下面两句要想忽略类型检查,要在前面加忽略错误语句
    ' 其结果与Application后带SumIf结果相同
    arr = WorksheetFunction.SumIf(Range("a2:a10000"), Array("B", "C", "G", "R"), Range("B2:B10000"))
    arr = Application.WorksheetFunction.SumIf(Range("a2:a10000"), Array("B", "C", "G", "R"), Range("B2:B10000"))
   
End Sub

吕布老师,你的这段代码有些问题:
Sub SumIfDemo()
    Dim arr
   
    '
arr = Application.SumIf(Range("a2:a10000"), Array("B", "C", "G", "R"), Range("B2:B10000"))
   
    On Error Resume Next
    ' 下面两句要想忽略类型检查,要在前面加忽略错误语句
    ' 其结果都为空

    arr = WorksheetFunction.SumIf(Range("a2:a10000"), Array("B", "C", "G", "R"), Range("B2:B10000"))
    arr = Application.WorksheetFunction.SumIf(Range("a2:a10000"), Array("B", "C", "G", "R"), Range("B2:B10000"))
   
End Sub


点评

确实是有问题。看到了  发表于 2012-5-1 00:05
??  发表于 2012-4-30 23:54
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-3-29 23:49 , Processed in 0.736320 second(s), 7 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表