セル★Cell |
||||
| わずかな知識で VBA！ |  セル★Cell
|
|||
セルの操作VBAでは、セルを「Rangeオブジェクト」といいます。 VBAで行う操作は、そのほとんどが、セルの操作といってもいいでしょう。 「マクロの記録」で記録したセルの操作は、手作業で行った操作の記録なの で、必ず、セルを選ぶ作業が記録されます。 手作業では、セルを選んで、アクティブにしたセルにしか操作できませんが、 マクロで行うセルの操作には、アクティブ状態でないセルにも操作が可能です。 Range("C9").Select ActiveCell.FormulaR1C1 = "VBA" → Range("C9").FormulaR1C1 = "VBA" Range("C10").Select |
わずかな知識で ＶＢＡ！ | |||
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つのセルかすべてのセルのどちらかだけで、 私は、全てのセルを選択する時以外は使用しません。 しかし、Cellｓプロパティは、引数に変数を使用できるというメリットがあります。 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つ右のセルというように、相対的なセルの参照形式です。 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 上のマクロは、アクティブセルが“Ｂ4”の時に記録を開始したものです。 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番目に並んでいる子供を指します。 どの子に聞いても、答えが「私の前の子」が「相対参照」で、 どの子に聞いても、答えが「一番前に並んでいる子供」になるのが「絶対参照」です。 よけいわかりづらくなったと言わないように！クゥー！（泣） |
||||
  |
||||
わずかな知識でＶＢＡ！ TOP マクロで何ができる？ ユーザーインターフェース マクロの記録 マクロの登録 イベントについて マクロの編集とデバッグ ゲームで覚えるVBAプログラミング オブジェクトの操作 セルの操作 MsgBox関数・InputBox関数 変数の型 ヘルプの活用 イミディエイトウィンドウ Withステートメント Setステートメント 条件分岐のステートメント If Then Else,Select Case 繰り返しのステートメント Do Loop 繰り返しのステートメント For Next ダイアログボックスを作ってみよう１ ダイアログボックスを作ってみよう2 【実践編】 ActiveXコントロール 各コントロールのプロパティ 文字列の操作 Endプロパティ リンク・検索 相互リンク マイプロフィール YouTubeの部屋 |
||||