Circular Error in VBA User defined Function

Question

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"

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.

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

1. Could anyone tell me which step in my script is wrong
2. Is there anyway convenient to test a function? As it is not like a procedure which execute directly by clicking the F5 button

Many thanks for your attention and help.

Answer 1

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

Now run TestMe by pressing F8 and enjoy the debugging.

---

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.

---

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

Answer 2

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

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.

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.

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

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.