👻

Excelで文字列計算できるVBA関数(CalcText)【括弧不一致も検出】

に公開
Excel
VBA
文字列計算
tech

はじめに

数量計算書などで (2.000＋3.000)/2×5.400 のような文字列を計算したい場面があります。
Excel標準では文字列を直接計算できませんが、VBAの Evaluate 関数を使えば可能です。
ただし、括弧の不一致や不正文字があると #VALUE! エラーになってしまいます。

そこで、括弧不一致を検出し、わかりやすいメッセージを返す関数を作成しました。

解決策

  • 全角記号を半角に変換（×、÷、π、√など）
  • 括弧の不一致を検出してメッセージ表示
  • エラー時は「計算エラーです」と返す

VBAコード

Public Function CalcText(txt As String) As Variant
    Dim expr As String
    expr = NormalizeExpression(txt)

    If expr = "" Then
        CalcText = ""
        Exit Function
    End If

    If Not IsValidBrackets(expr) Then
        CalcText = "括弧の不一致があります"
        Exit Function
    End If

    Dim result As Variant
    result = Application.Evaluate(expr)

    If IsError(result) Then
        CalcText = "計算エラーです。式を確認してください。"
    Else
        CalcText = result
    End If
End Function

Private Function NormalizeExpression(txt As String) As String
    Dim expr As String
    expr = StrConv(txt, vbNarrow)
    expr = Replace(expr, "×", "*")
    expr = Replace(expr, "＊", "*")
    expr = Replace(expr, "÷", "/")
    expr = Replace(expr, "／", "/")
    expr = Replace(expr, "−", "-")
    expr = Replace(expr, "π", "3.14159265358979")
    expr = Replace(expr, "√", "SQRT(")
    expr = Replace(expr, "%", "/100")
    NormalizeExpression = expr
End Function

Private Function IsValidBrackets(expr As String) As Boolean
    Dim i As Long, ch As String
    Dim depth As Long
    For i = 1 To Len(expr)
        ch = Mid(expr, i, 1)
        If ch = "(" Then
            depth = depth + 1
        ElseIf ch = ")" Then
            depth = depth - 1
            If depth < 0 Then
                IsValidBrackets = False
                Exit Function
            End If
        End If
    Next i
    IsValidBrackets = (depth = 0)
End Function

Excelでの使い方
セルに =CalcText(D12) と入力。
丸めたい場合は以下の式を使います：
=IF(D12="","",IF(ISNUMBER(CalcText(D12)),ROUND(CalcText(D12),3),CalcText(D12)))

実行例

正常な式
((0.800×0.750＋0.500×0.600)×4×10.000)+5.0
→ 41.00
括弧不一致
(0.800×0.750＋0.500×0.600)×4)×10.000+5.0
→ 「括弧の不一致があります」

!Excel実行例
Excelでの実行例。正常な式は計算結果、括弧不一致はメッセージを返す
!VBAコード例
VBAで作成したCalcText関数。括弧の不一致や不正文字を検出する仕組みを実装

最後に

トライ＆エラーの繰返しでしたねー
なかなか、問題がどこにあるのか？　わからなかったりで．．．
やっと、できたみたい　って感じながよー
ほいたらねー

Discussion