Option Explicit
Function 最大值(前字元 As String, 後字元 As String, 儲存格 As Range) As Double
Dim Y, A$, j&, P#, V
Application.Volatile
Set Y = CreateObject("Scripting.Dictionary")
A = Replace(Replace(Replace(儲存格, " " & 前字元, ","), 後字元, ","), " ", ",")
V = Split(A, ",")
For j = 3 To UBound(V) Step 4
If IsNumeric(V(j)) And V(j) <> "" Then
P = V(j): Y(P) = ""
End If
Next
最大值 = Application.Max(Y.keys())
End Function作者: ranceymm 時間: 2022-12-13 22:54
Option Explicit
Function MX(儲存格 As Range) As Double
Dim Y, V, A$, j&, P#
Application.Volatile
Set Y = CreateObject("Scripting.Dictionary")
A = Replace(Replace(Replace(儲存格, " 長", ","), "、", ","), " ", ",")
V = Split(A, ",")
For j = 3 To UBound(V) Step 4
If IsNumeric(V(j)) And V(j) <> "" Then
P = V(j): Y(P) = ""
End If
Next
MX = Application.Max(Y.keys())
Set Y = Nothing : Erase V
End Function
Option Explicit
Function MXA(字串 As String) As Double
Dim Y, V, j&, P#
Application.Volatile
Set Y = CreateObject("Scripting.Dictionary")
字串 = Replace(Replace(Replace(字串, " 長", ","), "、", ","), " ", ",")
V = Split(字串, ",")
For j = 3 To UBound(V) Step 4
If IsNumeric(V(j)) And V(j) <> "" Then
P = V(j): Y(P) = ""
End If
Next
MXA = Application.Max(Y.keys())
Set Y = Nothing: Erase V
End Function作者: quickfixer 時間: 2022-12-14 21:36
Sub test()
Dim Find_Num, reg As Object, i As Integer, mxa As Double
Set reg = CreateObject("VBScript.RegExp")
reg.Pattern = "長(.+?)、"
reg.Global = True
Set Find_Num = reg.Execute(Range("d3"))
If Find_Num.Count > 0 Then
For i = 0 To Find_Num.Count - 1
If mxa < Find_Num(i).submatches(0) Then mxa = Find_Num(i).submatches(0)
Next i
End If
MsgBox mxa
Set reg = Nothing
End Sub作者: 准提部林 時間: 2022-12-14 22:05
Option Explicit
Sub test_quickfixer()
Dim Find_Num As Object, reg As Object, i As Integer, mxa As Double
'↑宣告變數!(Find_Num,reg)是通用型變數,(i)是長整數變數,(mxa)是雙精度小數數字
Set reg = CreateObject("VBScript.RegExp")
Set Find_Num = CreateObject("VBScript.RegExp")
'↑令reg這物件變數是 正則
reg.Pattern = "長(.+?)、"
'↑令正則的規則是 "長"字元與"、"符號之間(含)
'會計科目名稱,(.+?)可以是任意字符串故採用.加號表示科目名稱長度不能為0 ,
'加號之後的問號表示如果與之後的模式衝突時, 盡可能少地匹配相應的字符串(非貪婪匹配)
reg.Global = True
'↑正則的結果都要保留
Set Find_Num = reg.Execute(Range("d3"))
MsgBox Find_Num(1)
'MsgBox Find_Num(1, 1) '失敗 偵錯
'MsgBox Find_Num(1, 0) '失敗 偵錯
'MsgBox Find_Num(0, 1) '失敗 偵錯
'↑令Find_Num這通用型變數是 [D3]儲存格值以reg執行的正則
'之前不確定Find_Num是陣列還是什麼? 今天進一步確定他也是物件
'1.SET
'2.Find_Num As Object
'3.Set Find_Num = CreateObject("VBScript.RegExp")
'4.MsgBox Find_Num(1)
If Find_Num.Count > 0 Then
'↑如果Find_Num這正則的數量是大於 0 ??
For i = 0 To Find_Num.Count - 1
'↑設順迴圈!i從0到 Find_Num正則的數量-1 (代表正則的索引號也是0開始)
If mxa < Find_Num(i).submatches(0) Then
'↑如果迴圈數正則中 取Pattern = "長(.+?)、" 規則的小括號中的數值,
'大於mxa這雙精度小數值??
'找了好久!submatches(0)代表第一個子匹配,即小括號中的數值
mxa = Find_Num(i).submatches(0)
'↑mxa的初始值是0,後續迴圈正則經If判斷式比較後,會取得最大值的
End If
Next i
End If
MsgBox mxa
Set reg = Nothing
Set Find_Num = Nothing
End Sub作者: Andy2483 時間: 2022-12-15 10:36
Function GetNum(xS As String, X$, TY$)
'↑自訂函數名GetNum,變數1(xS)是字串,變數2(X)是字串,變數3(TY)是字串
Dim T, k, s%, xD
'↑宣告(T,k,xD)是通用型變數,(s)是短整數
'後學還不能確定%&如何正確使用,可能是防止溢位??
GetNum = ""
'↑令GetNum這自訂函數值是空字元!
'代表的是先不管後面能不能有值,先給空格
xS = Replace(xS, X, "|" & X)
'↑令xS這輸入的變數字串進行字元置換(用輸入的X變數字串換成 "|"符號連接X變數字串)
s = Len(X)
'↑令s這短整數變數是 輸入的X變數字串的字元數
If s = 0 Then Exit Function
'↑如果s字元數是 0,就結束自訂函數
Set xD = CreateObject("Scripting.Dictionary")
'↑令xD是 字典
For Each T In Split(xS & "|", "|")
'↑設順迴圈!令T是 (xS輸入的變數字串連接"|"符號後 被"|"符號分割的一維陣列)陣列子字串
'& "|" 暫不清楚為何要加這個??懇請指導
If Left(T, s) = X Then
'↑如果迴圈陣列子字串T的左側 s(輸入的X變數字串的字元數) 的字串,是 輸入的X變數字串??
k = k + 1
'↑令k這通用型變數+1
xD(k) = Val(Mid(T, s + 1))
'↑令K這數字是key,
'ITEM是 迴圈陣列子字串T 取從s + 1個字元開始的全部字元,經Val函式轉化的數值
'學到了
'1.xD(k) = Val(Mid(T, 1)),Val函式轉化的字串開頭若不是數字,回傳值為0
'2.Val函式轉化的字串判讀到 可轉化為數值連續字元,之後的字元捨棄
End If
Next
If k = 0 Then Exit Function
'↑如果k是 0,就結束自訂函數
If UCase(TY) = "MAX" Then
'↑如果函數輸入的TY變數字串是 "MAX" ??
GetNum = Application.Max(xD.Items)
'↑令GetNum回傳xD字典裡的items最大值
ElseIf UCase(TY) = "MIN" Then
'↑否則如果函數輸入的TY變數字串是 "MIN" ??
GetNum = Application.Min(xD.Items)
'↑令GetNum回傳xD字典裡的items最小值
ElseIf UCase(TY) = "SUM" Then
'↑再否則如果函數輸入的TY變數字串是 "SUM" ??
GetNum = Application.Sum(xD.Items)
'↑令GetNum回傳xD字典裡的items平均值
End If
End Function
http://forum.twbts.com/thread-23804-1-1.html
'上鏈結帖用 Application.Volatile 是因為格式變化會影響自訂函數,
'所以將使用者定義的函數標示為易變,
'測試結果是游標格變化就會重算自訂函數
'此帖可以不必使用Application.Volatile,目標格編輯就會回傳值即可
Sub T_20221215()
MsgBox Val("4.21、03/04 12:00 AA-173.25(x)")
End Sub作者: Andy2483 時間: 2022-12-16 07:41
Sub T_20221216_1() '可以開頭是小數點
MsgBox Val(".21、03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_2() '可以自動Trim掉前方空白字元
MsgBox Val(" 4.21、03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_3() '可以判斷開頭是負符連接小數點
MsgBox Val("-.21、03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_4() '可以判斷開頭是負符
MsgBox Val("-4.21、03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_5() '可以自動Trim掉前後方空白字元
MsgBox Val(" -4.21 、03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_6() '可以判斷開頭是正符
MsgBox Val("+4.21、03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_7() '不做運算
MsgBox Val("+4.21+1、03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_8() '自動去除中間的空白字元
MsgBox Val("+4.21 1 1、03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_9() '不會將日期型式的字串判為日期,且整數的多於0會自動去除
MsgBox Val("03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_10() '不會將時間型式的字串判為時間
MsgBox Val("12:00 AA-173.25(x)")
End Sub
Sub T_20221216_11() '會判別奇怪符號
Workbooks.Add
[A1] = "1200" & ChrW(160) & "00 AA-173.25(x)"
MsgBox Val("1200" & ChrW(160) & "00 AA-173.25(x)") http://forum.twbts.com/viewthrea ... &from=favorites
End Sub
Sub T_20221216_12() '可以開頭是小數也可以去空省零
MsgBox Val("00 0.21、03/04 12:00 AA-173.25(x)")
End Sub
補充:
Sub T_20221216_11() '會辨認單引號
MsgBox Val("'4.21、03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_12() '會辨認"^"符號,而且出現 型態不符的偵錯
MsgBox Val("4.21^2、03/04 12:00 AA-173.25(x)")
End Sub作者: Andy2483 時間: 2022-12-23 12:30
