Excelを勉強しましょう EnjoyExcel

アイコンの機能や関数の説明、VBA初心者向けの勉強方法について情報掲載しています。関数、VBAともに理解が進むように出来るだけ事例を交えて説明しています。

VBA⑨ セルに罫線を書く & 実行ボタンを用意してみよう。

f:id:EnjoyExcel:20210627141028p:plain

最終行と最終列もセット出来るようになったのであとは罫線書いてセル画面に実行ボタンセットしたらcsvデータをもらってExcelに貼り付けたら表が出来ちゃうんじゃないか?って思えてきましたね。 早速やりましょう。

 

前回は変数の型、なんで変数を宣言するのかを説明しました。

enjoyexcel.hatenablog.com

 

では罫線から。

 

選択したセルの上、下、右、左、範囲選択して中の十字、実線だったり点線、色を変えたり・・・これ全部出来るんですけどここでの説明は省略。ひとまずコード書いてみましょう。

 

書き方のコツさえ分かれば後は場所と線の種類を入れ替えるだけですので各自でやってみてください。

 

ひとまず場所ごとに線を引くコードです。

 

With Range(”A1:E10”)

.Borders(xlEdgeTop).LineStyle = xlContinuous   '上に実線
.Borders(xlEdgeBottom).LineStyle = xlContinuous   '下に実線
.Borders(xlEdgeRight).LineStyle = xlContinuous   '右に実線
.Borders(xlEdgeLeft).LineStyle = xlContinuous   '左に実線
.Borders(xlInsideVertical).LineStyle = xlContinuous   '範囲内のセルに垂直実線
.Borders(xlInsideHorizontal).LineStyle = xlContinuous   '枠内のセルに平行実線

End With

 

またオブジェクトやプロパティの話になってしまうし今回は引数もいるのでもうこれは呪文だと思って暗記してください。

 

Withだけ少し説明します。Bordersの前には本来Rangeオブジェクトが居ます。

 

でも毎回書くと面倒だし画面も見にくいです。ですからWth 〇〇 とEnd with で挟んで○○を省略しましょうという書き方です。

 

With 内はドット以降にコードを書くことでWith 〇〇 を前に持ってますという建付けですね。これでコードの量も減りますし見やすくなります。

 

以上がWithの説明です。

 

あとはオブジェクト、コレクション、プロパティの話になるのですがひとまず置いといて以下のように書いたら線引けますって最初は覚えてしまってください。1は簡単に表に線を引く方法です。

 

1、指定範囲の全範囲に線を引きます

 Range.Borders.プロパティ = 設定値

 

2、指定範囲の中からindexで指定の場所に線を引きます

Range.Borders.(index).プロパティ = 設定値

 

さっきWithでたくさん書いたコードは2です。とにかく格子状に線を引いてしまうには1の書き方でよいと思います。

 

’セルA1からE10に格子状の実線を引きます

Range("A1:E10").Borders.LineStyle = xlContinuous

 

これで完了。

 

ではボタンをセットしましょう。

 

いろいろ作り方ありますけどこれでOK。

 

f:id:EnjoyExcel:20210627163204p:plain

 

 図形のボタンをボタンっぽくデザインしてこれでOK。

f:id:EnjoyExcel:20210627163515p:plain

 

あとはボタンにマクロを登録したらそれで終了です。

準備したボタン風の図形を選択し右クリック。マクロの登録を選択。

f:id:EnjoyExcel:20210627163846p:plain

ボタンに設定したいマクロを選択して右下のOKボタンでセット完了。

 

これだけ。結構簡単です。こうすることで実行が楽になります。

 

次はメッセージボックスですね。IF文のスキルが必要になりますので合わせて説明します。最後にループと少しだけExcelのワークシート関数を紹介してこのシリーズは終了の予定です。

 

ではもう少しお付き合いください。

VBA⑧ 変数っていろんな型がある。万能ですが形にこだわります。

f:id:EnjoyExcel:20210627130807p:plain

 

前回の記事では変数ってどんなものなのか少しわかっていただいたかと思います。

enjoyexcel.hatenablog.com

 

Dim が出てきて終わってましたので続きを説明します。

 

Dim ○○ As △△   って書きます。

 

〇〇は変数(任意)です。△△に入るのはたくさんの型があります。

 

そんなわけで変数には型を指定できます。

「できます」なのでしなくても使えるのですがエラーチェックで使えたりメリットの方が大きいので変数の型を定義するクセをつけましょう。

 

値の範囲が少々荒っぽいですが必要最低限の説明で最初に使いそうな型だけ並べました。値の範囲は各々勉強の進度に応じて調べてください。

f:id:EnjoyExcel:20210627134001p:plain

 

Integer とLong はどっちも整数(数値)です。

Integer は -32,768 ~ 32767 の整数。 Long は-20億~+20億ぐらいの数値を扱う事が出来ます。 

 

金額とかはLongでセルの行、列を追いかける時はIntegerって感じでしょうか。

金額も小数が出てきたらSingleを使ったりここには書きませんでしたがさらに桁が多いDoubleを使う等様々です。

 

ちなみに私はセルを追いかける時も金額もほとんどLongばっか。説得力ないですよね。

前回書いたコードも Dim r As Long って書いてます。。。

 

以前の記事で「Option Explicit」の話をしたと思います。

enjoyexcel.hatenablog.com

 

これは「変数の宣言を強制する」という事を言ってます。

(訳として合ってるかは?です)

 

おいおい分かってくるのですが変数の宣言は先ほども書いたようにエラーチェックにも使えるしほかの人の書いたコードを読むときにいろんな変数が設定されていると「これ何のこと?」ってなります。

 

変数の型を定義しておくことで他者が見てもある程度見当がつくようにトレーサビリティ的な要素もありますので変数は宣言してから使いましょう。

次は罫線を書いたりコードをセル画面から実行できる様ボタンを設定するための説明をします。

 

ボタンを押したらメッセージボックスを出して処理をするか?の確認も付けたいのでメッセージボックスもセットしたいですね。 次回説明以降でしていきます。

VBA⑦ いろんなものが入る箱。変数を使いましょう。

f:id:EnjoyExcel:20210627110504p:plain

 

前回は最終行と最終列を取得する方法を紹介しました。

enjoyexcel.hatenablog.com

 

では取得した最終行と最終列ってどうやってコードに展開するの?というところを説明させていただきます。

 

Range("A1:E5")に情報が居るとしてE5のところを取得した行と列に変えたいと思います。

 

行 = cells(rows.count,1).end(xlup).row

列 = cells(1,columns.count).end(xltoleft).column

 

これで行も列も5が返ってきますよね。

 

ではRange("A1:E5") = "おはようございます”

 

これを Range("A1:55") = ”おはようございます” にしたらどうなるか・・・NGです。

f:id:EnjoyExcel:20210627112403p:plain

こうなります。ちなみに「実行時エラー1004」は何か?

実行時エラー1004はいろんな要因がありますが今回のエラーをざっくり言うと「そんな情報は無い」と言ってます。書き方が違います。コンパイルエラーだと思ってください。

 

Range("A1") = "おはようございます" はOKなので A1までは使えそうです。

やっぱりE5の表現の仕方が違っているかもしれないと思えてきます。

 

数回前の記事でこんな書き方したの覚えてますか?

Range("A1","E5")という書き方も出来ます。

enjoyexcel.hatenablog.com

 

ってことはRange("A1","E5")のA1とE5だけダブルクォーテーションで挟んでいる書き方をしているので分けて書けそうな気がしませんか?

 

で行、列は番号しかわかってないので前回勉強した Cells(行,列)の形なら番号だけでセルを表現できそうですよね。A1はそのままでE5だけ書き換えてみましょう。

 

Range("A1",cells(5,5)) = "おはようございます"

 

これ 行けそうでは?

 

私はエラー出ませんでしたがいかがでしょうか?

 

出来たかもしれませんが Cells(5,5) って書いてるから出来るわけでコードの中で取得した行、列をCellsに展開しないといけないからまだこれではダメです。

 

ここでやっと表題欄でお知らせした 変数 という優秀な万能社員の登場です。

 

いろんな値を収納したり次の工程に送ったりしてくれる情報のつなぎ役をしてくれます。

 

例えば・・・

 

r = cells(rows.count,1).end(xlup).row

c = cells(1,columns.count).end(xltoleft).column

 

r と c が変数です。

上記のコードはいろんな情報が入る「rという箱」と「cという箱」に行番号や列番号をセットする。というコードだと思ってください。

 

取得出来たのが5行目、5列目だったら r=5、c=5 という事なのでRangeのコードに置き換えるとRange("A1",cells(r,c)) という書き方が出来ます。

 

これだったら取得された行、列がどんな数字であっても変数が受けて次のコードに値を転用するのでどんな値が来ても対応できますね。一通りコードを書いてみます。

 

↓ここからコードです

Sub day20210626_1()

 

'変数の定義

Dim r As Long

Dim c As Long

 

’表の最終行と最終列を取得する

r = cells(rows.count,1).end(xlup).row

c = cells(1,columns.count).end(xltoleft).column

 

’表全体に おはようございます を入力

Range("A1",Cells(r,c)) = "おはようございます"

 

End sub

↑ここでコードは終わりです。

 

このコードを実行すると

r も c も5だったらA1からE5までおはようございますが入力されます。

 

上記のようなRangeの中で”A1”のような書き方とcellsを併記するようなコードの書き方はあまりやらないのですが範囲指定する際にRangeの後のかっこにはどんな情報を取れば良いのか勉強する為にやっています。もう少し後の記事でRange(Cells(1,1),cells(2,2))のようなコードの書き方を提供していきます。

 

話をコードの内容に戻します。 また新しいコードをさらっと入れましたね。 Dim ってなんぞや?

 

次回説明します。

VBA⑥ 表の最終行と最終列ってどうやって指定するの?

f:id:EnjoyExcel:20210627012718p:plain

 

では前回の続きです。

今回はオブジェクト、プロパティについて触れるのははちょっとだけにします。

 

前回はややこしかったですね。

enjoyexcel.hatenablog.com

 

仕事をする範囲が定まらないときって意外とありますよね。どうしよ??というところで話が終わっていました。

 

定まらないからできません。って言いたくないのでやるためにはどうしようかって考えましょう。・・・自力で探しに行って決めちゃえば良いのでは??と思いませんか?

 

実際にやってみましょう。

 

範囲を指定する時にはRange("A1:E5")といったように始まりの列行と終わりの列行を指定しましたよね。

 

そもそも列と行って大丈夫ですよね?

 

f:id:EnjoyExcel:20210416155154p:plain



上記が列と行です。

 

大体のデータって始まりの列行は分かりますよね。分からないのは終わりの列行です。

データを右下に見ていって最終行と最終列が交差するところが終わりの列行なのですがそれって手動で確認した結果ですよね?

 

毎回最終行と最終列を手動で確認してコード実行してたらそれはもはや手作業であり半自動というか残念な感じで・・・やめましょう。

 

方法はあります。

 

ほとんどの場合見出しの行が一番上にいるのでそこをカウントしたら最終列が取得出来そうな感じがします。

 

行は一番左の要素が番号とかだったら全部値が居そうな感じがしますがそうじゃないデータもあります。ただし一番左に来る要素って品番だったり氏名だったりほとんどは最終行に値が居るデータのはずだという(言い切って)事にして一番左の列を最終行を確認するための行に使います。

 

この時に途中で空欄が居てもよしとします。とにかく最後の行に値が居る列を最終行を取得するデータとして使います。そしてその確率が極めて高いのが表の一番左に来る要素だという事(にします)です。

 

では具体的にどうやって取得するのかといいますと・・・

 

行 = cells(rows.count,1).end(xlup).row

1は表の1列目を最終行を認識する行にした場合です。表の1列目がC列なら3になります。

 

列 = cells(5,columns.count).end(xltoleft).column

5は表の見出し行が5行目だった場合です。3行目が見出し行だったら3が入ります。

 

小文字になってますけどコンパイルエラー(構文違い)がなければ必要なところは大文字に変化します。

 

ではコードの意味を考えていきましょう。

 

まずは行。

Cells から始まってます。 Cells はRangeオブジェクトを返すプロパティです。

 

は? 何それ ? って最初のころ???しかなかったのを覚えています。

Cellsはオブジェクトじゃないのに先頭から始まってますので意味不明ですよね。

例えばCells(1,1)というコードはRange("A1")を返すといった具合になります。

 

CellsはRange(”*”)の形を返すのでRangeを先頭に書かなくてもよいぐらいに思っておいてください。あとでまた例外を言うのが確定しているので心苦しいのですがひとまずそう思ってください。

 

Cellsは Cells(行,列) という建付けになっています。Rangeとは逆ですね。

あーややこしい。

 

とっ散らかってますが落ち着いていきましょう。

ひとまずコードの前半分を考えます cells(rows.count,1) なので「1列目の行に入力があるセルを取得しますよ」って書いてます。

 

後半は .end(xlup) です。「いったん一番下のセルまで行ってから上に帰ってきて」と言ってます。

 

後半の後半 .row  は「行を返して」です

 

前半のコードと合わせると 「1列目の行に入力があるセルを取得したいのでいったん一番下のセルまで行ってから上に帰ってきて値のあるセルの行を返して」って事になります。 「=結果最終行だよね」 ってことです。

 

実際の表で考えてみましょう。

1列目の行は5行目から始まってますがいったん置いといて・・・

「最終行まで行って1行目に向かって帰ってきた時に最初にある値は結果最終行だよね。その行番号だけをください。」っていう考え方です。

ここでは15行目が最終行という事になります。

f:id:EnjoyExcel:20210627105051p:plain


列 = cells(5,columns.count).end(xltoleft).column

列も一緒です。 「5行目を起点に列方向へドン付きまで行って帰ってきたときに入力があるセルの列を教えて」 という事です。 「結果最終列だよね」ってことです。

 

これで守備範囲決まりましたね。

VBA⑤ Rangeだけでもいろんな書き方がありますよ。

f:id:EnjoyExcel:20210626223019p:plain

 

ではRangeについてもう少し掘り下げていきましょう。

オブジェクトの話しないって言ったくせにRangeオブジェクトって書いてますね。

Rangeはオブジェクトです。前回コードを書いたので少しだけ理屈を話していきます。

 

Rangeオブジェクトはセルを指しています。

 

前回の記事にもありますようにRange("A1")っていうのはセルA1のことです。

enjoyexcel.hatenablog.com

 

前回文字列を書く時はダブルクォーテーションで挟むと伝えましたね。ってことは("A1")も A1は文字列ですね。だから何だ?っていう話なんですけどイメージとしてはこのセルに対する仕事が明確に決まっている時に使う書き方だと思ってください。

 

セルの値=絶対値 みたいなイメージです。

 

必ずこのセルに結果を出すとき とか 見に行くセルの番地が決まっている時 等ですね。

 

範囲指定するときも上記の書き方を応用して範囲指定します。

セルA1からセルE5までを指定したいときは以下のようにコードを書きます。

 

Range("A1:E5")

 

こんな感じです。

 

ここからはオブジェクトの話を混ぜながら話します。

 

「Range("A1:E5").value = "おはようございます"」って書いて実行すると指定範囲全体におはようございますが並びます。

 

ん?なんか付いてきましたね。 .value って何?って思いませんか?

これはRangeオブジェクトのValueプロパティ(属性)です。

「RangeオブジェクトのValueプロパティ におはようございますを代入してください」という意味になります。

 

Value」は省略出来るので書いてなかっただけです。

(言わなくてすいません。まだ省略してるとこあります。今はスルーで。)

 

ただ書かないといけないプロパティも居ます。

例えば  Range("A1:E5").Interior.CororIndex = 3 といった感じです。

これを Range("A1:E5") = 3 と書くと指定範囲に3が並ぶだけになってしまいます。

 

細かく読みますと RangeオブジェクトのInteriorプロパティを使ってInteriorオブジェクトを取得しInteriorオブジェクトのColorindexプロパティに3を代入して。となります。

 

意味としては「セルA1~E5のセルの背景NoにインデックスNo.3(赤)を代入して」となります。 

 

全部こうではないのですがイメージとしてはオブジェクトに紐付いたプロパティを使って次のオブジェクトを関連付けているということになります。今はこのぐらいでやめましょう。

 

触れないといけない話題なのですが最初から理屈っぽく考えると上記のようになってしまうので次行きます。

 

他にも範囲の書き方はこんなものがあります。

 

Range("A1") 'セルA1

Range("A1:A5") 'セルA1~A5  

 

ここまでは上記に似たのが居ますよね。

 

Range("A1","A5") 'セルA1~A5

Range("A:C") 'A列からC列

Range("1:5") '1行目から5行目

 

ここまではなんとなくイメージできると思います。

 

ではセルに対してコードを作成するのにどのセルに対して仕事をするのか決まってない時ってあるの?って考えてみましょう。

 

・・・

・・・

・・・

 

結構ありますよね。

・最終行が毎回分からないデータを使って仕事をしているとき

・行全体に空欄があるデータにフィルタを設定すると空欄が居る列から下はフィルタにかからないとき(飛び地を考慮して最後までフィルタの範囲にしないといけません)

 

などなど・・・。結構あると思います。

 

これはそもそも空白行なんてつくったり最終行が分からないデータの方が悪いんですけど仕方ない時ってありますよね。(文句言えないとき・・・)

 

そんなときは仕事をする範囲が決まらないからセルを指定することが出来ません。

 

どうしましょ?次いきますか。。。

 

 

VBA④ オブジェクト、プロパティ、メソッド・・・後で考えよう。

f:id:EnjoyExcel:20210626185520p:plain

 

表題からすると投げやりな感じなのですが「前向きにやっていくために難しいことは一旦放置」という感じで進めます。

 

前回の記事です。

enjoyexcel.hatenablog.com

 

理屈がしっかりしてないといけない書籍では必ず教えているオブジェクト、プロパティ、メソッド。

この3点セットが分かってないとコードを書くために理屈が成立しないという理屈(ややこしい)なのですが最初は何言ってんのか分からないのでまず最低限のことだけ覚えてコードを書きます。

 

英語では「文法分かってないと書けないし聞いても分からない」って言いますけど私は「文法分かってても話せないし書けない人も居るでしょ?」と思っています。

 

理屈っぽくならずまずは「習うより慣れろ」を実践していきましょう。

 

コードの書き方の基本はこれ。

「左辺に右辺を代入」です。

 

先回書いたコードの Sub ・・・End Sub の間に

Range("A1") = "おはようございます"

って書いてみましょう。

 

文字列をコードに書く時はダブルクォーテーションで挟むというルールがあります。ご注意ください。

 

f:id:EnjoyExcel:20210626184139p:plain

 

range・・・って感じで小文字で書いていってEnterを押したあとRangeというように先頭が大文字になったらエラーなしという事です。これはどのコードにも言えますがあえて小文字で書いて大文字に変換されるかを確認し大文字になればコード間違えてないという感じでエラーチェックにも使えます。 ですからコードは小文字で書きましょう。

 

ではおはようございますの件に戻りましょう。

 

おはようございます と書いたコードの文字のあたりをマウスで選択してからVBEの上段「実行」タブを選択し 「Sub/ユーザーフォームの実行」を選択しましょう。

 

するとセル画面のA1セルに「おはようございます」って入っていませんか?

これは先ほど書きました「左辺に右辺を代入」という事からすると「A1セルにおはようございますを代入してください」という解釈になります。

 

これだけです。この繰り返しでマクロを構築します。命令を連続して書いて1手で作業完了するマクロを作成することで結果自動化・・・という流れになります。

 

続けてセルA2にこんにちは、セルA3にこんばんは と書いてみましょう。

f:id:EnjoyExcel:20210626185153p:plain

 

これを同じように実行してみましょう。

セルに文字は入力されましたでしょうか?

 

セルをクリアにするときは

Range("A1").ClearContents

Range("A2").ClearContents

Range("A3").ClearContents

 

これでOK。ClearContentsを使ってセルの値を消しました。

 

次はもう少しいろんなことが出来るコードを書いていこうと思います。

VBA③ コードを書きましょう。

f:id:EnjoyExcel:20210626175957p:plain

 

環境構築の続きです。前回の記事です。

enjoyexcel.hatenablog.com

 

コードを書く画面の話をしたら本格的な勉強を始めていきます。

プロパティウインドウやローカルウインドウ、イミディエイトウインドウはおいおいやっていきます。

 

ではコードを書く画面の説明です。

前回の記事にも書かせていただきましたがまずは標準モジュールをセットしてからコードを書きましょう。

 

オブジェクトブラウザの枠の中でマウスを右クリック。中央あたりに「挿入」というコマンドがでるので横に展開して「標準モジュール」を選択。

 

一番下に標準モジュールがセットされました。

 

その後はmodule1をダブルクリックしてください。

コードを書く画面がアクティブになり一番上に「Option Explicit」という表示が入ります。

 

この文字は変数について説明するときに具体的な説明をしますので今はスルーしてください。(消さないように注意願います)

 

ではここからはコードを書いていこうと思いますが・・・コードを書く画面白くないですか??

 

こんな感じのが見やすいですよね?変えましょう。

f:id:EnjoyExcel:20210626172836p:plain

VBE上段の「ツール」から「オプション」を選択。

オプションウインドウが出てくるので「エディタの設定」タブを選択。

f:id:EnjoyExcel:20210626173201p:plain

 

以下のように変えてみましょう。

あとフォントはMeiryo UI(日本語)、サイズはお好みでお願いします。

f:id:EnjoyExcel:20210626173447p:plain

最初の状態よりは分かりやすくなっているはずです。

ここからコードを書いていくとより分かりやすく感じると思います。

 

では今度こそコードを書きましょう。

 

「Option Explicit」の下に

sub 20210626_1()  と書いてEnterを押してみましょう。

名前は特に意味は無いですが今回はアンダーバーより前は日付にしています。

使ってはいけない文字もあります。使うとエラーで止まります。ここでは紹介しません。各自で検索してみてください。

 

f:id:EnjoyExcel:20210626174528p:plain



・・・わざとエラー出してみました。

subのあといきなり数字使うとエラーになります。等々いろんな縛りがあるのですがやっていけば分かるようになりますのでここでは流します。

 

ではsub の後を day20210626_1() にしてみましょう。

 

いろいろ変わりましたよね。

・Sub が青くなって先頭が大文字。

・()が黄色。

・青文字の End Sub が入力される。

 

Sub と End Sub の間にコードを書いていきます。

これが1つの命令という事になります。

 

End Sub の下に Sub day20210626_2() を入力するとすぐ上に 線が入ってコードが分割されると思います。 これでより命令の単位が明確になると思います。

 

ちなにみ先頭にある「Option Explicit」の上にも線が入ってますよね。

こんな感じでコードを書く画面は一つでも複数の命令を書くことが出来ます。

 

f:id:EnjoyExcel:20210626175639p:plain

最後に画像内で控えめに存在しているグレーの文字です。シングルクォーテーションの後に日本語が書かれています。見た感じ全部日本語ですからコードじゃなさそうです。

 

これはコメント機能です。シングルクォーテーションを最初に入れる事でコメントを記入出来ます。これはコードには含まれないのでいくらでも書いてください。

 

最初はコード自体が何をやっているか?どんな処理をしているのかを書いていけばよいと思いますが慣れてくるとコードは読めるので書かなくても良いと思います。

 

コードが読めるようになってきてからのコメントはなんでこのコードを書くに至ったのかという経緯や注意点を書いたほうが良いと思います。

 

引き継ぎ時、後で自分で読み返す際助けになります。

 

では命令を書いていきましょう。