在VBA用户定义的功能的通知错误(Circular Error in VBA User define

我是新来的VBA编程和已经写了内插的VBA函数。我尝试从小区C17和其他细胞输入列CTO功能“WindPressure”

然后，该函数获取从柱C（（M）高于地面的Z高度）输入和内插，以获得设计的风压，但是我失败由于圆误差。

其代码如下：

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

谁能告诉我这一步在我的剧本是错误的
反正是有方便的测试功能？因为它是不喜欢它通过点击按钮F5直接执行的过程

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

Answer 1:

这是如何使用F5或F8作为一个程序对其进行调试：

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

现在运行TestMe按F8，享受调试。

另一种方式是写?WindPressure(7)在即时窗口，并把停车标志在VB编辑器。它将由线走线。

关于错误，删除Select从功能部件，VBA不允许你有使用它。

Answer 2:

正如@ YowE3K指出，当函数试图修改环境调试于事无补。

所以，你的代码试图选择范围T13 ，它不能在UDF做。在此之后的任何参考ActiveCell是错误的，并试图改变，因为你不能改变环境的活动单元格失败。

在一个侧面说明，你还没有把Option Explicit在模块的顶部，否则也会抱怨你没有声明的变量i 。

因此，而不是改变ActiveCell正好被设置为你所需要的单元格的引用。当你使用ActiveCell使用范围参考代替。

Option Explicit

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

      Dim i As Long
      Dim rT13 As Range

      ' Select first line of data.
      'Range("T13").Select '**** Won't work in a UDF ****
      Set rT13 = Range("T13") 'Set a reference to T13 instead.


      ' 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 <= rT13.Value Then

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

            'Exit If Enter this if statement
            Exit Function

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


         ' Case 2: > 5m
            x1 = z

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

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

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

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

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

            'Exit If Enter this if statement
            Exit Function

         End If
      Next i

End Function

要检查你的代码，你可以把一个破发点的Range("T13").Select并添加手表ActiveCell.Address -当代码到达行，你会看到ActiveCell保留您键入的公式的单元格成。

文章来源: Circular Error in VBA User defined Function