- 帖子
- 4901
- 主題
- 44
- 精華
- 24
- 積分
- 4916
- 點名
- 257
- 作業系統
- Windows 7
- 軟體版本
- Office 20xx
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-4-30
- 最後登錄
- 2024-11-27
              
|
6#
發表於 2012-2-12 22:10
| 只看該作者
本帖最後由 Hsieh 於 2012-2-12 22:14 編輯
回復 5# cr123a
你是儲存成增益集，那麼必須使用物件類別模組，讓開檔動作能加入說明
物件類別模組- Public WithEvents App As Application
- Private Sub App_NewWorkbook(ByVal Wb As Workbook)
- Get_Macro
- End Sub
- Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
- Get_Macro
- End Sub
一般模組- Public xlApp As New Class1 '宣告 xlApp 為上面自訂的物件模組
- Sub Auto_Open() 'Auto_Open會在此檔開啟時自動執行
- Set xlApp.App = Application
- '把自訂模組的 App 設為(也可說連結到)Application物件
- End Sub
- Sub Get_Macro()
- Dim cMdl As Object, Ay$, Ay1$, MyName, i&, k%
- Set cMdl = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule '函數所在位置
- i = cMdl.CountOfLines
- ar = Split(cMdl.Lines(1, i), Chr(10))
- For i = 0 To UBound(ar)
- If ar(i) <> "" And ar(i) Like "Function *" Then
- n = InStr(ar(i), " ")
- k = InStr(Split(ar(i), " ")(1), "(")
- MyStr = Mid(ar(i), n + 1, k - 1)
- If Ay = "" Then
- Ay = MyStr
- Else
- Ay = Ay & Chr(10) & MyStr
- End If
- End If
- If ar(i) <> "" And InStr(ar(i), "說明") > 0 Then
- If Ay1 = "" Then
- Ay1 = Replace(ar(i), "'", "")
- Else
- Ay1 = Ay1 & Chr(10) & Replace(ar(i), "'", "")
- End If
- End If
- Next
- MyName = Split(Ay, Chr(10))
- MyStr = Split(Ay1, Chr(10))
- For i = 0 To UBound(MyName)
- Application.MacroOptions macro:=MyName(i), Category:=10, Description:=MyStr(i)
- Next
- End Sub
- '以下為你原本自訂函數程式
- Function pkaJPreMath(A)
- '說明:pka20120211 每一字串加上符號
- pkaJPreMath = "$ " & A & " $ "
- End Function
- Function pkaJPreAddBam03(intA, IntB, A)
- '說明:pka20120211 每一字串加上 括號 大 中 小
- If intA = 0 Then '不加判別直接加
- If IntB = 1 Then
- pkaJPreAddBam03 = "( " & A & " ) "
- ElseIf IntB = 2 Then
- pkaJPreAddBam03 = "[ " & A & " ] "
- ElseIf IntB = 3 Then
- pkaJPreAddBam03 = "{ " & A & " } "
- End If
- ElseIf intA = 1 Then 'A為負值才加括號
- If IntB = 1 And Mid(A, 1, 1) = "-" Then
- pkaJPreAddBam03 = "( " & A & " ) "
- ElseIf IntB = 2 And Mid(A, 1, 1) = "-" Then
- pkaJPreAddBam03 = "[ " & A & " ] "
- ElseIf IntB = 3 And Mid(A, 1, 1) = "-" Then
- pkaJPreAddBam03 = "{ " & A & " } "
- Else
- pkaJPreAddBam03 = A
- End If
- End If
- End Function
載入此增益集後，開新檔案或開啟舊檔後函數類別會多出一個"命令"類別
此處的函數就有說明了
|
|
發表於 2012-2-12 00:01
| 
