时间戳

首页 > 解决方案 > ＃价值！包含成功编译的 VBA 函数的单元格出错

excel - ＃价值！包含成功编译的 VBA 函数的单元格出错

问题描述

我正在使用 VBA 集成到 Excel 中。我在工作表上的有组织的表格中具有集成过程所需的大量变量，这些变量将根据需要进行更改。我编写了以下代码来完成此操作，并成功调试了所述代码，使其编译时没有错误。

Dim dIdt As Range
Dim Isnp As Range
Dim Tau As Range

Dim A As Range
Dim B As Range
Dim C As Range
Dim D As Range


Function Iscr(t)
Set dIdt = Worksheets("Sheet1").Range("F11").Value
Set Isnp = Worksheets("Sheet1").Range("F14").Value
Set Tau = Worksheets("Sheet1").Range("F13").Value
Iscr = (dIdt * t) + (Isnp * (Exp((-t) / Tau)))
End Function

Function Econd1A(t)
Set A = Worksheets("Sheet1").Range("B17").Value
Set B = Worksheets("Sheet1").Range("B18").Value
Set C = Worksheets("Sheet1").Range("B19").Value
Set D = Worksheets("Sheet1").Range("B20").Value
Econd1A = A + (B * (Log(Iscr(t)))) + (C * (Iscr(t))) + (D * (Iscr(t) ^ (1 / 2)) * Iscr(t))
End Function

Function Econd1(x0, t1, t2)

'define range of integral
int_range = t2 - t1

'discretize the integral into n slices dt wide
n = 1000
dt = int_range / n

'initialize variables
ta = t1
tb = ta + dt
Econd1 = x0

'calculate areas using trapezoidal rule

'sum area under curve of each slice
For j = 1 To n
Econd1 = Econd1 + (tb - ta) * (Econd1A(ta) + Econd1A(tb)) / 2
ta = tb
tb = ta + dt
Next

End Function

我现在在调试方面不知所措，因为该错误似乎与 VBA 无关。

单元格的预期输出为 .0127，但它却给出了 #VALUE！错误。

标签： excelvbaintegration

解决方案

Function Iscr(ByVal t As Double) As Double
    Dim dIdt As Double, Isnp As Double, Tau As Double
        dIdt = Worksheets("Sheet1").Range("F11").Value
        Isnp = Worksheets("Sheet1").Range("F14").Value
        Tau = Worksheets("Sheet1").Range("F13").Value
        Iscr = (dIdt * t) + (Isnp * (Exp((-t) / Tau)))
End Function

Function Econd1A(ByVal t As Double) As Double
    Dim A As Double, B As Double, C As Double, D As Double
        A = Worksheets("Sheet1").Range("B17").Value
        B = Worksheets("Sheet1").Range("B18").Value
        C = Worksheets("Sheet1").Range("B19").Value
        D = Worksheets("Sheet1").Range("B20").Value
        Econd1A = A + (B * (Log(Iscr(t)))) + (C * (Iscr(t))) + (D * (Iscr(t) ^ (1 / 2)) * Iscr(t))
End Function

Function Econd1(ByVal x0 As Double, ByVal t1 As Double, ByVal t2 As Double) As Double
Dim int_range As Integer
    int_range = t2 - t1
    n = 1000
    dt = int_range / n
    ta = t1
    tb = ta + dt
    Econd1 = x0
        For j = 1 To n
            sEcond1 = sEcond1 + (tb - ta) * (Econd1A(ta) + Econd1A(tb)) / 2
            ta = tb
            tb = ta + dt
        Next j
Econd1 = sEcond1
End Function

推荐阅读