【VBA】数字を英語表記にする(ドル・セント表記あり)ユーザー定義関数

貿易書類などで、数値(金額)を英語表記にする際に、便利&時短なエクセルのユーザー定義関数を紹介します。

U.S. DOLLARS や CENTS などを他の通貨に変えることも出来ます。

U.S DOLLARS TWELVE THOUSAND THREE HUNDRED FORTY FIVE AND CENTS SIXTY SEVEN ONLY

数字の英語表記って、難しいわけでないのですが、毎回の入力作業は面倒くさいですよね。

また手入力だと、スペルミスなどの間違いも起こる可能性がありますね。

スペルミスのチェックは、思い込みが入ると、見つけにくいです。

英語表記が出来るVBAを紹介しますので、コピペして使ってみてください。

エクセルマクロ

ユーザー定義関数の登録方法はこちら↓

メイン、サブ①~③の全てを記述する

VBAを記述後、A1に数値を入力し、A2のように数式を入れると英語表記の結果が得られる。
※A3も同じ結果が得られる。

目次

数値を英語表記にするVBA

メインのVBA

Function 英語表記1(ByVal 金額 As Variant) As String

Dim ドル As String
Dim 小数点の位置, 整数部分, 小数部分, 三桁ずつ, Count As Long
ReDim Place(9) As String

Place(2) = ” THOUSAND “
Place(3) = ” MILLION “
Place(4) = ” BILLION “

金額 = Trim(Str(金額))

小数点の位置 = InStr(金額, “.”)

If 小数点の位置 > 0 Then
    小数部分 = 数値②(Left(Mid(金額, 小数点の位置 + 1) & “0”, 2))
    整数部分 = Left(金額, 小数点の位置 – 1)
Else
    整数部分 = 金額
End If

Count = 1

Do While 整数部分 <> “”
    三桁ずつ = 数値③(Right(整数部分, 3))
    ドル = 三桁ずつ & Place(Count) & ドル

    If Len(整数部分) > 3 Then
        整数部分 = Left(整数部分, Len(整数部分) – 3)
    Else
        整数部分 = “”
    End If

    Count = Count + 1
Loop

Select Case ドル
    Case “”
        ドル = “NO DOLLARS “
    Case “ONE”
        ドル = “U.S DOLLAR ONE “
    Case Else
        ドル = “U.S DOLLARS ” & ドル & ” “
End Select

Select Case 小数部分
    Case “”
        小数部分 = “ONLY”
    Case “ONE”
        小数部分 = “AND CENT ONE ONLY”
    Case Else
        小数部分 = “AND CENTS ” & 小数部分 & ” ONLY”
End Select

英語表記1 = ドル & 小数部分

End Function

サブのVBA①

Function 数値③(ByVal ③ As String) As String

Dim Result, 百の位 As String

If Val(③) = 0 Then Exit Function

③ = Right(“00” & ③, 3)

If Mid(③, 1, 1) <> “0” Then
    百の位 = 数値①(Mid(③, 1, 1)) & ” HUNDRED “
End If

If Mid(③, 2, 1) <> “0” Then
    Result = 百の位 & 数値②(Mid(③, 2))
Else
    Result = 百の位 & 数値①(Mid(③, 3))
End If

数値③ = Result

End Function

サブのVBA②

Function 数値②(② As Long) As String

Dim Result, 十の位 As String

Result = “”

If Val(Left(②, 1)) = 1 Then
    Select Case Val(②)
        Case 10
            Result = “TEN”
        Case 11
            Result = “ELEVEN”
        Case 12
            Result = “TWELVE”
        Case 13
            Result = “THIRTEEN”
        Case 14
            Result = “FOURTEEN”
        Case 15
            Result = “FIFTEEN”
        Case 16
            Result = “SIXTEEN”
        Case 17
            Result = “SEVENTEEN”
        Case 18
            Result = “EIGHTEEN”
        Case 19
            Result = “NINETEEN”
    End Select
Else
    Select Case Val(Left(②, 1))
        Case 2
            十の位 = “TWENTY “
        Case 3
            十の位 = “THIRTY “
        Case 4
            十の位 = “FORTY “
        Case 5
            十の位 = “FIFTY “
        Case 6
            十の位 = “SIXTY “
        Case 7
            十の位 = “SEVENTY “
        Case 8
            十の位 = “EIGHTY “
        Case 9
            十の位 = “NINETY “
    End Select

    Result = 十の位 & 数値①(Right(②, 1))
End If

数値② = Result

End Function

サブのVBA③

Function 数値①(① As Long) As String

Select Case Val(①)
    Case 1
        数値① = “ONE”
    Case 2
        数値① = “TWO”
    Case 3
        数値① = “THREE”
    Case 4
        数値① = “FOUR”
    Case 5
        数値① = “FIVE”
    Case 6
        数値① = “SIX”
    Case 7
        数値① = “SEVEN”
    Case 8
        数値① = “EIGHT”
    Case 9
        数値① = “NINE”
    Case Else
        数値① = “”
End Select

End Function

英語表記にするVBAの解説

メインの解説

「金額」を英語表記にする関数
※小数第三位以下の数値は無効

※桁が増えれば足す
Place(5) = ” TRILLION “
Place(6) = ” QUADRILLON “
Place(7) = ” QUINTILLION “

金額を文字列にする

小数点の位置を検索

小数点以下を英語表記にし、整数部分を数値で抜き出す
小数点が存在する場合
    小数点以下2桁を英語表記にする(数値②式)・・・①
    整数部分を抜き出す
小数点が存在しない場合
    整数部分を英語表記にする

整数部分が空白でない場合(空白の場合はループを抜ける)
    右から三桁を抜き出し、英語表記にする(数値③式)

    ループ1回目:1~999まで & Place(1)=”” & ドル=””
    ループ2回目:1000~999999まで & Place(2)=” THOUSAND ” & ドル=1回目の結果
    ・・・        ・・・②

    整数部分が4桁以上の場合
        整数部分の右から三桁を削除する
    整数部分が3桁以下の場合
        整数部分を空白にする

    Count に1追加する
ここまでをループする

整数部分の結果②「ドル」について
空白の場合
    「ドル」を「NO DOLLARS」にする
1の場合
    「ドル」を「U.S DOLLAR ONE 」にする
2以上の場合
    「ドル」を「U.S DOLLARS 」&「ドル」にする

小数部分①について
空白の場合
    「小数部分」を「ONLY」にする
1の場合
    「小数部分」を「AND CENT ONE ONLY」にする
2以上の場合
    「小数部分」を「AND CENTS 」&「小数部分」& 「 ONLY」にする

英語表記1 = 「ドル」 & 「小数部分」

サブ①の解説

100-999の数値をテキストに変換

③の数値が0ならファンクションを終了する

取り出した数値を3桁以上にしたものの、下3桁を取り出す

百の位が0でない場合
百の位を英語表記にし、「&HUNDRED」を足す

十の位と一の位を英語表記にする
十の位が0でない場合
    数値③ = 百の位 & 数値②式
十の位が0の場合
    数値③ = 百の位 & 数値①式

サブ②の解説

10から99の数値をテキストに変換

Resultを空白にする

十の位が1の場合
    数値② = 10~19の場合
20~99の値の場合
    数値② = 十の位 & 数値①式

サブ③の解説

1から9までの数値をテキストに変換

①の数値(一の位)について
    数値① = 1~9の場合

メインのループ部分を変更する

Do While〜Loop
の部分を
For〜Next
に変更してみました。

ループ部分を変更したメインVBA

Function 英語表記2(ByVal 金額 As Variant) As String

Dim ドル As String
Dim 小数点の位置, 整数部分, 小数部分, 三桁ずつ, i, h As Long
ReDim Place(9) As String

Place(2) = ” THOUSAND “
Place(3) = ” MILLION “
Place(4) = ” BILLION “

金額 = Trim(Str(金額))

小数点の位置 = InStr(金額, “.”)

If 小数点の位置 > 0 Then
    小数部分 = 数値②(Left(Mid(金額, 小数点の位置 + 1) & “0”, 2)) ‘英語
    整数部分 = Left(金額, 小数点の位置 – 1) ‘数字
Else
    整数部分 = 金額
End If

‘整数部分を英語表記にする
h = WorksheetFunction.RoundUp(Len(整数部分) / 3, 0)

For i = 1 To h
    三桁ずつ = 数値③(Left(Right(整数部分, 3 * i), Len(Right(整数部分, 3 * i)) – 3 * (i – 1)))
    ドル = 三桁ずつ & Place(i) & ドル
Next i

Select Case ドル
    Case “”
        ドル = “NO DOLLARS “
    Case “ONE”
        ドル = “U.S DOLLAR ONE “
    Case Else
        ドル = “U.S DOLLARS ” & ドル & ” “
End Select

Select Case 小数部分
    Case “”
        小数部分 = “ONLY”
    Case “ONE”
        小数部分 = “AND CENT ONE ONLY”
    Case Else
        小数部分 = “AND CENTS ” & 小数部分 & ” ONLY”
End Select

英語表記2 = ドル & 小数部分

End Function

この記事が気に入ったら
フォローしてね!

よかったらシェアしてね!

この記事を書いた人

子育てに奮闘しながらも、再びガッツリ走り込める日を夢見るフルタイム会社員。

コメント

コメントする

CAPTCHA


目次
閉じる