Call UDF function that has user defined type variables as parameters from Worksheet

[adulador]

Hi,

UDF functions can be called from a Worksheet, given they are 'Public functions' and are defined inside a Module (not in the Worksheet Code).

But if a UDF function has variables that are defined as user defined type variables it will simply give a #VALUE! error:

For example, if in a cell we have the formula (with the code inside a macro):
= fATanPoints(fXY2Point(1;2);fXY2Point(3;4))
It will return #VALUE!

Instead, calling another UDF function like:
=fATanPointsXY(1;2;3;4)
Will give as answer = 0.553, that is correct.

Public Type POINT
    X As Double
    Y As Double
End Type


Public Function fXY2Point(ByRef X As Double, ByRef Y As Double) As POINT
    Dim TestPoint As POINT
    With TestPoint
        .X = X
        .Y = Y
    End With
    fXY2Point = TestPoint
End Function


Public Function fATanPoints(ByRef Point1 As POINT, ByRef Point2 As POINT) As Double
'Return the Atan of the line between two points (for this example, didn't implemented error traps, as they're used valid values that has no errors)
    fATanPoints = Atn(Point2.Y - Point1.Y) / (Point2.X - Point1.X)
End Function

Public Function fATanPointsXY(ByRef X1 As double, byref Y1 As double, byref X2 As double, byref Y2 As double) As Double
'Return the Atan of the line between two points (for this example, didn't implemented error traps, as they're used valid values that has no errors)
    fATanPointsXY = Atn(Y2 - Y1) / (X2 - X1)
End Function

The question is, how can I call the UDF function fATanPoints that has User Defined Type variables in order to get the same result?
I suppose that in Excel 97 the CALL() function will solve this, but now, I'm stuck.

Thanks in advance.

 

[Jaafar Tribak]

Maybe something like this :

Public Type POINT
    X As Long
    Y As Long
End Type

Private Declare Sub CopyMemory Lib "kernel32" Alias _
"RtlMoveMemory" (pDst As Any, pSrc As Any, ByVal ByteLen As Long)

Public Function fXY2Point(ByRef X As Long, ByRef Y As Long) As Long
    Static i As Long
    Static TestPoint As POINT
    Static TestPoint2 As POINT
    
    If i = 0 Then
        With TestPoint
            .X = X
            .Y = Y
        End With
        fXY2Point = VarPtr(TestPoint)
    Else
        With TestPoint2
            .X = X
            .Y = Y
        End With
        fXY2Point = VarPtr(TestPoint2)
    End If
    i = IIf(i = 1, 0, i + 1)
End Function

Public Function fATanPoints(ByRef Point1Ptr As Long, ByRef Point2Ptr As Long) As Double
    'Return the Atan of the line between two points (for this example, didn't implemented error traps, as they're used valid values that has no errors)
    Dim Point1 As POINT
    Dim Point2 As POINT
    
    CopyMemory ByVal Point1, ByVal Point1Ptr, ByVal LenB(Point1)
    CopyMemory ByVal Point2, ByVal Point2Ptr, ByVal LenB(Point2)
    fATanPoints = Atn(Point2.Y - Point1.Y) / (Point2.X - Point1.X)
End Function


Sub Test()
    MsgBox fATanPoints(fXY2Point(1, 2), fXY2Point(3, 4))
End Sub

 

[adulador]

Jaafar, it is working. Using pointers is a incredible creative way of circunvent this.

Although I had the hope that any other way of doing this exists... as I do not programm declaring pointers. Would have to rethink all my methodology if so.

Thank you so much in pointing this out. Maybe for a simple function like the one posted could not be enough to justify a modification, but for any other objects will be very usefull.