Excel VBA > VBA：実務活用 > シート内にある全セルの文字数を取得する(ループ未使用版)
このエントリーをはてなブックマークに追加

シート内にある全セルの文字数を取得する(ループ未使用版)

実質1行でシート内にある全セルの文字数を取得する方法

時々、シートの中に含まれるセル全部の文字数が必要になる時があります。
プログラム的にはループを使ってゴリゴリ回す方法がありますが、そんなことはせずに実質1行できる方法があります。

そもそも一般操作でも取得できる

実のところ、VBAからでなくても、通常の関数を入力するだけでもセル全体の文字数は取得できます。
ポイントは「配列数式」です。Excelのバージョンが上がるに連れ、SUMIFS、COUNTIFSなど末尾に「S」のつく関数が登場し始めていることもあり、なおさら配列数式自体の存在感が低下している感もありますが、この方法を使うと全セルの文字数が取得できます。
配列数式は、下記の画像のように{}でくくるルールがあります。キーボードで「Ctrl + Shift + Enter」でできます。
シート内にある全セルの文字数を取得する(ループ未使用版)_01

シート内にある全セルの文字数を取得する(ループ未使用版)_02

案外使えるEvaluate

当初は、一般操作で配列数式で実装できたらから、VBAでもできるだろうと思っていましたがうまく行きませんでした。
というか、やり方の問題ですが、文字数を取得するのにセルに一度格納するのが何となく嫌でした。そこで思いついたのが「Evaluate」です。
このメソッドは感覚的に処理が遅くなるイメージがありますが、これで目的の処理が実装できます。

Sub 全セル内の文字数カウント()

    Dim Wsh     As Worksheet

    Set Wsh = ThisWorkbook.Worksheets(“Sheet1”)

    '' 表範囲を任意で設定
    Debug.Print Application.Evaluate(“=SUM(LEN(A1:C2))”)


    '' 表範囲を自動で設定
    Debug.Print Application.Evaluate(“SUM(LEN(” & Wsh.Cells(1, 1).CurrentRegion.Address & “))”)



End Sub

今回のケースで気をつけるのは、Evaluateはアクティブなシートを見るようなので、対象となるシートがアクティブでない場合、違うセルの内容を見ます。
従い今回のケースでは、事前に「Wsh.Activate」を入れておくと固いです(「=」は有ってもなくても動きました)。
また、セル範囲がつかめない場合は、UsedRangeでアドレスを持ってきたほうが良いかもしれません。

カテゴリ：VBA：実務活用

シート内にある全セルの文字数を取得する(ループ未使用版)
「Range,Cells」と「Resize」のセル範囲指定を比べてみる
動的なコントロールの「Enter」、「Exit」を疑似的に検知する
できるだけファイルをコピーされたくない時の対策について考える
Listboxに10列以上を追加するとき
Like演算子(「○○が含まれない」検索)で気を付けること
ListBoxに長い文字列を設定するとき
Excelで作ったツール・システムをできるだけ外部から管理する方法はあるか？
「RowSourceプロパティを設定できません。プロパティの値が無効です。」エラーについて
DictionaryでKey(キー)やItem(アイテム)をループして取得する方法3種類