Call VBA function that returns custom type from spreadsheet

2
votes

I have a vba function that returns a custom data type, defined as:

Public Type stockValue
        stock As String
        value As Double
End Type

My question is how do I deal with this when i call the function from a spreadsheet cell ? for example, say i want the cell do display the stock value, i tried =function().stock and it doesn't work

any help is appreciated, thanks !

Function getLowestPnl(strat As String, rank As Integer) As stockValue

    Call Conecta_DB(conexao)
    Set registros = New ADODB.Recordset

    strSQL = "SELECT stock,sum([value]) FROM Reports.dbo.Entry WHERE idStrategy='" & strat & "' and idType=1 GROUP BY stock  ORDER BY sum([value])"
    'strSQL = "SELECT [finance],[sales],[management],[research],[administration] FROM [COS].[dbo].[Complementarity] WHERE [idCompany] =" & idCompany & " and [year]=" & year & " and [CEO]=1"

    registros.Open strSQL, conexao, adOpenStatic, adLockOptimistic

    parar = False
    If Not registros.EOF Then

        x = registros.GetRows()
        i = 0
        Do While parar <> True

            If i = (rank - 1) Then
                getLargestShortExp.stock = Trim(x(0, i))
                getLargestShortExp.value = x(1, i)
                parar = True
            End If
            i = i + 1
        Loop

    End If

    registros.Close
    getLowestPnl = ret
End Function
2
vbaexcelspreadsheet
Show the rest of your code – Ron Rosenfeld
What Charles Williams wrote. Although VBA can understand User Defined Types, Excel cannot. Another approach would be to use an event-triggered macro to place the results next to the relevant arguments. – Ron Rosenfeld

2 Answers

4
votes

You can only return data types that Excel understands from a user-defined function. Excel does not understand custom data types.

Instead you would have to return a variant array containing the 2 values from your custom data type. Then either you would array enter the function into 2 cells, or use another function such as INDEX to retrieve the value you wanted from the returned array.

0
votes

You can do the following : 

Type XYDouble
    X As Double
    Y As Double
End Type

Private Function Compute(ByVal X As Double, ByVal Y As Double) As XYDouble
    Compute.X = ....
    Compute.Y = ...
End Function


Function ComputeX(ByVal X As Double, ByVal Y As Double) As Double
    ComputeX = Compute(X, Y).X
End Function

Function ComputeY(ByVal X As Double, ByVal Y As Double) As Double
    ComputeY = Compute(X, Y).Y
End Function

and, you can get the X (or Y) value in cell with the formula : "=ComputeX(C7;D7)"