月份: 2023 年 1 月

Excel 新增自訂 function 數字轉大寫中文

Function Num2Str(Str As String) As String
For i = 1 To Len(Str)
a = Mid(Str, i, 1)
Select Case True
Case a = 0
aa = aa & “　零”
Case a = 1
aa = aa & “　壹”
Case a = 2
aa = aa & “　貳”
Case a = 3
aa = aa & “　參”
Case a = 4
aa = aa & “　肆”
Case a = 5
aa = aa & “　伍”
Case a = 6
aa = aa & “　陸”
Case a = 7
aa = aa & “　柒”
Case a = 8
aa = aa & “　捌”
Case a = 9
aa = aa & “　玖”
Case Else
aa = aa & a
End Select
Next i
aa = Right(“零　零　零　零　零　零　零　零　零” & aa, 18)
bb = Left(aa, 6) & ” ” & Mid(aa, 7, 2) & ” ” & Mid(aa, 9, 2) & ” ” & Mid(aa, 11, 2) & Right(aa, 6)
Num2Str = bb
End Function

Excel Button Click with ERROR handler

Sub 按鈕1_Click()
‘On Error Resume Next
On Error GoTo MyErrorHandler:
s_line = Cells(1, 8)
e_line = Cells(1, 9)
If Not IsNumeric(s_line) Then
MsgBox (“請輸入數字”)
End
End If
If Not IsNumeric(e_line) Then
MsgBox (“請輸入數字”)
End
End If
If s_line > e_line Then
MsgBox (“請輸入正確起訖”)
End
End If
If e_line – s_line > 4 Then
MsgBox (“最多輸入5筆資料”)
End
End If
Range(“C5:F9”).Value = “”

For i = 1 To e_line - s_line + 1
    'Cells(4 + i, 3).Value = Worksheets("shipment").Cells(s_line + i - 1, 14).Value
    Cells(4 + i, 3).Value = Application.WorksheetFunction.VLookup(Worksheets("shipment").Cells(s_line + i - 1, 6).Value, Worksheets("item_ref").Range("A:E"), 2, False)
    Cells(4 + i, 5).Value = Worksheets("shipment").Cells(s_line + i - 1, 7).Value
    Cells(4 + i, 6).Value = Worksheets("shipment").Cells(s_line + i - 1, 9).Value
Next i

Exit Sub

MyErrorHandler:
If Err.Number = 1004 Then
MsgBox “第” & CStr(s_line + i – 1) & “行無參照品名”
Resume Next
ElseIf Err.Number = 13 Then
MsgBox “You have entered an invalid value.”
End If

End Sub

自訂 IsInt函數 檢查是否是整數

Function IsInt(aValue as Variant) As Boolean
    On Error Resume Next
    IsInt = (CInt(aValue) = aValue)
    On Error Goto 0
End Function