none
配列変数を使ってセルに色を塗る RRS feed

  • 質問

  • フォーラムの皆様:

    配列変数を使ってセルに色塗りをしたいと思っております。お知恵を頂けると幸いです。

    実行前と実行後で以下のようにセルのValueによって(ここでは02 Yokohama, 04 Kyoto, 06 Sapporoのみ)色を塗りたいと思います。

    実際は5,000行以上のデータです。いろいろやり方はあると思うのですが、ここでは配列変数を使ってやってみたいと思っております。

    以下のコードを実行すると

    If Cells(i, 1) = Color Then

    のところで

    Compile Error Type Mismatchと出てしまいます。

    こんな『まんま』のコードで配列変数が扱えるはずがない・・・というお叱りを受けてしまうと思いますが、私の考えだと配列変数Colorの中には3つの文字列がグループで入っているはずだからこういうコードになるはずだ・・・という認識で書いております。

    恐れ入りますが、この考え方の間違っているところを御指摘していただけますでしょうか?

    よろしくお願いいたします。

    Option Explicit

    Dim i As Long
    Dim Color(1 To 3) As String
    Sub Hairetsu_Color()
    Color(1) = "02 Yokohama"
    Color(2) = "04 Kyoto"
    Color(3) = "06 Sapporo"
    For i = 1 To 18
    If Cells(i, 1).Value = Color Then
        Cells(i, 1).Interior.ColorIndex = 6
    End If
    Next i
    End Sub


    LiLi803

    2017年7月10日 2:19

回答

  • ある要素が、配列に含まれるかどうか判定するには関数 Filter を使うとよいかもしれません。

    Filter(配列, 要素)

    というように使います。戻り値は、指定された要素を含む配列となりますで、UBound 関数で添字の最大値を取得し、-1 以外であれば配列に要素が含まれているということになるようです。ただ、要素に空文字を指定した場合は、配列に含まれていると判定されてしまうようなので、空文字を除外するコードで下記のようにするとよさそうです。

    Option Explicit
    
    Dim i As Long
    Dim Color(1 To 3) As String
    Sub Hairetsu_Color()
    Color(1) = "02 Yokohama"
    Color(2) = "04 Kyoto"
    Color(3) = "06 Sapporo"
    For i = 1 To 18
        If Cells(i, 1).Value <> "" And UBound(Filter(Color, Cells(i, 1).Value)) <> -1 Then
            Cells(i, 1).Interior.ColorIndex = 6
        End If
    Next i
    End Sub
    • 編集済み kenjinoteMVP 2017年7月10日 4:24
    • 回答の候補に設定 trapemiyaModerator 2017年7月11日 1:48
    • 回答としてマーク LiLi803 2017年7月12日 2:58
    • 回答としてマークされていない LiLi803 2017年7月12日 2:58
    • 回答としてマーク LiLi803 2017年7月12日 2:58
    2017年7月10日 2:51
  • Compile Error Type Mismatchと出てしまいます。

    こんな『まんま』のコードで配列変数が扱えるはずがない・・・というお叱りを受けてしまうと思いますが、私の考えだと配列変数Colorの中には3つの文字列がグループで入っているはずだからこういうコードになるはずだ・・・という認識で書いております

    人間の頭で考えればそれで正解です。しかし、VBAではそこまでサポートしていません。VBAではそこまでしかできないという仕様だと思って下さい。つまり、VBAでは値と配列という集合を直接比較することができません。これはVBAが劣っているということではなく、プログラミング言語全般によく見られる思想です。そもそも1対多で等しいかどうかを比べることができないと考える(定義する)ことは普通であり基礎となることだと思います。
    よって、比較はあくまで1対1で行う必要あります。例えば次のように行います。

    Option Explicit
    
    Dim i, j As Long
    Dim Color(1 To 3) As String
    
    Sub Hairetsu_Color()
    
        Color(1) = "02 Yokohama"
        Color(2) = "04 Kyoto"
        Color(3) = "06 Sapporo"
    
        For i = 1 To 18
            For j = 1 To 3
                If Cells(i, 1).Value = Color(j) Then
                    Cells(i, 1).Interior.ColorIndex = 6
                End If
            Next j
        Next i
    
    End Sub

    For j = 1 To 3 の部分は、ちょっと気が利いた書き方をすれば、kenjinoteさんがかかれているようにUBoundを使って、
    For j = 1 To UBound(Color) と書くことができます。

    >引用のリンクをクリックすると灰色の背景の中にしか書き込むことができないので直接引用させていただきました。すみません。

    ちょっとしたTipsですが、HTMLという箇所をクリックすると、引用を含んだHTMLが開きますので、その末尾に文字を多少入力して戻ると、普通に灰色の背景の下から文字を書き込むことができるようになります。



    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    • 回答としてマーク LiLi803 2017年7月12日 2:58
    2017年7月11日 1:46
    モデレータ
  • Kenjinote様:

    ありがとうございました!!

    できました!! やはり=配列変数はいくらなんでも強引でした・・・。

    今までこのような判定をする時に何十も別名の変数を用意したり、何十行もの判定ElseIf Codeを長々書くのですが

    本当にそれではだめだ・・・と思います。ですから配列を習得することが必須となっているのですがなかなか前に進みません

    そしてKenjinote様が教えてくださったUBound(Filter(Color, Cells(i, 1).Value))のように関数を組み合わせて使うことも

    絶対必要なのになかなか使いこなせません。

    本当にありがとうございました。

    引用のリンクをクリックすると灰色の背景の中にしか書き込むことができないので直接引用させていただきました。すみません。

    LiLi803 (打倒残業!!)



    LiLi803

    • 回答としてマーク LiLi803 2017年7月12日 2:58
    2017年7月10日 4:12

すべての返信

  • ある要素が、配列に含まれるかどうか判定するには関数 Filter を使うとよいかもしれません。

    Filter(配列, 要素)

    というように使います。戻り値は、指定された要素を含む配列となりますで、UBound 関数で添字の最大値を取得し、-1 以外であれば配列に要素が含まれているということになるようです。ただ、要素に空文字を指定した場合は、配列に含まれていると判定されてしまうようなので、空文字を除外するコードで下記のようにするとよさそうです。

    Option Explicit
    
    Dim i As Long
    Dim Color(1 To 3) As String
    Sub Hairetsu_Color()
    Color(1) = "02 Yokohama"
    Color(2) = "04 Kyoto"
    Color(3) = "06 Sapporo"
    For i = 1 To 18
        If Cells(i, 1).Value <> "" And UBound(Filter(Color, Cells(i, 1).Value)) <> -1 Then
            Cells(i, 1).Interior.ColorIndex = 6
        End If
    Next i
    End Sub
    • 編集済み kenjinoteMVP 2017年7月10日 4:24
    • 回答の候補に設定 trapemiyaModerator 2017年7月11日 1:48
    • 回答としてマーク LiLi803 2017年7月12日 2:58
    • 回答としてマークされていない LiLi803 2017年7月12日 2:58
    • 回答としてマーク LiLi803 2017年7月12日 2:58
    2017年7月10日 2:51
  • Kenjinote様:

    ありがとうございました!!

    できました!! やはり=配列変数はいくらなんでも強引でした・・・。

    今までこのような判定をする時に何十も別名の変数を用意したり、何十行もの判定ElseIf Codeを長々書くのですが

    本当にそれではだめだ・・・と思います。ですから配列を習得することが必須となっているのですがなかなか前に進みません

    そしてKenjinote様が教えてくださったUBound(Filter(Color, Cells(i, 1).Value))のように関数を組み合わせて使うことも

    絶対必要なのになかなか使いこなせません。

    本当にありがとうございました。

    引用のリンクをクリックすると灰色の背景の中にしか書き込むことができないので直接引用させていただきました。すみません。

    LiLi803 (打倒残業!!)



    LiLi803

    • 回答としてマーク LiLi803 2017年7月12日 2:58
    2017年7月10日 4:12
  • Compile Error Type Mismatchと出てしまいます。

    こんな『まんま』のコードで配列変数が扱えるはずがない・・・というお叱りを受けてしまうと思いますが、私の考えだと配列変数Colorの中には3つの文字列がグループで入っているはずだからこういうコードになるはずだ・・・という認識で書いております

    人間の頭で考えればそれで正解です。しかし、VBAではそこまでサポートしていません。VBAではそこまでしかできないという仕様だと思って下さい。つまり、VBAでは値と配列という集合を直接比較することができません。これはVBAが劣っているということではなく、プログラミング言語全般によく見られる思想です。そもそも1対多で等しいかどうかを比べることができないと考える(定義する)ことは普通であり基礎となることだと思います。
    よって、比較はあくまで1対1で行う必要あります。例えば次のように行います。

    Option Explicit
    
    Dim i, j As Long
    Dim Color(1 To 3) As String
    
    Sub Hairetsu_Color()
    
        Color(1) = "02 Yokohama"
        Color(2) = "04 Kyoto"
        Color(3) = "06 Sapporo"
    
        For i = 1 To 18
            For j = 1 To 3
                If Cells(i, 1).Value = Color(j) Then
                    Cells(i, 1).Interior.ColorIndex = 6
                End If
            Next j
        Next i
    
    End Sub

    For j = 1 To 3 の部分は、ちょっと気が利いた書き方をすれば、kenjinoteさんがかかれているようにUBoundを使って、
    For j = 1 To UBound(Color) と書くことができます。

    >引用のリンクをクリックすると灰色の背景の中にしか書き込むことができないので直接引用させていただきました。すみません。

    ちょっとしたTipsですが、HTMLという箇所をクリックすると、引用を含んだHTMLが開きますので、その末尾に文字を多少入力して戻ると、普通に灰色の背景の下から文字を書き込むことができるようになります。



    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    • 回答としてマーク LiLi803 2017年7月12日 2:58
    2017年7月11日 1:46
    モデレータ
  • trapemiya様:

    人間の頭で考えればそれで正解です。しかし、VBAではそこまでサポートしていません。VBAではそこまでしかできないという仕様だと思って下さい。つまり、VBAでは値と配列という集合を直接比較することができません。これはVBAが劣っているということではなく、プログラミング言語全般によく見られる思想です。そもそも1対多で等しいかどうかを比べることができないと考える(定義する)ことは普通であり基礎となることだと思います。
    よって、比較はあくまで1対1で行う必要あります。例えば次のように行います。

    Option Explicit
    
    Dim i, j As Long
    Dim Color(1 To 3) As String
    
    Sub Hairetsu_Color()
    
        Color(1) = "02 Yokohama"
        Color(2) = "04 Kyoto"
        Color(3) = "06 Sapporo"
    
        For i = 1 To 18
            For j = 1 To 3
                If Cells(i, 1).Value = Color(j) Then
                    Cells(i, 1).Interior.ColorIndex = 6
                End If
            Next j
        Next i
    
    End Sub

    For j = 1 To 3 の部分は、ちょっと気が利いた書き方をすれば、kenjinoteさんがかかれているようにUBoundを使って、
    For j = 1 To UBound(Color) と書くことができます。



    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    >そもそも1対多で等しいかどうかを比べることができないと考える(定義する)ことは普通であり基礎となることだと思います。

    >よって、比較はあくまで1対1で行う必要あります。例えば次のように行います。

    ご説明ありがとうございました。 配列を人間の話す言語で押し込んではダメですね。 プログラム言語で考えれば配列でもコード的には1対1の比較になるようにしなくてはいけないのですね。

    IFのネスト・・・それもわたしの課題の一つです。ひとつのIF構文であっぷあっぷですのでそこも何とかしなくてはいけない部分です。

    ありがとうございました。今まで5,000行を手でハイライトしてセルに色を塗っていましたがやっとそれから解放されます。

    Lili 803


    LiLi803

    2017年7月11日 4:45