セル★Cell

わずかな知識で VBA!

セル★Cell


 セルの操作



  VBAでは、セルを「Rangeオブジェクト」といいます。
  VBAで行う操作は、そのほとんどが、セルの操作といってもいいでしょう。

  「マクロの記録」で記録したセルの操作は、手作業で行った操作の記録なの
で、必ず、セルを選ぶ作業が記録されます。
  
  手作業では、セルを選んで、アクティブにしたセルにしか操作できませんが、
  マクロで行うセルの操作には、アクティブ状態でないセルにも操作が可能です。

  Range("C9").Select
  ActiveCell.FormulaR1C1 = "VBA" 
 Range("C9").FormulaR1C1 = "VBA"
  Range("C10").Select

  

わずかな知識で
VBA!


  Ranageプロパティでセルの操作を行う

    「マクロの記録」でセルB2を選択し記録を終了すると、
    プロシージャの命令群は次のようになります。

    Range(“B2”).Select
  
      


  また、複数のセルを選択する操作を記録すると、次の様になります。

    Range(“B2:C5”).Select      B2からC5の範囲を選択

      

  次に離れたセルを同時に選択すると

    Range(“B2,B5,D2,D5”).Select   B2、B5、D2、及び、D5を選択

      

  次に、離れた複数のセルを選択すると

    Range(“B2:D5,F2:G5”).Select  B2からD5、及び、F2からG7の範囲を選択

     

  となります。

  行全体や列全体を選択すると次の様になります。

    Range(“1:1”).Select
    Range(“A:A”). Select
    Range(“1:5”). Select
    Range(“A:D”).Select


  最後に、定義付済みの名前の範囲も指定できます。

    Range(“諸経費率”).Select


  Cellsプロパティでセルを選択する。

  VBAには、Rangeプロパティの他にCellsプロパティというものがあります。
  ただし、Cellsプロパティは、表記がわかりにくく、参照できるのも1つのセルかすべてのセルのどちらかだけで、
  私は、全てのセルを選択する時以外は使用しません。

  しかし、Cellsプロパティは、引数に変数を使用できるというメリットがあります。

  Cellsプロパティで1つのセルを選択する。

    Cellsプロパティでは、「Cells(行、列)」の形式でセルを特定します。
    Rangeプロパティとはちょうど逆になるので、注意が必要です。

    Cells(5, 3).Select  = Range("C 5").Select

     

    Cellsプロパティで全てのセルを選択する。(ワークシートの全てのセルを選択する。)

     Cells.Select

     

    Cellsプロパティで引数を使用してセルを選択する。

     Cells(i, r).Select ・・ ( i,rは変数)


    Rangeプロパティ、Cellsプロパティ共に、アクティブになったシートのセルしか選択できません。
    アクティブになっていないシートのセルを選択する場合には、
    あらかじめ目的のシートをアクティブにしておく必要があります。

    以前説明した、コンテナの概念を思い出してください。

    同じブック内ならば、
     Worksheets(“シート名”).Select まず、目的のシートをアクティブにします。
     Range(“A5;C5”).Select

    他のブック内のセルならば
     Workbooks(“ブック名.xls”).Worksheets(“シート名”).Activate
     Range(“A5;C5”).Select


    まず、目的のブックのシートをアクティブにします。
    上の例で「Activate」を「Select」にすると、Excelは、アクティブになっているブック内のシートしか
    Select(選択)できないので、エラーになりますが、次のように指定する方法もあります。

     Windows(“ブック名.xls”).Activate
     Worksheets(“シート名”).Select
     Range(“A5;C5”).Select


    VBAでは、セルの値を取得したり、セルの値を変更するために、
    わざわざそのセルを選択する必要はありません。
    コンテナの概念を活用して行えます。

  
  セルの値を取得する

    セルの値を取得するには、Valueプロパティを使用します。

    Sub 取得( )
       Dim namae As String
  ‘変数「namae」を文字列変数として宣言
       namae = Range(“B4”).Value  ‘namaeにセルB4の値を代入
    End Sub


         


  セルに値を設定する

    セルに値を設定する場合は、取得する場合の逆の方法で行います。

       Range(“B4”).Value = “VBA”

    他のシートのセルに入力する場合は、
       Worksheets(“Sheet2”).Range(“B4”).Value = “VBA”

    他のブックのセルに入力する場合は、
       Workbooks(“ブック名.xls”). Worksheets(“Sheet2”).Range(“B4”).Value = “VBA”
       となります。

    ただし、Workbooks(“ブック名.xla”)は、アクティブになっていなくてもよいのですが、
    Excel上に開いている必要があります。

    また、「Setステートメント」のページで説明する参照セルを使用した入力方法もあります。
    この方法は、「SETステートメント」のページで詳しく説明しています。
    そちらをご覧ください。

  セルの値を別のセルに入力する。

    Range(“B4”).Value = Range(“C10”).Value
    または、先ほどの例で行うと
      
       Sub 取得( )
          Dim namae As String   
          namae = Range(“B4”).Value    
namaeにセルB4の値を代入
          Range(“C10”).Value = namae    
セルC10にnamaeの値を代入
       End Sub

       となります。

  セルの数式を取得、入力する。

    セルに入力されているデータには、値と数式があります。

    マクロの記録で操作を記録すると、全て、FormulaR1C1プロパティで記録されます。

    実際には、Valueプロパティで、数式を設定したり、Formulaプロパティや、
    FormuraR1C1プロパティでも値の設定が可能です。
 
    しかし、Valueプロパティでは、数式の結果を取得することはできますが、数式を取得することはできません。
    式を取得する場合は、Formulaプロパティを使用します。
   
       Dim siki As String
       siki = Range("F12").Formula


    私の場合、数式を取得する目的以外では、全て、Valueプロパティを使用しています。


    ここでちょっとそれらの形式のお勉強です。

    A1形式

      普段見慣れている、列をA,B,Cなどのアルファベットで表す形式です。

    R1C1形式
  
      R1C1形式は、基準となるセルから1つ下、1つ右のセルというように、相対的なセルの参照形式です。

    A1形式での数式の入力

        Range(“C1”).Value = “=A1 + B1”
        Range(“C3”).Value = “=SUM(A3:B3)”


      これをR1C1形式にすると

        Range(“C1”).Value = “=RC[-2] + RC[-1]”
        Range(“C5”).Value = “=SUM(RC[-2]+RC[-1])”

       となります。

      A1形式の数式は、どこのセルに入力しても、セルA1とセルB1の和を求める数式になりますが、
      R1C1形式では、常に、入力したセルの2列左のセルと1列左のセルの和を求める数式となります。
      (絶対参照と相対参照)

  セルのデータをコピー・貼り付ける

    マクロ記録によるコピー、貼り付けでは下記のようになります。
 
      Sub Macro1()
          Range("B5").Select
          Selection.Copy
          Worksheets("sheet1").Select
          Range("B15").Select
          ActiveSheet.Paste
          Range("C18").Select
       End Sub


    ここの「Copy」、「Paste」は、クリップボードを経由する方法です。

    これでも十分に実用可能ですが、毎回、コピー先をSelectしなければならず、
     また、クリップボードを経由するという1ステップも入ってしまいます。

    上のプロシージャと同じことは、下記のプロシージャでもできます。

      Sub Macro1()
         Worksheets("sheet1").Range("B15").Value = Range("B5").Value
      End Sub



    コピーして貼り付ける場合に、Excelには「形式を選択して貼り付け」というものがあります。
    そのための「PasteSpecial」というメソッドも用意されています。

    しかし、VBAでは、書式や数式は自由に設定でき、
    他のセルの値は上記のプロシージャで十分取得できます。

    そこで、必要になってくるのは、計算結果等を他の帳票に転記し、元の数値を変更した場合に、
    同時に転記先の値も変更される、「リンク貼り付け」の方法だけということになります。

    マクロの記録で、リンク貼り付けを記録した場合は、
       
       Sub Macro2()
          Range("B5").Select
          Selection.Copy
          Range("B15").Select
          ActiveSheet.Paste Link:=True
       End Sub

 
    と、Pasteの後にLink:=Trueという引数が付きます。

    ですが、セルに入力された結果を見ると
  
     「=Sheet2!$B$5」または、「=Sheet2!B5」となっていると思います。
  
    マーク付は、絶対参照で、マーク無しは、相対参照の場合です。

    ということは、コピー機能を使用しなくても、
    上記の様に入力すれば「リンク貼り付け」と同じことができるわけです。

    このような発想が、マクロ作成には必要です。

    同じシート内なら  Range("B15").Value = "=B5" と目的のセルアドレスを入力。

    同じブック内の別のシートなら、シート名に続けて「!」(エクスクラメーションマーク)、
    そしてセルアドレスを入力します。
        
        Range("B15").Value = "=Sheet2!B5"

    ただし、シート名に「( )」(カッコ)が付いている場合は、
    シート名を「’」シングルコーテーションマークで囲む必要があります。

        Range("B15").Value = "='(1)'!B5"

    では、別のブックの場合は、どうしたらいいでしょうか?
    そうです、解らない場合は、「マクロの記録」で記録してみましょう!

       ActiveCell.FomulaR1C1 = "=[ファイル名.xls]シート名!R5C2"
  
    となっているハズです。

    ファイル名を拡張子付きで、[ ](半角大カッコ)で囲み、
    続けてシート名、「!」、そしてセルアドレスを入力すればいいのです。

    ただし、FormulaR1C1プロパティでは、A1形式の「B5」は使用できません。
    エラーとしてはじかれます。

    A1形式を使用したい場合は、

       ActiveCell.Value = "=[ファイル名.xls]シート名!B5"
  
    というように、Valueプロパティを使用します。

    では、目的のセルのアドレスがわからない場合はどうするのでしょうか?
    詳しくは他の章で説明しますが、Setステートメントというものがあります。
    参照セルを、他のシートや他のブックのシートに設定できます。

    *この場合のステートメントは、「命令文の一種」という意味ですが、
      他に「キーワードの一種」という意味もあります。
  
    これは英語を分かり易く日本語に訳したためで、英語より、日本語の方が細分化されているためです。
    どちらも大雑把にいうとステートメントなのです。

    その参照セルを他のステートメントを使用して目的のセルにセットします。

    参照セルを目的のセルにセットしたとしたら、
    参照セルのアドレスを取得すれば目的のセルのアドレスがわかります。

    探しに行ったブック名や、シート名は分かっているので、
    そのコンテナ部分を上記の様に追加してセルに記入すればいいのです。

 
    アドレスの取得には、「Address」プロパティを使用します。
    参照セルが「Setcell」、それを取込む変数名が「banti」だとすると
    
       banti = Setcell.Address  です。

       例: Range("B15").Value = "=" & banti

   ということで、
   一つのシートや表を丸ごとコピーするなどの場合を除いては、コピー機能はほとんど使用しません。
 
PR
397時間動画でわかる格安パソコン教室 ホームページ作成、オフィス(ワード、エクセル、パワーポイント、アクセス)2003/2007,Photoshop,Illustrator,Dreamweaver,Flash,Java,PHP,C,SEO,PPCが学べる!


  マクロでしかできないコピー

    手作業ではできなくて、マクロでしかできないことがあります。

    シートの一部を非表示にした時に、手作業でコピーを行うと
    非表示にされているセルまでコピーされてしまいますが、
    マクロでは見えているところだけコピーすることが可能です。

    まず、シートに入力されたデータ範囲、つまり、アクティブセル領域全体を一度に選ぶ方法から

    それには、「CurrentRegion」プロパティを使います。

       Range("A1").CurrentRegion.Select

    次に、見えている、非表示にされていない部分だけをコピーします。
    ここで、役に立つのが「SpecialCellsメソッド」です。

       Selection.SpecialCells(xlCellTypeVisible).Copy

    で、別のシートに貼り付ける方法は、

       Worksheets("Sheet2").Select
       Range("A1").CurrentRegion.Select
       Activesheet.Paste


    となります。実行すると、折りたたまれ非表示にされたセルはコピーされずに、
    見えていたセルだけのコピーができます。

 

  ここで重要な注意のお知らせです。

    Excelはコピーを行うとコピーモードというものになります。
    このモードがやっかいで、このモードのまま、行挿入等の処理を行うとうまくいきません。

    手作業時には、コピーモードのままだとメニューに「挿入」が表示されませんが、
    マクロで行うと実行できてしまいます。ただし、エラーです。

    Excelの持っているバグとも言えますが、
    コピーモードのままセルを選択してEnterキーを押した経験のある方なら経験したことがあると思います。

    そう、以前にコピーしたものが意に反して貼り付けられてしまいます。

    そこで、このコピーモードを解除する必要に迫られるのですが、その方法は、

      Application.CutCopyMode = False

    で行えます。

    コピーを行ったら、必ず、このコマンドでコピーモードを解除するクセを付けてください。

  

  セルのデータをクリアーする

    セルの「数式と値」をクリアーする

    方法は2つあります。

    1.Valueプロパティーで、空の文字列(””)(ダブルコーテーションマークを続けて2つ)を代入する

        Range(“B4”).Value = “”

    2.ClearContentsメソッドを使う

        Range(“B4”).ClearContents


  セルの書式をクリアーする

     Range(“B4”).ClearFormats


  セルの「数式と値」及び、「書式」をクリアーする

     Range(“B4”).Clear



  相対参照時のセル範囲の選び方

     マクロを作成していくと、特定の複数の場所に同じ作業を行うなど、
    参照セルを基準に場所を指定する時があります。

    たとえば、絶対参照でその範囲を指定し、そのデータを消去する場合は、

       Range(“B5:B8”).Clear
  
    としますが、これは、アドレスがわかっている場合にしかできません。

    それはどんな場合かというと、ある表の中の一行だけを消去するような場合です。

    消去する行は、条件によって決まる訳ですから、条件が変われば、消去する行の場所も変わります。
    このような場合には、相対参照でその範囲を指定します。

    Excel2003までは、マクロの記録が始まると「記録終了」ツールバーが表示されます。
    そのツールバーの中に「相対参照」ボタンがあります。

         

    Excel2007では、「リボン」の「開発」タグの中に「マクロの記録」ボタンがありますが、
    そのすぐ下に「相対参照で記録」ボタンがあります。
    このボタンをクリックした後に「マクロの記録」ボタンをクリックします。

      

     この「相対参照」で記録すると、セルを選択する操作は、
     Offsetプロパティを使ったステートメントで記録されます。

     

        Sub Macro1()
           ActiveCell.Offset(0, 2).Range("A1:C1").Select
        End Sub


    上のマクロは、アクティブセルが“B4”の時に記録を開始したものです。

    Offsetプロパティで2つ右側のセルを指定した後に、Rangeプロパティで範囲を指定しています。

    この辺がなかなか分かりづらいと思いますが、選択されているセル範囲にRangeプロパティを使うと、
    その範囲内でのA1形式でセルが判別されます。

    セル“D4”が”A1”、”E4”が”B1”で、そして、セル”F4”が”C1”という風に指定します。

    試しに、次のマクロを実行してみてください。

       Sub Macro1()
          ActiveCell.Offset(0, 4).Range("A1:C1").Select
          Selection.Range("B1").Value = "ここ"

       End Sub

     

  
    セル”F4”が”A1”ですから、”B1”であるセル”G4”に「ここ」が代入されます。

    また、単独のセル”D4”だけを選択する操作だと

       Sub Macro1()
          ActiveCell.Offset(0, 2).Range("A1").Select
       End Sub


    と記録されますが、キーワードの「Range(“A1”)」は省略可能です。

       Sub Macro1()
          ActiveCell.Offset(0, 2).Select
       End Sub


    「相対参照」ボタンをクリックすると、マクロの記録はこのような規則に従ってステートメントを作成します。

    Offsetプロパティは、基準となるセルから、
    上下左右にいくつ離れたセルかをカッコ内に入力し、セルを特定します。

    Offset(2, 3) は、基準となるセルから、下に2行、右に3列離れたセルです。

    Offset(-2, -3) だと、基準となるセルから、上に2行、左に3列離れたセルになります。

  
  相対参照と絶対参照

    分かりづらい言葉ですが、やさしい例(?)で説明すると、

     一列に子供が並んでいたとします。
    前から2番目の子供に、何かを質問したら、その答えが、「私の前の子」と答えたとします。
    「私の前の子」は一番前に並んでいる子供を指しますが、
    5番目の子供にすれば、「私の前の子」は、4番目に並んでいる子供を指します。
  
    どの子に聞いても、答えが「私の前の子」が「相対参照」で、
    どの子に聞いても、答えが「一番前に並んでいる子供」になるのが「絶対参照」です。
  
    よけいわかりづらくなったと言わないように!クゥー!(泣)
        


  わずかな知識でVBA!       TOP

  マクロで何ができる?   ユーザーインターフェース    マクロの記録   マクロの登録
 
  イベントについて      マクロの編集とデバッグ     ゲームで覚えるVBAプログラミング
 
  オブジェクトの操作     セルの操作            MsgBox関数・InputBox関数
 
  変数の型           ヘルプの活用          イミディエイトウィンドウ  
 
  Withステートメント      Setステートメント
 
  条件分岐のステートメント   If Then Else,Select Case
 
  繰り返しのステートメント   Do Loop
 
  繰り返しのステートメント   For Next
  
  ダイアログボックスを作ってみよう1              ダイアログボックスを作ってみよう2 【実践編】
  
  ActiveXコントロール   各コントロールのプロパティ      文字列の操作        Endプロパティ

  リンク・検索   相互リンク    マイプロフィール       YouTubeの部屋 
    

Copyright (C) わずかな知識でVBA! All Rights Reserved