麻辣家族討論版版 - Powered by Discuz! Board

標題: 呼叫自訂函數 [打印本頁]

作者: leiru 時間: 2015-3-17 23:32 標題: 呼叫自訂函數

我有一個程式碼，如下
請問我要如何呼叫自訂函數
我選公式＞插入函數，找不到使用者定義函數，要請教如何呼叫自訂函數，
可否寫步驟出來，感謝

Function AUTO_CATEGORY(A, B, C)

Dim result_start, result_make, result1, result2, result3, result4, result5

Dim result6, result7, result8, result9, result10, result11

Dim result12, result13, result14, result15, result16, result17

Dim result18, result19, result20, result21, result22, result23, result24

Dim result25, result26, result27, result28, result29, result30, result31

Dim result32, result33, result34, result35, result36, result37, result38

Dim result39, result40, result41, result42, result43, result44, result45, result46

result_make = InStr(A.Value, "7070")
result_start = InStr(B.Value, "7070")
result1 = InStr(C.Value, "週出帳")
result2 = InStr(C.Value, "（１）")
result3 = InStr(C.Value, "記欠發票稅額轉列")
result4 = InStr(C.Value, "非記欠")
result5 = InStr(C.Value, "（４）")
result6 = InStr(C.Value, "（５）")
result7 = InStr(C.Value, "遞延帳項攤提")
result8 = InStr(C.Value, "應收")
result9_1 = InStr(C.Value, "學生宿舍")
result9_2 = InStr(C.Value, "宿舍")
result9_3 = InStr(C.Value, "宿網")
result10 = InStr(C.Value, "WI-FI")
result11 = InStr(C.Value, "預收轉營收")
result12 = InStr(C.Value, "劃")
result13 = InStr(C.Value, "越")
result14 = InStr(C.Value, "應攤")
result15 = InStr(C.Value, "營收帳項調整")
result16 = InStr(C.Value, "（７）")
result16_1 = InStr(C.Value, "數據發票類服務轉列營收")
result17 = InStr(C.Value, "帳單合併營收調整")
result18 = InStr(C.Value, "調改帳傳票")
result19 = InStr(C.Value, "窗口")
result20 = InStr(C.Value, "退")
result21 = InStr(C.Value, "更正")
result22 = InStr(C.Value, "營收轉帳事項")
result23 = InStr(C.Value, "營收銷帳劃出傳票")
result24 = InStr(C.Value, "科目調整")
result25 = InStr(C.Value, "建商")
result26 = InStr(C.Value, "帳務調帳")
result27 = InStr(C.Value, "LB410")
result28 = InStr(C.Value, "人工帳")
result29 = InStr(C.Value, "預估")
result30 = InStr(C.Value, "發票類負數")
result31 = InStr(C.Value, "調改帳入帳")
result32 = InStr(C.Value, "補銷帳")
result33 = InStr(C.Value, "數據發票業務負數")
result34 = InStr(C.Value, "科目誤列轉正")
result35 = InStr(C.Value, "免稅")
result36 = InStr(C.Value, "會計科目轉正")
result37 = InStr(C.Value, "沖帳")
result38 = InStr(C.Value, "紅利")
result39 = InStr(C.Value, "週估列")
result40 = InStr(C.Value, "週發票類月租費")
result41 = InStr(C.Value, "未銷帳")
result42 = InStr(C.Value, "應收軍事")
result43 = InStr(C.Value, "是方")
result44 = InStr(C.Value, "結算")
result45 = InStr(C.Value, "公話")
result46 = InStr(C.Value, "外牆")
result47 = InStr(C.Value, "HINET預繳制發票及發票作業前預繳科目轉列")
result48 = InStr(C.Value, "科目轉列")
result49 = InStr(C.Value, "營收帳項調整")
result50 = InStr(C.Value, "出租押金設算利息")

If (result1 > 0 And result_start = 0) Then
      AUTO_CATEGORY = "系統出帳"
ElseIf (result2 <> 1 And result3 > 0) Then
      AUTO_CATEGORY = "記欠發票"
ElseIf (result > 0 And result_start > 0) Then
      AUTO_CATEGORY = "HD記欠"
ElseIf (result2 = 1 And result3 > 0) Then
      AUTO_CATEGORY = "HD記欠"
ElseIf (result2 = 1 And result4 > 0) Then
      AUTO_CATEGORY = "HD非記欠"
ElseIf (result5 = 1) Then
      AUTO_CATEGORY = "收支系統"
ElseIf (result6 = 1 Or result7 > 0 Or result8 = 1) Then
      AUTO_CATEGORY = "收支調整"
ElseIf (result9_1 > 0 Or result9_2 > 0 Or result9_3 > 0) Then
      AUTO_CATEGORY = "收支調整"
ElseIf (result10 > 0 Or result11 > 0) Then
      AUTO_CATEGORY = "收支調整"
ElseIf (result_make = 0 And result12 = 1 And result13 = 0) Then
      AUTO_CATEGORY = "收支調整"
ElseIf (result_make > 0 And result12 > 0 And result13 = 0) Then
      AUTO_CATEGORY = "應付攤分"
ElseIf (result_make > 0 And result14 > 0) Then
      AUTO_CATEGORY = "應付攤分"
ElseIf (result_make > 0 And result15 > 0) Then
      AUTO_CATEGORY = "應付攤分"
ElseIf (result16 = 1 Or result16_1 > 0) Then
      AUTO_CATEGORY = "預收轉營收"
ElseIf (result17 > 0 Or result18 > 0 Or result20 > 0 Or result21 > 0) Then
      AUTO_CATEGORY = "調改帳"
ElseIf (result5 <> 1 And result19 > 0) Then
      AUTO_CATEGORY = "調改帳"
ElseIf (result22 > 0 Or result23 > 0 Or result24 > 0 Or result25 > 0) Then
      AUTO_CATEGORY = "調改帳"
ElseIf (result26 > 0 Or result27 > 0 Or result30 > 0 Or result31 > 0) Then
      AUTO_CATEGORY = "調改帳"
ElseIf (result28 > 0 And result29 = 0) Then
      AUTO_CATEGORY = "調改帳"
ElseIf (result32 > 0 Or result33 > 0 Or result34 > 0 Or result35 > 0) Then
      AUTO_CATEGORY = "調改帳"
ElseIf (result36 > 0 Or result37 > 0 Or result47 > 0 Or result48 > 0 Or result49 > 0) Then
      AUTO_CATEGORY = "調改帳"
ElseIf (result_make > 0 And result29 > 0 And result38 = 0) Then
      AUTO_CATEGORY = "數分估列"
ElseIf (result39 > 0 Or result40 > 0) Then
      AUTO_CATEGORY = "北分估列"
ElseIf (result41 > 0) Then
      AUTO_CATEGORY = "未銷帳估列"
ElseIf (result42 > 0 Or result43 > 0 Or result44 = 1 Or result45 > 0 Or result46 > 0 Or result50 > 0) Then
      AUTO_CATEGORY = "其他"
ElseIf (result38 > 0) Then
      AUTO_CATEGORY = "紅利積點"
Else
      AUTO_CATEGORY = "[No Match Rule?]"
End If

End Function

作者: diolin 時間: 2015-3-18 10:12

寫在 Module 中的 public function 都可以在函數的使用者定義中找到

直接在 cell 中輸入也可以

作者: leiru 時間: 2015-3-18 11:44

回復 2# diolin

不好意思，對vba很陌生，
　想請問在function前面加public，這是指什麼意思。。。

謝謝指導

作者: diolin 時間: 2015-3-19 10:14

其實不加 public , 預設也是 public

就是可以給 Module 外的呼叫

如果是 private , 就只有該 Module 裡可以呼叫

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)