VBA用户定义函数中的循环错误

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"

我是VBA编程的新手,并为插值编写了VBA函数。我试图从单元格C17和C列中的其他单元格输入函数“WindPressure”

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.

然后该函数从C列获得输入(高于地面的高度z(m))并进行插值以获得设计风压,但由于圆形误差,我失败了。

The code is as follow:

代码如下:

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

Could anyone tell me which step in my script is wrong

谁能告诉我我的剧本中哪一步是错的

Is there anyway convenient to test a function? As it is not like a procedure which execute directly by clicking the F5 button

有没有方便测试功能?因为它不像是通过单击F5按钮直接执行的过程

Many thanks for your attention and help.

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

2 个解决方案

#1

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

这是如何使用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

Now run TestMe by pressing F8 and enjoy the debugging.

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

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.

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

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

关于错误,从功能中删除选择部件,VBA不允许您在那里使用它。

#2

As @YowE3K has pointed out, debugging doesn't help when the function tries to modify the environment.

正如@ YowE3K指出的那样,当函数尝试修改环境时,调试没有帮助。

So, your code tries to select range T13 which it can't do in a UDF. After this any reference to the ActiveCell is wrong and trying to change the active cell fails as you can't modify the environment.

因此,您的代码尝试选择在UDF中无法执行的范围T13。在此之后,对ActiveCell的任何引用都是错误的,并且尝试更改活动单元失败,因为您无法修改环境。

On a side note, you also haven't put Option Explicit at the top of the module otherwise it would also complain you haven't declared the variable i.

另外,您还没有将Option Explicit放在模块的顶部,否则它也会抱怨您没有声明变量i。

So, rather than change the ActiveCell just set a reference to the cell you need. Whenever you use ActiveCell use that range reference instead.

因此,不要更改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

To check your code you could put a break-point on Range("T13").Select and add a watch on ActiveCell.Address - when the code reaches the line you'll see that the ActiveCell remains the cell that you typed the formula into.

要检查代码,可以在Range(“T13”)上设置一个断点。在ActiveCell.Address上选择并添加一个监视 - 当代码到达该行时,您将看到ActiveCell仍然是您键入公式的单元格成。

#1