自定義函數如何賦值給儲存格
自定義函數內如何賦值給儲存格,感恩. [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121259&ptid=24000]1#[/url] [i]dou10801[/i] [/b]謝謝前輩發表此主題與範例
後學研習自訂函數,測試方案如下,前輩參考
儲存格函數設定:
[attach]36413[/attach]
VBA:
[attach]36414[/attach] [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121264&ptid=24000]2#[/url] [i]Andy2483[/i] [/b]請教前輩,是否能將result[參數],傳到[A1]儲存格,謝謝. [i=s] 本帖最後由 Andy2483 於 2023-5-23 16:37 編輯 [/i]
[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121265&ptid=24000]3#[/url] [i]dou10801[/i] [/b]
謝謝前輩回復
自訂函數好像只會給自己值,後學也是初學,前輩請參考以下的應用,看看適不適合
[attach]36416[/attach] [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121266&ptid=24000]4#[/url] [i]Andy2483[/i] [/b]
c1公式,d1顯示
[attach]36417[/attach]
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 [i=s] 本帖最後由 Andy2483 於 2023-5-24 08:23 編輯 [/i]
[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121267&ptid=24000]5#[/url] [i]quickfixer[/i] [/b]
[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121265&ptid=24000]3#[/url] [i]dou10801[/i] [/b]
謝謝二位前輩
後學藉此帖學習到很多知識,懵懵懂懂學習心得如下,請前輩再指導
[attach]36418[/attach]
Option Explicit
Function MyCustomFunction(ByVal arg1 As Integer, ByVal arg2 As Integer) As String
[color=SeaGreen]'↑自訂函數:MyCustomFunction(宣告arg1是 長整數 ,宣告arg2是 長整數)
'↑因為[B2]本身自訂函數格 過程給[A1]值,而[B2]最終值是空白,所以不能是As Integer
'可以刪掉 As Integer變成通用型變數,或換宣告是 As String[/color]
Evaluate "test1(" & Application.Caller.Offset(-1, -1).Address(0, 0) & "," & arg1 & "," & arg2 & ")"
[color=SeaGreen]'↑回傳Evaluate "~~~" 值
'arg1變數傳遞給 a變數
'arg2變數傳遞給 b變數[/color]
MyCustomFunction = ""
[color=SeaGreen]'↑令這自訂函數結果是空白[/color]
End Function
Function test1(c As Range, a As Integer, b As Integer)
c = a + b
[color=SeaGreen]'↑a + b計算結果傳遞給c變數(儲存格)[/color]
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
===============================================
以下是學習網頁
[url]https://learn.microsoft.com/zh-tw/office/vba/api/excel.application.Evaluate[/url]
[url]https://learn.microsoft.com/zh-tw/office/vba/api/excel.application.Caller[/url] [i=s] 本帖最後由 Andy2483 於 2023-5-24 11:28 編輯 [/i]
[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121268&ptid=24000]6#[/url] [i]Andy2483[/i] [/b]
更正:
Function MyCustomFunction(ByVal arg1 As Integer, ByVal arg2 As Integer) As String
[color=SeaGreen]'↑自訂函數:MyCustomFunction(宣告arg1是 [b]短整數[/b] ,宣告arg2是 [b]短整數[/b])結果值是字串 [/color]
% -integer(整數資料類型_-32,768 到 32,767)
& -long(長整數_-2,147,483,648 到 2,147,483,647) [i=s] 本帖最後由 dou10801 於 2023-5-24 11:43 編輯 [/i]
[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121268&ptid=24000]6#[/url] [i]Andy2483[/i] [/b]感謝:quickfixer,Andy2483 兩位前輩指點,如果,要傳遞給[A1]的是一個字串,要如何處理,感恩. [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121272&ptid=24000]8#[/url] [i]dou10801[/i] [/b]
謝謝前輩回復
後學將結果變化如下,請參考
執行結果:
[attach]36423[/attach]
Function test1(c As Range, a As Integer, b As Integer)
c = "'" & a & b
End Function [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121274&ptid=24000]9#[/url] [i]Andy2483[/i] [/b]
非常感謝回覆,試著用字串,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 [i=s] 本帖最後由 Andy2483 於 2023-5-24 15:26 編輯 [/i]
[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121275&ptid=24000]10#[/url] [i]dou10801[/i] [/b]
謝謝前輩再回復
後學研究如下方案,請前輩參考
執行結果:
[attach]36424[/attach]
Option Explicit
Function MyCustomFunction(ByVal arg1 As String, ByVal arg2 As String) As String
Evaluate "test1(" & Application.Caller.Offset(-1, -1).Address(0, 0) & ","[b][color=Red]""[/color][/b] & arg1 & [b][color=Red]""[/color][/b]","[b][color=Red]""[/color][/b] & arg2 & [b][color=Red]""[/color][/b]")"
MyCustomFunction = ""
End Function
Sub test1(c As Range, a As [b]String[/b], b As [b]String[/b])
c = a & b
End Sub [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121275&ptid=24000]10#[/url] [i]dou10801[/i] [/b]
請前輩參考這帖:
[url]http://forum.twbts.com/viewthread.php?tid=7533&highlight=%C2%F9%A4%DE%B8%B9%AA%BA%B0%DD%C3D[/url] [i=s] 本帖最後由 dou10801 於 2023-5-25 11:48 編輯 [/i]
[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121266&ptid=24000]4#[/url] [i]Andy2483[/i] [/b]
回覆 5# quickfixer
延伸學習,是否可將[傳遞參數],傳至[其他工作表],感恩. [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121290&ptid=24000]13#[/url] [i]dou10801[/i] [/b]
謝謝前輩繼續一起學習
後學研習前輩的需求情境,學習方案如下,請前輩參考
Option Explicit
Function MyCustomFunction(ByVal arg1 As String, ByVal arg2 As String) As String
[b]Evaluate "test1(工作表1!A1,""" & arg1 & """,""" & arg2 & """)"[/b]
MyCustomFunction = ""
End Function
Function test1(c As Range, a As String, b As String)
c = a & b
End Function [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121295&ptid=24000]14#[/url] [i]Andy2483[/i] [/b]
感恩指導,上了一課[參數傳遞],謝謝. Function MyCustomFunction(arg1, arg2, V%) '[color=blue]自訂函數只給結果值, 不能指定存放位置[/color]
Dim result
If V = 1 Then result = Val(arg1) + Val(arg2)
If V = 2 Then result = arg1 & arg2
MyCustomFunction = result
End Function
Sub TEST() [color=blue]'給自訂函數[參數],再指定填入位置, 須按鈕執行
[/color]Sheets("工作表2").[B2] = MyCustomFunction(100, 200, 1)
Sheets("工作表2").[B3] = MyCustomFunction("我的自訂", "函數", 2)
End Sub [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121303&ptid=24000]16#[/url] [i]准提部林[/i] [/b]
謝謝論壇,謝謝前輩指導
後學藉此帖學習前輩的方案,方案學習心得如下,請前輩再指導
執行結果:
[attach]36439[/attach]
Option Explicit
Function MyCustomFunction(arg1, arg2, V%) [color=SeaGreen]'自訂函數只給結果值, 不能指定存放位置[/color]
[color=SeaGreen]'↑自訂函數MyCustomFunction(),(arg1,arg2)宣告為通用型變數,V是短整數[/color]
Dim result
[color=SeaGreen]'↑宣告變數:result是通用型變數[/color]
If V = 1 Then result = Val(arg1) + Val(arg2)
[color=SeaGreen]'↑如果V變數是 1,就令result變數是 兩變數各轉為數值後相加值[/color]
If V = 2 Then result = arg1 & arg2
[color=SeaGreen]'↑如果V變數是 2,就令result變數是 兩變數相連接後的新字串[/color]
MyCustomFunction = result
[color=SeaGreen]'↑令MyCustomFunction函數回傳 result變數值[/color]
End Function
Sub TEST()[color=SeaGreen] '給自訂函數[參數],再指定填入位置, 須按鈕執行[/color]
Sheets("工作表2").[B2] = MyCustomFunction(100, 200, 1)
[color=SeaGreen]'↑令表2.[B2]儲存格值是 以arg1=100,arg2=200,V=1,
'給MyCustomFunction()函數回傳值[/color]
Sheets("工作表2").[B3] = MyCustomFunction("我的自訂", "函數", 2)
[color=SeaGreen]'↑令表2.[B3]儲存格值是 以arg1="我的自訂",arg2="函數",V=1,
'給MyCustomFunction()函數回傳值[/color]
End Sub [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121303&ptid=24000]16#[/url] [i]准提部林[/i] [/b]
感恩,多一種學習方式.
頁:
[1]