用户定义的函数会给出#VALUE!，但是手工计算可以吗？

Question

我已经创建了一个VBA用户定义函数来计算一些热特性。这个用户定义函数中的函数引用了一个非常大的模块，该模块引用了一个.DLL文件(如果您很好奇，可以使用REFPROP)。

当我把所有东西都插入到我的函数中时，我得到了#VALUE!。但是，如果我执行与工作表中的函数相同的所有步骤，则不会有任何问题，我会得到一个值。当我试图在我的函数中设置断点时，它不会在FluidDensity处停止。该点之前的所有内容都会停止并正确计算。

Option Explicit
Units = "E"
grav = 32.2

    Function DischargePipingLoss_per_FT(FluidName As String, BTUperHR As Double, OutsideDia_copper As String,LiquidTemp As Double, SuctionTemp As Double, Superheat As Double, PipeLossUnits As String, Isentropic_Eff As Double)

            Dim ps As Double
            Dim ts As Double
            Dim hs As Double
            Dim ss As Double
            Dim pd As Double
            Dim hd As Double
            Dim hd_actual As Double
            Dim td As Double
            Dim Re As Double, friction As Double
            Dim DynamicVisc As Double
            Dim KinematicViscosity_FT2perSEC As Double
            Dim InsideDia_FT As Double, PressureDrop_ft As Double
            Dim Velocity_FPS
            Dim BTUperLB As Double
            Dim LBperHR As Double
            Dim FT3perHR As Double
            Dim FluidDensity As Double
            Dim SuctionPressure As Double
            Dim ReturnGasTemp As Double
            Dim PSID_per_FT As Double

            'Determine suction pressure and temp
                ps = Pressure(FluidName, "Tvap", Units, SuctionTemp)
                ts = SuctionTemp + Superheat

            'Determine liquid enthalpy, suction gas enthalpy
                'hf = Enthalpy(FluidName, "PT", Units, HighPressure, GasCoolerOutletTemp)
                hs = VaporEnthalpy(FluidName, "PT", Units, ps, ts)

            'Determine return gas / discharge gas entropy
                ss = VaporEntropy(FluidName, "PT", Units, ps, ts)

            'Determine discharge pressure
                pd = Pressure(FluidName, "Tvap", Units, LiquidTemp)

            'Determine isentropic discharge gas enthalpy
                hd = Enthalpy(FluidName, "PS", Units, pd, ss)

            'Determine actual discharge gas enthalpy
                hd_actual = (hd - hs) / Isentropic_Eff + hs

            'Determine discharge temperature (uncomment if wanted)
                td = Temperature(FluidName, "PH", Units, pd, hd_actual)

            'calculate mass flowrate
                BTUperLB = RefrigerationEffect(FluidName, Units, LiquidTemp, SuctionTemp, Superheat)
                LBperHR = BTUperHR / BTUperLB

            'calculate fluid transport properties
                FluidDensity = Density(FluidName, "PH", Units, pd, hd_actual)
                DynamicVisc = Viscosity(FluidName, "PH", Units, pd, hd_actual)

           'calculate volume flowrate and velocity
                FT3perHR = LBperHR / FluidDensity
                InsideDia_FT = get_dia(OutsideDia_copper) / 12
                Velocity_FPS = FT3perHR / (3.14 * (InsideDia_FT / 2) ^ 2) / 3600

            'retrieve friction factor and calculate pressure drop (ft per ft)

                KinematicViscosity_FT2perSEC = DynamicVisc / FluidDensity
                PressureDrop_ft = FrictionFactor(Velocity_FPS, InsideDia_FT, KinematicViscosity_FT2perSEC) * (1 / InsideDia_FT) * (Velocity_FPS ^ 2 / (2 * grav))

            'convert pressure drop to psi per ft
            PSID_per_FT = PressureDrop_ft * FluidDensity / 12 ^ 2

            If PipeLossUnits = "PSI" Then
                DischargePipingLoss_per_FT = PSID_per_FT
            ElseIf PipeLossUnits = "degF" Then
                DischargePipingLoss_per_FT = deltaT(FluidName, td, PSID_per_FT)
            Else: DischargePipingLoss_per_FT = "error"
            End If

        End Function

Answer 1

当UDF函数试图更改Microsoft Excel的环境(请参见黄色方块)时，#VALUE是Excel VBA的标准答案。

检查这个小函数：

Public Function Example() As Long
    'Range("A1") = 10
    Example = 5
End Function

如果您在Excel中像这样调用它的=Example，它将返回5。

然后，如果您取消注释Range("A1")=10，您将看到出现#VALUE错误。因此，长话短说-你的函数(或它调用的函数)显然在做一些非法的事情。试着找出它是什么，并消除它。

由工作表单元格中的公式调用的用户定义函数不能更改Microsoft Excel的环境。这意味着这样的函数不能执行以下任何操作：

spreadsheet.

• Change

• 在workbook.

• Set上插入、删除或设置单元格的格式另一个单元格的值。
• 向workbook.
• Set移动、重命名、删除或添加工作表任何环境选项，如计算模式或屏幕视图。
• 向workbook.
• Set属性添加名称或执行大多数方法。

support.microsoft.com - Description of limitations of custom functions in Excel