【Excel】表示形式「文字列」でも関数は使いたい時の対処法

Excelのツール開発をやっていると、よく以下の要望をもらいます。

「私は関数を使ってデータを入力したいのに、セルが文字列型になっているので動かない」

まあ実際、私も関数はよく使うので、セルが文字列型になっていたらイラッとする気持ちはわかります。

↓文字列型だと計算式が評価されないですからね

しかし、手入力するユーザーのためにセルを文字列型にせざるを得ないときだってあるんです。

例えば以下のような表がある時

電話番号列は、文頭に0が含まれることが想定されるので、標準の表示形式では入力ミスを頻発させてしまいます。そのため、手入力するユーザーのために表示形式「文字列」は譲れないところです。

では、関数を使いたいというユーザーには諦めてもらうしか無いのでしょうか。

VBAのChangeイベントで両方に対応する

結論から言うと、VBAのChangeイベントを使用することで切り抜けることが出来ました。

ユーザーからはその後に指摘が上がってこなかったので、満足して使ってもらっていると思います。

やり方

1.まずは処理を実装したいワークシートを開きます。

2.Changeイベントに以下のVBAコードを記載します

Private Sub Worksheet_Change(ByVal r As Range)
    For Each c In r
        If Len(c) > 1 And Left(c.Value, 1) = "=" Then
            c.NumberFormatLocal = "G/標準"
            c.Formula = c.Formula
        Else
            c.NumberFormatLocal = "@"
        End If
    Next c
End Sub

動作確認

数値を入力した後は文字列型になっているので、前0が落ちない

しかし、=をつけると標準型に切り替わるので式を利用することが出来る

デメリットについて

これは、今回の件のデメリットと言うよりChangeイベントを使ったときのデメリットになるのですが

VBAを使用するとUndo情報がクリアされる

という制約があるので、Changeイベントで頻繁にVBAを動かすと「Ctrl + z」などでUndoを行う人から滅茶苦茶嫌われます。

どうにかVBAを使用した後にUndo情報を保持できないかと考えてみたのですが、正直難しいという結論になりました。

知恵袋などでも不可能という結論になっています。

https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q14118623074

しかし、Changeイベントが走ったとしても、セルの値を変更しなければUndo情報はクリアされません。

そこで、現実的な対処方法としては「文字列型にせざるを得ないセルのみでChangeイベント」を実行させるというのが考えられます。

Private Sub Worksheet_Change(ByVal r As Range)
    For Each c In r
        If c.column = 4 Then '追加
            If Len(c) > 1 And Left(c.Value, 1) = "=" Then
                c.NumberFormatLocal = "G/標準"
                c.Formula = c.Formula
            Else
                c.NumberFormatLocal = "@"
            End If
        End If '追加
    Next c
End Sub

「If c.column = 4 Then」という条件式を入れたことで、4列目以外が変更された時には何もしないという形になりました。

更に、元の書式から変更がない場合は何もしないようにします。

Private Sub Worksheet_Change(ByVal r As Range)
    For Each c In r
        If c.column = 4 Then
            If Len(c) > 1 And Left(c.Value, 1) = "=" Then
                If c.NumberFormatLocal <> "G/標準" Then '追加
                    c.NumberFormatLocal = "G/標準"
                    c.Formula = c.Formula
                End If '追加
            Else
                If c.NumberFormatLocal <> "@" Then c.NumberFormatLocal = "@"
            End If
        End If
    Next c
End Sub

これで本当に必要なときにしか値の変更が走らなくなるので、Undoもかなり使いやすくなるかと思います。

もっと応用をしていこうとすると

  • 列番号が前0を許可する列である
  • 入力された値が数値に変換可能な値である
  • 入力された値が0から始まっている

という条件のときに文字列型に変更するというような実装をすれば、このUndo問題はほぼ解決かなと思います。


性能面では大丈夫なのか?

Rangeをfor eachで回しているのでかなり性能が気になる感じではあります。

結論から言うと、「通常使うと想定される程度の操作では全く問題ない」です。

通常使うと思われる操作としては

  • 1000行程度をオートフィルする
  • 1000行程度をコピペで貼り付ける

という感じです。

通常想定されない操作は10万行を一気にコピーとかですね。これだとかなり遅い動きになってしまいます。

(そもそも、今回のコードを仕込まなくても重そうですが…)


コメントを残す

メールアドレスが公開されることはありません。

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)