Excel(エクセル)2007のVBAです。

1列において、&結合(数式)されているセルのデータを「値」として扱い、
文字数(バイト数)の条件に従って範囲指定(薄いブルー囲われた状態に)するVBAコード(ソース)を教えていただきたいです。


(※長いので「この質問・回答へのコメント」欄に続きを書いています。よろしくおねがいします。)

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2011/04/16 02:11:17
  • 終了:2011/04/16 15:17:42

ベストアンサー

id:Mook No.2

Mook回答回数1312ベストアンサー獲得回数3912011/04/16 10:32:44

ポイント210pt

前回の質問のコメントで変数の話題が、そこで書かれていたように日本語も変数に使えます。


ただ、古くからプログラミングをしている人の場合、日本語は問題を引き起こしたりするケースがあり、これが習慣になっている場合もあります。

また、外国語環境を想定する場合などは運用上使わないことが一般的です。

(私はこれらの理由からプログラム中では日本語を使用していません。)


ただ、個人で使用する範囲ではほとんど問題ないので、そこは好みでよいと思います。

試しに書いてみるとこんな感じでしょうか。

'// コマンドボタン処理
'// -----------------------------------------------
Private Sub CommandButton1_Click()
    Const 選択文字数 = 50
    
'-- 選択セルを含んだ列対象の場合
    指定文字数以上の選択 ActiveCell.Column, 選択文字数

'-- E 列固定の場合
'    指定文字数以上の選択 4, 選択文字数
End Sub

'// 対象1: 対象列 A=1 ... E=4 ...
'// 引数2: 選択条件文字数
'// 引数3: 処理開始行(省略可:デフォルト 2)
'// -----------------------------------------------

Sub 指定文字数以上の選択(対象列 As Long, 選択文字数 As Long, Optional 開始行 As Long = 2)
    Dim 最終行 As Long
    最終行 = Cells(Rows.Count, 対象列).End(xlUp).Row
    
    Dim 選択対象 As Range
    Set 選択対象 = Nothing

    DimAs Long
    For= 開始行 To 最終行
        If LenB(StrConv(Cells(, 対象列).Value, vbFromUnicode)) >= 選択文字数 Then
            If 選択対象 Is Nothing Then
                Set 選択対象 = Cells(, 対象列)
            Else
                Set 選択対象 = Union(選択対象, Cells(, 対象列))
            End If
        End If
    Next

    If 選択対象 Is Nothing Then
        MsgBox "選択対象がありません"
    Else
        選択対象.Select
    End If
End Sub

上記のコードを(私にとって)普通に書いたら、下記のようになるでしょうか。

やはり慣れた書き方の方が落ち着きますw。

Option Explicit

'// コマンドボタン処理
'// -----------------------------------------------
Private Sub CommandButton1_Click()
    Const SelectLength = 50
    
'-- 選択セルを含んだ列対象の場合
    SelectData ActiveCell.Column, SelectLength

'-- E 列固定の場合
'    SelectData 4, 選択文字数
End Sub

'// 対象 dstCol      : 処理対象行 A=1 ... E=4 ...
'// 引数 SelectLength: 選択条件文字数
'// 引数 startRow    : 処理開始行(省略可:デフォルト 2)
'// -----------------------------------------------
Sub SelectData(dstCol As Long, SelectLength As Long, Optional startRow As Long = 2)
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, dstCol).End(xlUp).Row
    
    Dim SelectRange As Range
    Set SelectRange = Nothing

    DimAs Long
    For= startRow To lastRow
        If LenB(StrConv(Cells(, dstCol).Value, vbFromUnicode)) >= SelectLength Then
            If SelectRange Is Nothing Then
                Set SelectRange = Cells(, dstCol)
            Else
                Set SelectRange = Union(SelectRange, Cells(, dstCol))
            End If
        End If
    Next

    If SelectRange Is Nothing Then
        MsgBox "選択対象がありません"
    Else
        SelectRange.Select
    End If
End Sub
id:egaosaiko

Mookさん

毎回細かいところまでお答えいただき、ほんとうにありがとうございます。

この質問でも、前回の質問の流れを汲んで回答なさってくれるので大変助かります。

日本語を極力使わない理由、かなり納得しました。

それはあまり見かけない訳ですね!


今回の質問のコードについても、さすがでした。

一度コードを書いておけば、あとは「選択文字数」の条件を変更するとき以外コードをいじる必要がない仕様というのは

かなり嬉しいですね!

私の要望を上回るコード、参りました。

すぐ使わせていただきます。

2011/04/16 15:04:20

その他の回答(1件)

id:windofjuly No.1

うぃんど回答回数2625ベストアンサー獲得回数11492011/04/16 07:33:10

ポイント180pt

一例

Sub Macro1()
    '準備
    Dim i As Long
    Dim strLength As Long
    Dim startCell As Range, targetColumn As Long, startRow As Long, lastRow As Long
    Range("E2").Select:  'スタートするセルを書いておく
    strLength = 25: '判定文字数を書いておく
    
    '初期設定
    targetColumn = Selection.Column: '対象列を求める
    startRow = Selection.Row + 1: '作業開始行を求める
    lastRow = Cells(Rows.Count, targetColumn).End(xlUp).Row: '最終行を求める
    
    '実行
    For i = startRow To lastRow
        If Len(Cells(i, targetColumn).Value) > strLength Then
            '判定文字数以上のセルはセレクトに加える
            Range(Selection, Cells(i, targetColumn)).Select
        Else
            '「詰めて扱うという想定」なので25文字以下の行になったらループを抜ける
            '詰めずに最終行までセレクトする場合には下記Exit Forは削る
            Exit For
        End If
    Next i
End Sub
id:egaosaiko

windofjulyさん

ご回答ありがとうございます。

コードの方、試させていただきました。

エラーもなく、私の望んだ仕様通りに動いてくれました。

条件を変えるにしても、コードの変更箇所も少ないので

かなり使いやすいVBAだと思います。

2011/04/16 14:53:16
id:Mook No.2

Mook回答回数1312ベストアンサー獲得回数3912011/04/16 10:32:44ここでベストアンサー

ポイント210pt

前回の質問のコメントで変数の話題が、そこで書かれていたように日本語も変数に使えます。


ただ、古くからプログラミングをしている人の場合、日本語は問題を引き起こしたりするケースがあり、これが習慣になっている場合もあります。

また、外国語環境を想定する場合などは運用上使わないことが一般的です。

(私はこれらの理由からプログラム中では日本語を使用していません。)


ただ、個人で使用する範囲ではほとんど問題ないので、そこは好みでよいと思います。

試しに書いてみるとこんな感じでしょうか。

'// コマンドボタン処理
'// -----------------------------------------------
Private Sub CommandButton1_Click()
    Const 選択文字数 = 50
    
'-- 選択セルを含んだ列対象の場合
    指定文字数以上の選択 ActiveCell.Column, 選択文字数

'-- E 列固定の場合
'    指定文字数以上の選択 4, 選択文字数
End Sub

'// 対象1: 対象列 A=1 ... E=4 ...
'// 引数2: 選択条件文字数
'// 引数3: 処理開始行(省略可:デフォルト 2)
'// -----------------------------------------------

Sub 指定文字数以上の選択(対象列 As Long, 選択文字数 As Long, Optional 開始行 As Long = 2)
    Dim 最終行 As Long
    最終行 = Cells(Rows.Count, 対象列).End(xlUp).Row
    
    Dim 選択対象 As Range
    Set 選択対象 = Nothing

    DimAs Long
    For= 開始行 To 最終行
        If LenB(StrConv(Cells(, 対象列).Value, vbFromUnicode)) >= 選択文字数 Then
            If 選択対象 Is Nothing Then
                Set 選択対象 = Cells(, 対象列)
            Else
                Set 選択対象 = Union(選択対象, Cells(, 対象列))
            End If
        End If
    Next

    If 選択対象 Is Nothing Then
        MsgBox "選択対象がありません"
    Else
        選択対象.Select
    End If
End Sub

上記のコードを(私にとって)普通に書いたら、下記のようになるでしょうか。

やはり慣れた書き方の方が落ち着きますw。

Option Explicit

'// コマンドボタン処理
'// -----------------------------------------------
Private Sub CommandButton1_Click()
    Const SelectLength = 50
    
'-- 選択セルを含んだ列対象の場合
    SelectData ActiveCell.Column, SelectLength

'-- E 列固定の場合
'    SelectData 4, 選択文字数
End Sub

'// 対象 dstCol      : 処理対象行 A=1 ... E=4 ...
'// 引数 SelectLength: 選択条件文字数
'// 引数 startRow    : 処理開始行(省略可:デフォルト 2)
'// -----------------------------------------------
Sub SelectData(dstCol As Long, SelectLength As Long, Optional startRow As Long = 2)
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, dstCol).End(xlUp).Row
    
    Dim SelectRange As Range
    Set SelectRange = Nothing

    DimAs Long
    For= startRow To lastRow
        If LenB(StrConv(Cells(, dstCol).Value, vbFromUnicode)) >= SelectLength Then
            If SelectRange Is Nothing Then
                Set SelectRange = Cells(, dstCol)
            Else
                Set SelectRange = Union(SelectRange, Cells(, dstCol))
            End If
        End If
    Next

    If SelectRange Is Nothing Then
        MsgBox "選択対象がありません"
    Else
        SelectRange.Select
    End If
End Sub
id:egaosaiko

Mookさん

毎回細かいところまでお答えいただき、ほんとうにありがとうございます。

この質問でも、前回の質問の流れを汲んで回答なさってくれるので大変助かります。

日本語を極力使わない理由、かなり納得しました。

それはあまり見かけない訳ですね!


今回の質問のコードについても、さすがでした。

一度コードを書いておけば、あとは「選択文字数」の条件を変更するとき以外コードをいじる必要がない仕様というのは

かなり嬉しいですね!

私の要望を上回るコード、参りました。

すぐ使わせていただきます。

2011/04/16 15:04:20
  • id:egaosaiko
    (続きです。)


    たとえば、

    【E列】セルに入っているデータ
    E2:=A2&B2&C2&D2
    E3:=A3&B3&C3&D3
    E4:=A4&B4&C4&D4
    E5:=A5&B5&C5&D5
    E6:=A6&B6&C6&D6
    E7:=A7&B7&C7&D7
    E8:=A8&B8&C8&D8
    E9:=A9&B9&C9&D9
    E10:=A10&B10&C10&D10
    E11:=A11&B11&C11&D11


    【E列】セルの見え方
    E2:犬はとってもかわいい動物です☆たくさんの人にとっても愛されています★
    E3:猫はすごくカワイイ動物だと思います◆いろんな方に飼われています★
    E4:パンダといったら、世界中で愛されているアニマルです◇多くの人を楽しませています◆
    E5:キリンはすごく大きくて大人気の動物です★子供から大人までいろんな人に人気があります☆
    E6:ライオンは動物界の王様と言われています☆特にたてがみに貫禄があります◆
    E7:☆☆
    E8:◆★
    E9:◇◆
    E10:★☆
    E11:★◆


    上記のように「E列」に&結合データがある場合に
    コマンドボタン(CommandButton1_Click)実行で、

    ↓↓↓

    【E列】
    --------------(↓ここから範囲指定↓)----------------------------------------------------
    E2:犬はとってもかわいい動物です☆たくさんの人にとっても愛されています★
    E3:猫はすごくカワイイ動物だと思います◆いろんな方に飼われています★
    E4:パンダといったら、世界中で愛されているアニマルです◇多くの人を楽しませています◆
    E5:キリンはすごく大きくて大人気の動物です★子供から大人までいろんな人に人気があります☆
    E6:ライオンは動物界の王様と言われています☆特にたてがみに貫禄があります◆
    --------------(↑ここまで範囲指定↑)----------------------------------------------------
    E7:☆☆
    E8:◆★
    E9:◇◆
    E10:★☆
    E11:★◆


    といった具合に範囲指定してくれるコードが知りたいのです。


    具体的にはこの場合、
    E2~E6の「長めのデータ(表現が曖昧ですいません)が入っているセルだけ」を範囲指定したいので、
    文字数で判断すべきだと思うのです。


    【E列】の文字数
    E2:全角34文字(68バイト)
    E3:全角32文字(64バイト)
    E4:全角40文字(80バイト)
    E5:全角42文字(84バイト)
    E6:全角35文字(70バイト)
    E7:全角2文字(4バイト)
    E8:全角2文字(4バイト)
    E9:全角2文字(4バイト)
    E10:全角2文字(4バイト)
    E11:全角2文字(4バイト)

    ↑のような文字数になっています。

    E7以降の、「記号(☆、★、◇、◆)だけの行」においては、
    「記号を消したくない」のと、今後ここに「日本語を加えるかもしれない」ということを考慮して

    (けっこう曖昧で感覚的ですが、)
    「E2~E列で、全角25文字(50バイト)以上のところまで」を範囲指定するようにしたいです。


    ※【補足です】
    ●範囲指定した後もその範囲のセルは「&結合(数式)のまま」になっていることが条件です。
    「値」にならないようにしたいのです。

    ●上記に書いた「E列(E2~E11)」というのは、あくまで例です。
    実際には、違う列でも使え、行の限界まで対応できるようにしたいです(1列だけ処理できれば満足です。)

    ●上記に書いた「E列(E2~E11)」のデータも、あくまで例です。
    実際には、違うデータでも使え、文字数が増減しても対応できるようにしたいです。
    ですが、今のところ、
    「ある1列の2行目~列の最終行において、全角25文字(50バイト)以上のところまで」を範囲指定できれば満足です。
    また、たとえば

    【E列】の文字数
    E2:全角34文字(68バイト)
    E3:全角32文字(64バイト)
    E4:全角1文字(2バイト) ←①
    E5:全角42文字(84バイト)
    E6:全角35文字(70バイト)
    E7:全角2文字(4バイト)
    E8:全角2文字(4バイト)
    E9:全角200文字(400バイト) ←②
    E10:全角2文字(4バイト)
    E11:全角2文字(4バイト)

    ↑①のように
    「全角25文字(50バイト)以上のセル」と「全角25文字(50バイト)以上のセル」の間に、
    「全角24文字(48バイト)以下のセル(全角25文字(50バイト)未満のセル)」が存在する可能性は考えなくて大丈夫です。

    さらにまた、
    ↑②のように
    「全角24文字(48バイト)以下のセル」と「全角24文字(48バイト)以下のセル」の間に、
    「全角25文字(50バイト)以上のセル」が存在する可能性も考えなくて大丈夫です。

    最低でも「全角25文字(50バイト)以上のデータ」を、2行目3行目・・・と詰めて扱うという想定でおねがいします。



    そもそもここまでの条件を満たすことをエクセルVBAで可能なのかどうかも分からないのですが、
    もしデキるという方がいましたら、コードを教えてくれないでしょうか。
    どうかよろしくおねがいします。

  • id:Mook
    >範囲指定(薄いブルー囲われた状態に)する
    の処理を確認したいのですが、背景色を「薄いブルー」にするということでしょうか。

    追記:
    あっ、ばかなことを質問してしまいました。
    「選択する」ということですね。
  • id:Mook
    仕様を見落としていました。
    連続していない場合も、条件に該当するものをすべて選択するようになってしまっています。

    最初に条件に該当しないものが出た時点終了する場合は
        End If
      Next
    の前に
        Else
          Exit For
        End If
      Next
    の2行を追加してください。
  • id:egaosaiko
    Mookさん

    コメントありがとうございます。

    >「選択する」ということですね。
    はい、そうです。
    私の説明がまどろっこしくて申し訳ありません。
    (薄いブルー囲われた状態に) ←ここは省略するべきでした。


    また、仕様の修正ありがとうございます。
    ここはぜひ確認しておきたいのですが、

        End If
      Next
    の前に
        Else
          Exit For
        End If
      Next
    の2行を追加してください。

    実際は「2行」ではなく、「4行」ということでいいのでしょうか。
    確認していただけると嬉しいです。
  • id:Mook
    後ろの2行(End If以降)は共通ですから、その前に2行追加してください。


    下記は別件でのコメントですが
    いろいろな処理をボタンで使用とされており、それはそれでもよいのですが、
    ボタンを使わず、ショートカットキーを使用する方法もあります。

    シートモジュールではなく、標準モジュールにコードを置き、
    Alt+F8 で「マクロ」ウィンドウを開いた下に、オプションボタンがあります。
    実行したいマクロを選んでからオプションを開き、ショートカットキーを
    設定しておくとボタンがなくともそのショートカットキーで実行できます。

    今回の選択するだけのようなものは、ボタンよりそちらの方が便利かと思います。
  • id:egaosaiko
    Mookさんへ
    ご回答ありがとうございます。

    >後ろの2行(End If以降)は共通ですから、その前に2行追加してください。
    わかりました!
    助かります。


    ショートカットいいですね。
    教えていただきうれしいです。
    ある程度キーを記憶しておかないといけないですが、
    慣れれば確かに、こちらの方が操作が楽ですね!

    参考にさせていただきます。
  • id:SALINGER
    変数の日本語使用について補足すると、Excel95時代に2バイト文字をうまく処理できなかった大昔は
    確かに不具合がありましたが、ユニコードで処理される今のVBAではほとんど不具合は無くなっています。
     
    私はたまにプロシージャ名に使うくらいだけですが、確か人力検索で回答される方の中にはよく使われる方もいます。
    そういう回答を否定するものではなくて、それはそれで変数の意味が分かりやすくもありますから、
    初心者でも馴染みやすいVBならではの仕様だと思います。
     
    私の場合は作ったプロシージャが何をするルーチンだったか忘れっぽいので、そこに「重複カラー変更」
    とかって名前にしておけば、呼び出すときにすぐにわかるので便利です。
    日本語も使い方次第です。
  • id:Mook
    ちょっと語弊があったかもしれません。
    影響範囲をわかったうえで、理解しやすいのであれば、
    使うことは問題ありません。

    私の場合は、VB だけに限らず Java や Web 関連のソースを扱いますし、
    (そういうところでは、相変わらず多バイトコードはいろいろと問題を
    引き起こします。)
    仕事上外国人相手にソースコードのやり取りをしますので、使用できない
    ということです。

    決して使うことを否定しているわけではありません。
  • id:SALINGER
    私は前のコメントでVBAの話をしてるつもりでしたが、ここではMookさんはプログラム全般の話をしてたのでしたか失礼しました。
    egaosaikoさんがJavaやWeb関連のソースを扱うことがあれば、もちろんそのようにするべきでしょうね。
    補足のつもりでしたが気に触ったのでしたら申し訳ありません。
  • id:egaosaiko
    SALINGERさんへ

    貴重なVBA情報ありがとうございます。

    日本語とコメント('~)を併用すれば、余計な混乱をかなり軽減できそうですね!
    VBAの勉強の仕方として、
    やっぱり分かりやすいほうがいいので上達の近道になるなら日本語もいいかもしれないと思いました。

    英単語を多く知っている方がプログラムに有利なのは変わらないかもしれないですが。




    Mookさんへ

    (私からすると)とても貴重な経験をされているのですね。

    一応は、英語を使っておけば間違いないとは聞きますよね。
    私の場合、とりあえず自分で勉強する時あるいは慣れるまでは、日本語を使っていこうかなと思っています。

    とりあえず、個人でVBAを扱うだけなので動けばOK!という感じだったりもします。
  • id:SALINGER
    日本語変数の弱点というか気になる点がありまして、
    それは分かりやすい反面、その意味を意識しすぎて逆に間違えるというのがあります。
     
    例えば、前の質問にあったように"セルA1"という変数名を使うとします。
    "セルA1"という変数にRange("A1")が入っているならいいのですが、
    プログラムでは変数に他のセルを入れるケースも当然出てきます。
    そのときに変数名に意味がありすぎると、その意味に引っ張られて間違えてしまうのです。
     
    それとアルファベットの変数名には良い点もありまして、
    例えば次のような変数名を宣言します。
    Dim lastRow as Long
    つまり小文字と大文字が混ざった変数です。
    そうして変数を入力するときに、全て小文字で入力してみると、勝手にRが大文字になることがわかると思います。
    ここでもし大文字にならなかったら、変数を宣言し忘れてるかスペルが間違っているとすぐわかります。
     
    こういった理由で私の場合も変数に日本語を使うことはありませんが、それは不具合とかというよりその方が便利だからという理由です。人それぞれでしょうが。
    最初のうちはとっつき易い方法で、徐々に理解を深めて行くうちにアルファベットに置き換えていければいいんじゃないでしょうか。
  • id:SALINGER
    前の前の質問のコメントで「日本語でもOK」と書いたことから
    質問と関係ない話題で盛り上がってしまって申し訳ありませんでした。
  • id:taknt
    >例えば、前の質問にあったように"セルA1"という変数名を使うとします。
    >"セルA1"という変数にRange("A1")が入っているならいいのですが、
    >プログラムでは変数に他のセルを入れるケースも当然出てきます。
    >そのときに変数名に意味がありすぎると、その意味に引っ張られて間違えてしまうのです。

    cell_A1なんて変数を作ったら 同様ですよ。

    これは 日本語変数の弱点でも何でもない。
  • id:SALINGER
    日本人であるが故に日本語により強い意味を考えるという話です。
    アルファベットだから意味が無くなるという話ではないことは質問者様もわかっていると思います。
  • id:egaosaiko
    takntさんへ

    アドバイスありがたいです。
    変数名は、分かりやすい名前を付けた方がいいけど、
    「限定」しすぎない習慣をつけた方がいいみたいですね。




    SALINGERさんへ

    >前の前の質問のコメントで「日本語でもOK」と書いたことから
    >質問と関係ない話題で盛り上がってしまって申し訳ありませんでした。

    いえ、(私が理解できるかどうかは別として)むしろ助かります。
    すぐ反映できそうなことは、ぜひ取り入れていきたいので。
    機会があれば、これからもよろしくおねがいします!

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません