[發問] 自定義函數如何賦值給儲存格

dou10801

自定義函數內如何賦值給儲存格,感恩.

Andy2483

回復 1# dou10801


    謝謝前輩發表此主題與範例
後學研習自訂函數,測試方案如下,前輩參考

儲存格函數設定:

下載 (24.28 KB)

2023-5-23 15:33

VBA:

下載 (55.08 KB)

2023-5-23 15:34

dou10801

回復 2# Andy2483 請教前輩,是否能將result[參數],傳到[A1]儲存格,謝謝.

Andy2483

回復 3# dou10801


    謝謝前輩回復
自訂函數好像只會給自己值,後學也是初學,前輩請參考以下的應用,看看適不適合

下載 (21.09 KB)

2023-5-23 16:37

quickfixer

回復 4# Andy2483

c1公式,d1顯示

   

下載 (6.86 KB)

2023-5-24 06:39

Function test(a As Integer, b As Integer)
    Evaluate "test1(" & Application.Caller.Offset(0, 1).Address(0, 0) & "," & a & "," & b & ")"
    test = ""
End Function


Sub test1(c As Range, a As Integer, b As Integer)
    c = a + b
End Sub

Andy2483

回復 5# quickfixer
回復 3# dou10801

謝謝二位前輩
後學藉此帖學習到很多知識,懵懵懂懂學習心得如下,請前輩再指導

下載 (20.14 KB)

2023-5-24 08:18

Option Explicit
Function MyCustomFunction(ByVal arg1 As Integer, ByVal arg2 As Integer) As String
'↑自訂函數:MyCustomFunction(宣告arg1是 長整數 ,宣告arg2是 長整數)
'↑因為[B2]本身自訂函數格 過程給[A1]值,而[B2]最終值是空白,所以不能是As Integer
'可以刪掉 As Integer變成通用型變數,或換宣告是 As String
Evaluate "test1(" & Application.Caller.Offset(-1, -1).Address(0, 0) & "," & arg1 & "," & arg2 & ")"
'↑回傳Evaluate "~~~" 值
'arg1變數傳遞給 a變數
'arg2變數傳遞給 b變數
MyCustomFunction = ""
'↑令這自訂函數結果是空白
End Function

Function test1(c As Range, a As Integer, b As Integer)
c = a + b
'↑a + b計算結果傳遞給c變數(儲存格)
End Function

Sub 儲存格位址()
MsgBox [A1].Address(0, 0)
MsgBox [A1].Address(1, 0)
MsgBox [A1].Address(0, 1)
MsgBox [A1].Address(1, 1)
End Sub
===============================================
以下是學習網頁
https://learn.microsoft.com/zh-t ... pplication.Evaluate
https://learn.microsoft.com/zh-t ... .application.Caller

Andy2483

回復 6# Andy2483


    更正:
Function MyCustomFunction(ByVal arg1 As Integer, ByVal arg2 As Integer) As String
'↑自訂函數:MyCustomFunction(宣告arg1是 短整數 ,宣告arg2是 短整數)結果值是字串

% -integer(整數資料類型_-32,768 到 32,767)
& -long(長整數_-2,147,483,648 到 2,147,483,647)

dou10801

回復 6# Andy2483 感謝:quickfixer,Andy2483 兩位前輩指點,如果,要傳遞給[A1]的是一個字串,要如何處理,感恩.

Andy2483

回復 8# dou10801


    謝謝前輩回復
後學將結果變化如下,請參考

執行結果:

下載 (19.82 KB)

2023-5-24 12:44

Function test1(c As Range, a As Integer, b As Integer)
c = "'" & a & b
End Function

dou10801

回復 9# Andy2483
非常感謝回覆,試著用字串,MyCustomFunction("水果","蔬菜"),如何處理,謝謝.
Option Explicit
Function MyCustomFunction(ByVal arg1 As String, ByVal arg2 As String) As String
Evaluate "test1(" & Application.Caller.Offset(-1, -1).Address(0, 0) & "," & arg1 & "," & arg2 & ")"
MyCustomFunction = ""
End Function

Function test1(c As Range, a As Range, b As Range)
c = "'" & a & b
End Function