Excel 2021 VBAを使って、ユーザー定義関数（UDF）を作成し、特定の条件に基づいて時間と数値を区別する方法について解説します。質問者が直面している問題は、時間の表示形式と数値の表示形式を適切に分ける方法に関するもので、VBAの関数内でフォーマットを使う際に直面する制約についても説明します。
1. ユーザー定義関数（UDF）の目的
ユーザー定義関数（UDF）は、Excel内で独自の計算式や処理を定義するための強力なツールです。質問者が作成した関数の目的は、値が0または「0:00」の場合に空欄を返し、それ以外の場合には時間または数値を適切にフォーマットして返すことです。
以下のコードは、質問者が作成した関数の一部です。
Function ZeroBlank(val As Variant) As Variant
If val = 0 Or val = "0:00" Or val = "00:00" Then
ZeroBlank = ""
ElseIf InStr(val, ":") > 0 Or InStr(val, "：") > 0 Then
ZeroBlank = Format(val, "[h]:mm")
Else
ZeroBlank = Format(val, "#,###")
End If
End Functionこの関数は、時間の形式と数値の形式を区別し、それに応じたフォーマットを適用しようとしています。しかし、ユーザー定義関数内でのフォーマットにはいくつかの制約があります。
2. VBAでのフォーマット制限
VBAの関数内で「Format」関数を使用して表示形式を変更することは可能ですが、Excelのセル内で設定できるような「数値フォーマット」のような機能とは異なります。セルの表示形式を変更することはVBA内のユーザー定義関数で直接行うことはできません。
そのため、「[h]:mm」のような時間の表示形式や「#,###」のような数値のフォーマットを適用するためには、VBAのコードでフォーマットするのではなく、Excelのセルのフォーマット設定を直接変更する必要があります。
3. 問題の解決方法
質問者が求めている動作を実現するためには、セルに値を設定した後にそのセルのフォーマットを設定する方法が最適です。以下のコード例では、セルの値をユーザー定義関数で処理した後、そのセルに適切なフォーマットを設定しています。
Sub SetCellValueAndFormat()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Sheets("Sheet1")
ws1.Range("C5").Value = ZeroBlank(ws2.Cells(i, "C").Value)
If InStr(ws1.Range("C5").Value, ":") > 0 Then
ws1.Range("C5").NumberFormatLocal = "[h]:mm"
Else
ws1.Range("C5").NumberFormatLocal = "#,###"
End If
End Subこのようにすることで、関数内で計算された値が適切にフォーマットされ、時間と数値の形式を正しく区別できます。
4. まとめ
Excel 2021のVBAを使用して、ユーザー定義関数（UDF）で「時間」と「数値」を判別し、適切なフォーマットを適用するためには、セルのフォーマット設定を活用することが重要です。直接的な表示形式の変更はVBA内で行えませんが、セルの値を処理した後にフォーマットを変更することで、質問者が求める動作を実現できます。
コメント