调用从电子表格返回自定义类型的VBA函数 [英] Call VBA function that returns custom type from spreadsheet
问题描述
我有一个vba函数返回自定义数据类型,定义如下:
I have a vba function that returns a custom data type, defined as:
Public Type stockValue
stock As String
value As Double
End Type
我的问题是如何当我从电子表格单元格调用函数时处理这个问题?例如,我想要单元格显示股票
值,我试过 = function()。stock
不起作用
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
推荐答案
只返回Excel从用户定义的函数中理解的数据类型。 Excel不了解自定义数据类型。
You can only return data types that Excel understands from a user-defined function. Excel does not understand custom data types.
相反,您必须从自定义数据类型返回包含2个值的变体数组。然后你可以将数组输入到2个单元格中,或使用其他函数(如INDEX)从返回的数组中检索所需的值。
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.
这篇关于调用从电子表格返回自定义类型的VBA函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!