M
Matthew Pfluger
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
format
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
columns
' 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
errorFound:
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
....etc...
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
format
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
columns
' 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
errorFound:
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
....etc...