在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為搜尋比對的值
不知能不能解決?