VBA用户定义功能中的循环错误 [英] Circular Error in VBA User defined Function

问题描述

我是VBA编程的新手，并且编写了用于插值的VBA函数. 我尝试从单元格C17和列C中的其他单元格获取输入到函数"WindPressure"

I am new to the VBA programming and have written a VBA function for interpolation. I attempt to get the input from cell C17 and other cell in column Cto the function "WindPressure"

然后该函数从C列(地上高度z(m))获取输入并进行插值以获取设计风压，但是由于圆误差，我失败了.

The function then gets the input from column C( Height above ground z (m) ) and interpolate to get the design wind pressure, but I fail due to the circular error.

代码如下:

Function WindPressure(z As Double) As Double

      Dim Row_Nos As Integer

      Dim x1 As Double
      Dim x2 As Double
      Dim x3 As Double

      Dim y1 As Double
      Dim y2 As Double
      Dim y3 As Double

      ' Select first line of data.
      Range("T13").Select

      ' Set Nos of row to interploate
      Row_Nos = 12

      ' Interpolation for Design Wind pressure
      For i = 0 To Row_Nos

         ' Case 1: <= 5m
         If i = 0 And z <= ActiveCell.Value Then

            WindPressure = ActiveCell.Offset(0, 1).Value

            ' Shifting Back
            ActiveCell.Offset(0, -1).Select

            'Exit If Enter this if statement
            Exit Function

         ElseIf i >= 0 And z <= ActiveCell.Value Then


         ' Case 2: > 5m
            x1 = z

            x2 = ActiveCell.Offset(-1, 0).Value

            x3 = ActiveCell.Offset(2, 0).Value

            y2 = ActiveCell.Offset(-2, 1).Value

            y3 = ActiveCell.Offset(2, 0).Value

            y1 = ((x1 - x3) / (x2 - x3) * (y2 - y3)) + y3
            WindPressure = y1

            ' Shifting Back
            ActiveCell.Offset(-1, -1).Select

            'Exit If Enter this if statement
            Exit Function

         End If
            ActiveCell.Offset(1, 0).Select
      Next i


End Function

1. 有人可以告诉我脚本中的哪一步是错误的
2. 总有没有方便的功能测试?因为它不像单击F5按钮直接执行的过程

非常感谢您的关注和帮助.

Many thanks for your attention and help.

推荐答案

这是使用 F5 或 F8 作为过程对其进行调试的方法:

This is how to debug it with F5 or F8 as a procedure:

Public Sub TestMe()

    Debug.Print windpressure(7)

End Sub

Function windpressure(z As Double) As Double

    Stop
    'the rest of the function
'    Dim Row_Nos As Integer
'    Dim x1 As Double
'    Dim x2 As Double
'    Dim x3 As Double

End Function

现在通过按 F8 运行TestMe并进行调试.

Now run TestMe by pressing F8 and enjoy the debugging.

另一种方法是在立即窗口中写入?WindPressure(7)，并在VB编辑器中放置一个停止符号.它将逐行.

Another way is to write ?WindPressure(7) in the immediate window and to put a stop sign in the VB Editor. It will go line by line.

关于错误，请从功能中删除Select部分，VBA不允许您在其中使用它.

Concerning the errors, remove the Select part from the function, VBA does not allow you to use it there.

这篇关于VBA用户定义功能中的循环错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！