在Excel VBA中使用自訂函數?

[motor712]

各位大大：
以下是在excel中，使用函數的公式列。但是我現在的需求是sheet需從VT1延伸至VT160。
這樣會造成程式太長，而無法編輯。請問如何利用VBA？以下是我的想法，但計算出來的值不對。

在excel中
=AVERAGE(HLOOKUP(C2,VT1!$D$3:$CR$23,21,0),HLOOKUP(C2,VT2!$D$3:$CR$23,21,0),HLOOKUP(C2,VT3!$D$3:$CR$23,21,0))

在excel VBA中
Function AVGSheets(sheet_index As Integer)

For i = 1 To sheet_index
Application.Average (Application.HLookup(Sheets("Summary").Range("D2"), _
Sheets("VT" & i).Range("D3:CR23"), 21, 0))
Next i

End Function

[沙拉油]

試試

程式碼:

Function AVGSheets(lookup_value As Range, table_array As String, row_index_num As Integer, sheets_name As String, sheet_index As Integer) As Double
　　Dim mysum As Double, i As Integer, n As Integer
　　Dim rng As Range, row1 As Range
　　
　　For i = 1 To sheet_index
　　　　Set row1 = Sheets(sheets_name & i).Range(table_array).Rows(1)
　　　　Set rng = row1.Find(what:=lookup_value, lookat:=xlWhole)
　　　　If Not rng Is Nothing Then
　　　　　　Set rng = rng.Offset(row_index_num - 1, 0)
　　　　　　n = n + 1
　　　　　　mysum = rng + mysum
　　　　End If
　　Next i
　　
　　AVGSheets = mysum / n
End Function

引數 lookup_value 是要搜尋比對的值（如您上述公式 Hlookup 內的C2）
引數 table_array 是被搜尋比對儲存格位址（如您上述公式 Hlookup 內的 $D$3:$CR$23 ）此引述必須傳入字串值
引數 row_index_num 是回傳值，如同 Hlookup 的第3個引數
引數 sheets_name 是工作表的名稱
引數 sheet_index 是工作表個數

以您上述的公式引用此自訂函數，應該是
AVGSheets(C2, "$D$3:$CR$3",21, "VT", 160)
試試看吧！沒詳細測試。

[motor712]

天啊，你真的是太厲害了！程式都沒問題耶！感恩啦！
我已經買了VBA的書了，我要向你看齊...

我還有一個問題，'自訂函數'好像不會自己更新，對嗎？
一般的函數，只要我們將函數內所指向的值刪除，那我們寫的函數的儲存格的值就會不見(公式不見)。

但自訂函數的部分，似乎就不會自動更新，要點入公式內再確認一次。
請問是否有'!Refresh'的功能呢？

[leonchou]

儲存格的公式不會因參照內容不見而消失，除非手動刪除公式。

更新的問題可使用 Volatile 。以下的範例
只要包含此函數的工作表上任何儲存格變動或
公式引發重算，此函數也會重新計算。

Function My_Func()
　　Application.Volatile
　　'
　　' 自訂函數的內容
　　'
End Function

[motor712]

喔！抱歉，我寫錯了，我的意思是有公式的儲存格不會自動更新。
除非你再次編輯程式。

先問一下volaitle是excel的內建函數嗎？不過，我找不到。
如果不是，為何這樣簡單的程式，就可達到我要的效果。

我的想法是...按一個按鈕後，所有資料自動更新。

[oobird]

Sub 按鈕1_Click()
　　ActiveCell.FormulaR1C1 = ""
End Sub
不過也不須要這麼麻煩，直接在任一公式所在儲存格快按兩下就達到重算的目的了。

[leonchou]

Volatile 是屬於 Application 物件的方法，不是函數。

如何查詢VBA輔助說明?
在VBA編輯視窗下，將游標停在欲查詢的關鍵字上(此例為Volatile)，按下 F1 鍵。
Volatile 方法

用於將使用者自訂函數記號為揮發性函數，無論何時在工作表的任意儲存格中進行計算，揮發性函數都必須重新進行計算。非揮發性函數在輸入變數改變時才重新計算，若不用於計算工作表儲存格的使用者自訂函數中，則此方法無效。

語法
expression.Volatile(Volatile)

expression　必要的運算式。該運算式傳回一個 Application 物件。

Volatile　選擇性的 Variant 資料型態。如果為 True 則將該函數記號為揮發性的；如果為 False 則將該函數記號為非揮發性的。預設值為 True。

[P0573]

試試

程式碼:

Function AVGSheets(lookup_value As Range, table_array As String, row_index_num As Integer, sheets_name As String, sheet_index As Integer) As Double
　　Dim mysum As Double, i As Integer, n As Integer
　　Dim rng As Range, row1 As Range
　　
　　For i = 1 To sheet_index
　　　　Set row1 = Sheets(sheets_name & i).Range(table_array).Rows(1)
　　　　Set rng = row1.Find(what:=lookup_value, lookat:=xlWhole)
　　　　If Not rng Is Nothing Then
　　　　　　Set rng = rng.Offset(row_index_num - 1, 0)
　　　　　　n = n + 1
　　　　　　mysum = rng + mysum
　　　　End If
　　Next i
　　
　　AVGSheets = mysum / n
End Function

引數 lookup_value 是要搜尋比對的值（如您上述公式 Hlookup 內的C2）
引數 table_array 是被搜尋比對儲存格位址（如您上述公式 Hlookup 內的 $D$3:$CR$23 ）此引述必須傳入字串值
引數 row_index_num 是回傳值，如同 Hlookup 的第3個引數
引數 sheets_name 是工作表的名稱
引數 sheet_index 是工作表個數

以您上述的公式引用此自訂函數，應該是
AVGSheets(C2, "$D$3:$CR$3",21, "VT", 160)
試試看吧！沒詳細測試。

請問一下,如果是被搜尋比對的值不是儲存格,而是程式產出的值丟到陣列變數中
那要怎麼寫,因為陣列不是物件,不能被set

-->Set rng = Y.Find(what:=Yield, lookat:=xlWhole)
若Application.VLookup(Yield, Y, 1, 1)又會出現錯誤
Y為搜尋比對的範圍,陣列變數
Yield為搜尋比對的值
不知能不能解決?

[leonchou]

陣列(元素)不是不能Set，要看陣列變數宣告的型態是什麼。
陣列有陣列的處理方式，當內建函數不支援陣列時，
就用陣列的方式處理。相關參考：
http://gb.twbts.com/index.php/topic,510.0.html
http://gb.twbts.com/index.php/topic,1130.0.html

因為你問的很簡略，所以別人也只能回答概念的東西。
如果能把你的需求和程式寫詳細一點，
也許會有更好的解決方案。

[P0573]

原來陣列可以用Dictionary物件的方法Set,
後來我用別的方式解決了,但是方法很笨
附上我原本的問題,下次可以用leonchou兄的方法試試,謝謝你提供的information~

有一公式
Yield=[1/(1+D0*A_logic)^n2]*[1/(1+ D0*n1*A_memory)^n2]
已知A_logic,A_menory,n1,n2,Yield數值,求D0
做法是先預設一組D0值(例:於A1:A20輸入0.08,0.09,0.01...........)
然後套入公式,求最接近的值

D0公式,求D0
D0(A_logic,A_memory,n1,n2,Yield,D0_range)
D0公式,求Yield(驗算用)
D0Yield(A_logic,A_memory,n1,n2,Yield,D0_range)


Function D0(A_logic, A_menory, n1, n2, Yield As Range, D0_Range As Range) As Double

Dim Y(30) As Double
Dim Y_Count As Integer
Dim TempValue As Double
Y_Count = 0

Y_Count = D0_Range.Count

For i = 1 To Y_Count

　　TempValue = (1 / (1 + D0_Range(i) * A_logic) ^ n2) * (1 / (1 + D0_Range(i) * n1 * A_menory) ^ n2)
　　Y(i - 1) = Mid(TempValue, 1, 10)
Next

'dD0=Y.Find(what:=Yidle,lookat:=xlWhole　
'原本想用Find或Vlookup解決最後想不出來還是用最笨的方法找

For i = 0 To Y_Count - 1
　　If Yield < Y(i) And Yield > Y(i + 1) Then
　　　 D0 = D0_Range(i + 2)
　　End If
Next
 
End Function
Function D0Yield(A_logic As Range, A_menory As Range, n1 As Range, n2 As Range, D0_Value As Range) As Double

D0Yield = (1 / (1 + D0_Value * A_logic) ^ n2) * (1 / (1 + D0_Value * n1 * A_menory) ^ n2)

End Function

順便修正我原先的錯誤觀念
之前寫的
Application.VLookup(Yield, Y, 1, 1)是錯的方法
在VBA中，要使用excel函數可透過 WorksheetFunction 物件呼叫
所以要寫成
WorksheetFunction.VLookup(Yield, Y, 1, 1)