UDT in an Add-in



I have an add-in that contains several UDFs which work great. The add-in
also contains another module containing a single UDF that makes use of a UDT.
This function is the only one in the add-in that cannot be called somehow.
I get the #VALUE error.

The code is below. Please note that this code is a combination of my own
work and freely available work by Brian Murphy at www.xlrotor.com.

The module works correctly as is in a regular workbook. The only thing I
can think of is that the UDT is causing the function to error. However, I
tried converting the UDT to a class module with the same results.

Are there any restrictions on using UDTs in add-ins?

Matthew Pfluger

' **************************************************************
' User-defined variables
' **************************************************************

Type XYZ ' Vector variable type
x As Double
y As Double
z As Double
End Type

' **************************************************************
' User-defined enumerations
' **************************************************************

Private Enum BezErr
BezErr_InvalidT ' t is not between 0 and 1 inclusive
BezErr_NotEnoughPoints ' function requires at least 2 points
(linear interpolation)
BezErr_InvalidData ' Data must be in continuous columns of XYZ
End Enum

' Comments: This procedure calculates a 4-control point Bezier
interpolation within a given
' data set.
' Arguments: XYrange The data points where X, Y, & Z are in
' t Interpolation Parameter, varies between
0 and 1
' Returns: Ret Desc
' Date Developer Action
' 10 Jun 03 Brian Murphy Initial version
' 12 Oct 07 Matthew Pfluger Removed reference to chart
' Function interpolates based on data only

Public Function BezInterp(XYrange As Range, t As Range)
On Error GoTo 0

' Check parameter 't'
Dim iErrNum As BezErr
If t < 0 Or t > 1 Then
iErrNum = BezErr_InvalidT
GoTo errorFound
End If

' Declare Variables
Dim iKnots As Integer ' Number of input cells
Dim iCurveNum As Integer ' Curve segment number based on t value and
number of data points
Dim dModT As Double ' Modified t-value
Dim dTInterval As Double ' Range of t to interpolate between
ReDim pts(0 To 3) As XYZ ' The 4 data points (inputs)
ReDim bz(0 To 3) As XYZ ' The 4 control points (define the shape of
the Bezier curve)
Dim uPt As XYZ ' Interpolated Point

' Initiate Variables
iKnots = XYrange.Rows.Count

' Begin Interpolation (code omitted here for now)

' Calculate control points from data points
Call getControlPts(pts, bz)

' Calculate Cubic Bezier-interpolated value
uPt = Bezier4(bz(0), bz(1), bz(2), bz(3), dModT)
BezInterp = Array(uPt.x, uPt.y)

Exit Function

Select Case iErrNum
Case BezErr_InvalidT
BezInterp = "Parameter 't' must be between 0 and 1."
Case BezErr_InvalidData
BezInterp = "Data must be in continuous columns of XYZ format."
Case BezErr_NotEnoughPoints
BezInterp = "Function requires at least 2 points to interpolate."
End Select

End Function

Function CreateXYZ(a, b, Optional c) As XYZ
CreateXYZ.x = a
CreateXYZ.y = b
If Not (IsMissing(c)) Then CreateXYZ.z = c
End Function

Function XYZAdd(a As XYZ, b As XYZ) As XYZ
XYZAdd.x = a.x + b.x
XYZAdd.y = a.y + b.y
XYZAdd.z = a.z + b.z
End Function





Please excuse me. I found my error. In the function call, the inputs must
be ranges, and I was trying to call the function using a range and an
integer, respectively. My mistake.

Matthew Pfluger

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question