VBA舍入功能与工作表舍入功能 [英] VBA Round function vs Worksheet Round function

问题描述

我试图将此Excel函数更改为VBA代码

I tried to change this Excel function into VBA code

Excel

=ROUND(value,sigfig-(1+INT(LOG10(ABS(value)))))

VBA

Public Function sigfig(val As Double, sigf As Integer) As Double
Dim var As Double
var = Abs(val)
var = Application.WorksheetFunction.Log10(var)
var = Int(var)
sigf = sigf - (1 + var)
sigfig = Round(val, sigf)
End Function

对于0以下的值，Excel和VBA都可以正常工作.但是在VBA中，当值(val)大于sigfig(sigf)时，它给我一个错误.例如，如果我想拥有3个sigfig的55481，它给出的是#VALUE，而不是55500.

For below 0, both of the Excel and VBA work well. However in VBA, when value (val) is more than sigfig (sigf), it gives me an error. For example, if I want to have 3 sigfig of 55481, it gives #VALUE, instead of 55500.

请帮助！

推荐答案

问题是VBA的 Round 函数中的第二个参数不能为负，但在工作表函数的版本中可以为负.

The problem is that the second argument in VBA's Round function can't be negative, but in the worksheet function's version it can.

解决方案很简单，只需使用:

The solution is simple, just use:

sigfig = Application.WorksheetFunction.Round(val, sigf)

它将按预期工作.

Application.WorksheetFunction使 Round 可用很重要.之所以这样做是因为VBA功能不能完全复制其功能.相比之下， Abs 只会复制该功能，因此您不能从VBA调用该功能的工作表版本.

It is significant that Application.WorksheetFunction makes Round available. It does that precisely because the VBA function does not duplicate its functionality exactly. By contrast, Abs would just duplicate the functionality, so you can't call the worksheet version of that function from VBA.

这篇关于VBA舍入功能与工作表舍入功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！