|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
EXERCISE02:
train0201 |
|
2007-1-8 by KOMPas |
|
|
|
exercise02.xls |
|
|
|
■ |
「はがき印刷」で紹介した「転記マクロ」を、少しおさらいしておきます。 |
|
|
|
|
|
|
Sheet3.Cells(5, 5) =
Sheet10.Cells(23, 12) |
|
|
|
といったコードは、 |
|
|
右辺を、左辺に転記するコードであることをここで確認しておきます。 |
|
|
つまり、上記コードは、 |
|
|
右辺 Sheet10シートのセルL23 のデータを、 |
|
|
左辺 Sheet3シートのセルE5 に転記する |
|
|
というコードだ、ということです。 |
|
|
|
■ |
抽象的なものは説明するとややこしくなりがちですが、 |
|
|
変数X という、EXCELシート上にはない、仮想のデータを使って、下のコードをみてみると、 |
|
|
X = Sheet10.Cells(23, 12) |
|
|
|
このコードは、 |
|
|
右辺 Sheet10シートのセルL23 のデータを、 |
|
|
変数X
に転記する |
|
|
という意味になります。 |
|
|
変数Xは、EXCELシートに書き込まれているわけではなく、 |
|
|
あなたが使っているコンピュータが「覚えている」のだと、考えてください。 |
|
|
同じく、下のコードは、 |
|
|
Sheet3.Cells(5, 5) = X |
|
|
|
右辺 変数X のデータを、 |
|
|
左辺 Sheet3シートのセルE5 に転記する |
|
|
という意味になります。 |
|
|
2つをあわせて、 |
|
|
X = Sheet10.Cells(23, 12) |
|
|
|
Sheet3.Cells(5, 5) = X |
|
|
|
のように書くと、 |
|
|
Sheet3.Cells(5, 5) =
Sheet10.Cells(23, 12) |
|
|
|
と同じ意味になります。 |
|
|
|
■ |
次のコードは、 |
|
|
X = X * 3 + 10 |
|
|
|
右辺 Xを3倍して10足した数を、 |
|
|
変数X
に転記する |
|
|
という意味になります。 |
|
|
このコードがはしると、 |
|
|
変数Xは、もとのXの3倍に 10足した値にかわります。 |
|
|
|
■ |
もう1つ、 |
|
|
i = 23 |
|
|
|
j = 12 |
|
|
|
X = Sheet10.Cells(i, j) |
|
|
|
というコードは、 |
|
|
X = Sheet10.Cells(23, 12) |
|
|
|
と同じものであることをここで理解してください。 |
|
|
|
■ |
では、あなたのスケジューラを作ってみましょう。 |
|
|
|
|
■ |
空のEXCELを、新規作成でつくってください。 |
|
|
「ファイル」−「名前を付けて保存」で、適当な名前(カレンダ.xls等)で保存します。 |
|
|
|
■ |
まず、「カレンダ」様式を作ってみます。 |
|
|
|
|
■ |
様式をつくるときは、小さな同じ幅の列をたくさん作っておいてはじめるのがコツです。 |
|
|
(「はがき印刷」ではがき様式をつくったときのように) |
|
|
■ |
1週間が7日で、6週分あれば、全ての月のカレンダがこなせます。 |
|
|
■ |
めんどうくさいので、このEXCELの「カレンダ」シートを頁コピーしてください。 |
|
|
この「カレンダ」様式は、各1日分の幅は5列とることにして、日付で1行、データに2行とり、合わせて3行です。 |
|
|
|
(5列ではなく、13列にするとか、データに8行いるとか、あとで、おもいっきり変更してください。あとで) |
|
|
|
|
|
■ |
あなたのEXCELで、「カレンダ」様式をつくったシートがSheet1だとして説明を続けます。 |
|
|
|
■ |
「カレンダ」様式の月を変更すると、カレンダを作り直すマクロを書いてみましょう。 |
|
|
|
|
■ |
「ツール」「マクロ」「Visual Basic
Editor」の順に開いて、 |
|
|
左側の「プロジェクトエクスプローラ」で、Sheet1をダブルクリックします.。 |
|
|
右のコード欄の、一番上左側「(general)」と書いてあるところを、「Worksheet」に変更します。 |
|
|
その横が、(Declarations)
から、「SelectionChange」に変わったはずです。 |
|
|
右端の下向き三角▼をクリックして、リストを表示させ、 |
|
|
「Change」を選んでください。 |
|
|
すると、下のほうに、 |
|
|
Private Sub
Worksheet_Change(ByVal Target As Range) |
|
|
|
|
|
|
End Sub |
|
|
|
が表示されましたか? |
|
|
|
■ |
ここに以下のコードを(根性で)記述します。グリーン及び橙の文字/行はコメントなので、省略してください。 |
|
|
|
白抜き文字だけが正味のコードです。(34行です) |
|
|
|
'Target
はデータを書き換えた場所。rr はtarget左上セルの行番号、cc は左上セルの列番号。
|
|
|
|
rr = Target.Row |
|
|
|
cc = Target.Column |
|
|
|
If rr = 2 And cc = 28 Then |
|
|
|
'変更した場所が2行目 28列
(つまりAB2セル)なら、 |
|
|
|
'つまり、月を書き換えたら、 |
|
|
|
'マクロは仕事(カレンダーを設定しなおす)をはじめます。 |
|
|
|
yy
= Sheet1.Cells(2, 23) |
|
|
|
mm
= Sheet1.Cells(2, 28) |
|
|
|
dd1 = DateSerial(yy, mm, 1) |
|
|
|
dd2 = DateSerial(yy, mm + 1, 0) |
|
|
|
ddnn = Day(dd2) |
|
|
|
'対象月の1日の曜日を調べます。 |
|
|
|
'weekday関数は、引数に日付を与えると、 |
|
|
|
'曜日番号(日=1、月=2、火=3、水=4、木=5、金=6、土=7)を返します。 |
|
|
|
wd1 = Weekday(dd1) |
|
|
|
'魔法の定数mahoo を求めます。 |
|
|
|
mahoo = wd1 - 1 |
|
|
|
|
|
|
|
'カレンダの日付欄が、このシートでは、毎日5列右に移動し、 |
|
|
|
'週がかわると、3行下にとんで、B列に戻ることを確認してください。 |
|
|
|
For i = 0 To 5 |
|
|
|
For j = 0 To 6 |
|
|
|
|
'繰り返し処理を使った「転記」コードです。 |
|
|
|
|
'転記するのは、「■」というデータです。 |
|
|
|
|
'下のコードは、(6+3*1)行、(2+5*j)列 のセルに「■」を埋め込みます。 |
|
|
|
|
'i,j が増えていくにいたがって、42個のセルに順に「■」を埋め込みます。 |
|
|
|
Sheet1.Cells(6 + 3 * i, 2 + 5 * j) = "■" |
|
|
|
'同時に日付欄の1つ下のセルを空白にします。 |
|
|
|
Sheet1.Cells(6 + 3 * i + 1, 2 + 5 * j) = "" |
|
|
|
'ついでに日付欄の2つ下のセルを空白にします。 |
|
|
|
Sheet1.Cells(6 + 3 * i + 2, 2 + 5 * j) = "" |
|
|
|
|
|
|
|
Next j |
|
|
|
Next i |
|
|
|
'上記でみたように、 |
|
|
|
'カレンダの日付欄が、このシートでは、毎日5列右に移動し、 |
|
|
|
'週がかわると、3行下にとんで、B列に戻ることを確認してください。 |
|
|
|
'uu は uu=0
で始まりますが、1つの日付欄を処理する時に、uuは、1増えます。 |
|
|
|
'uu
は、42個の日付欄番号のようなもんだと思ってください。 |
|
|
|
'カレンダ様式への転記は、上記或いは下記のように、通常繰返し処理を使って行うと便利です。 |
|
|
|
'下方向へは i 、右方向へは j
の値を1つずつ増やすことで、 |
|
|
|
' Sheet1.Cells(6 + 3 *
i, 2 + 5 * j) |
|
|
|
'つまり、6 + 3 * i 行目
(6行目から始めて、i * 3 行ずつ下に移動した行) |
|
|
|
' 2 + 5 * j 列
(2列目から始めて、j * 5 列ずつ右に移動した行) |
|
|
|
'の日付欄にuu
から計算できるデータを書き込みます。 |
|
|
|
uu
= 0 |
|
|
|
For i = 0 To 5 |
|
|
|
For j = 0 To 6 |
|
|
|
uu = uu + 1 |
|
|
|
ddx = uu - mahoo |
|
|
|
'おやまぁ不思議。ここで求めたddxが、日付です。(あたりまえ) |
|
|
|
'(余韻) |
|
|
|
'日付ddxが、1 より小さいとき、 |
|
|
|
'及び、当月の末日より大きいときは、日付ddxをかきこまない。 |
|
|
|
If ddx < 1 Then |
|
|
|
ElseIf ddx > ddnn Then |
|
|
|
Else |
|
|
|
Sheet1.Cells(6 + 3 * i, 2 + 5 * j) = ddx |
|
|
|
End If |
|
|
|
Next j |
|
|
|
Next i |
|
|
|
'「カレンダの基本」といいたものがあるかもしれませんが、僕は知りません。 |
|
|
|
'わかりやすいコードをと、思いましたが、 |
|
|
|
'「計算が理解できない」かもしれません。 |
|
|
|
'試してみて、うまくいくようなら、コピーしてお使いください。 |
|
|
|
|
|
|
|
'日付欄の埋め込みを2回に分けて行っていますが、 |
|
|
|
'勿論1回ですませてかまいません。 |
|
|
|
|
|
|
|
'カレンダを作り終わったら、お約束の印刷マクロを書き加えます。 |
|
|
|
www =
MsgBox("印刷しますか", vbOKCancel) |
|
|
|
If www = vbCancel Then |
|
|
|
Else |
|
|
|
Sheet1.Range("a1:ak24").PrintOut 1 |
|
|
|
End
If |
|
|
|
|
|
|
|
End If |
|
|
|
|
■ |
つぎに、「予定表」シートを作ります。 |
|
|
|
|
■ |
「はがき印刷」でもやったように、一覧表は「連結」しないほうがなにかと便利です。 |
|
|
様式は「連結」でつくったほうが便利で、一覧表はそうでないほうが便利です。 |
|
|
「連結」を使うと、コードを書くときにいらぬ手間がかかります。 |
|
|
また、EXCELの強力な「並び替え」や、「コピー」するのがややこしくなってしまいます。 |
|
|
|
■ |
4行目A列から順に、年、月、日、曜日、本文、種類、記録者 の見出し欄を作ります。 |
|
|
5行目以下が、カレンダに表示する「予定」を記述する場所ですが、 |
|
|
曜日欄にはEXCELシート関数で、下記を書き込んでみてください。 |
|
|
=IF(A5="","",MID("日月火水木金土",WEEKDAY(DATE(A5,B5,C5)),1)) |
|
|
(もっと簡単な式があるのでしょうが、とりあえずつくってみました) |
|
|
種類欄は、「私用,仕事,暦」のリストにしてみます。(このEXCELではリスト以外も入力可) |
|
|
リストの作り方はここでは省略します。 |
|
|
(リスト以外も入力可にするには、 |
|
|
「データ」「入力規則」「エラーメッセージ」で、 |
|
|
「無効なデータが入力されたら云々・・・」のチェックをはずします。 |
|
|
|
■ |
あなたのEXCELで、「予定表」をつくったシートがSheet2だとして説明を続けます。 |
|
|
|
|
|
|
■ |
「カレンダ」をダブルクリックすると、「予定表」シートから当月データ読み込むマクロを作ります。 |
|
|
|
|
■ |
「ツール」「マクロ」「Visual Basic
Editor」の順に開いて、 |
|
|
左側の「プロジェクトエクスプローラ」で、Sheet1をダブルクリックします.。 |
|
|
今コードを書き込もうとしているのは、「予定表」ではなく「カレンダ」です。 |
|
|
右のコード欄の、一番上左側「(general)」とあれば、「Worksheet」に変更します。 |
|
|
その横には、「Change」等が表示されています。 |
|
|
右端の下向き三角▼をクリックして、リストを表示させ、 |
|
|
「BeforeDoubleClick」を選んでください。 |
|
|
すると、下のほうに、 |
|
|
Private Sub
Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) |
|
|
|
|
|
|
End Sub |
|
|
|
が表示されましたか? |
|
|
|
■ |
ここに以下のコードを(根性で)記述します。グリーン及び橙の文字/行はコメントなので、省略してください。 |
|
|
|
白抜き文字だけが正味のコードです。(34行です) |
|
|
'まず、当月を求める |
|
|
|
yy =
Sheet1.Cells(2, 23) |
|
|
|
mm = Sheet1.Cells(2, 28) |
|
|
|
dd1 = DateSerial(yy, mm,
1) |
|
|
|
dd2 = DateSerial(yy, mm +
1, 0) |
|
|
|
ddnn
= Day(dd2) |
|
|
|
'対象月の1日の曜日を調べます。 |
|
|
|
'weekday関数は、引数に日付を与えると、 |
|
|
|
'曜日番号(日=1、月=2、火=3、水=4、木=5、金=6、土=7)を返します。 |
|
|
|
wd1 =
Weekday(dd1) |
|
|
|
'魔法の定数mahoo を求めます。 |
|
|
|
mahoo
= wd1 - 2 |
|
|
|
'カレンダ表示するための検索条件を求めます。 |
|
|
|
key = Sheet1.Cells(2, 13) |
|
|
|
'カレンダの内容表示の初期化 |
|
|
|
For i = 0 To 5 |
|
|
|
For j = 0 To 6 |
|
|
|
'繰り返し処理を使った「転記」コードです。 |
|
|
|
'i,j が増えていくにいたがって、42個の日付欄を日付はそのままで、内容欄だけ空白を埋め込みます。 |
|
|
Sheet1.Cells(6 + 3 * i + 1, 2 + 5 * j) = "" |
|
|
|
Sheet1.Cells(6 + 3 * i + 2, 2 + 5 * j) = "" |
|
|
|
|
|
|
|
Next j |
|
|
|
Next i |
|
|
|
'予定表から検索して、該当したら、カレンダに書き込みます。 |
|
|
|
i = 5 |
|
|
|
Do Until Sheet2.Cells(i,
1) = "" |
|
|
|
If Sheet2.Cells(i, 1) = yy
And Sheet2.Cells(i, 2) = mm Then |
|
|
|
'予定表データの年/月が、当月だったらBingo! |
|
|
|
'予定表データがキーワードに該当するか否かをコード上で判断し、 |
|
|
|
'予定表データの日付から、カレンダのどの位置に表示すべきかを計算でもとめます。 |
|
|
|
'予定表 |
|
|
|
'ddx は予定表データの日付 |
|
|
注意! |
|
|
ddx = Sheet2.Cells(i, 3) |
|
|
2007-2-4 下記バグってました。お恥ずかしい。 |
|
|
バグ修正! |
'日付から、魔法の定数をプラスして求めた値uu は、カレンダの日付欄番号(但し始めは0番)となります。 |
|
'日付から、魔法の定数をマイナスして求めた値uu は、カレンダの日付欄番号(但し始めは0番)となります。 |
|
uu
= ddx + mahoo |
|
|
uu
= ddx - mahoo |
|
|
'uu を 7で割った商は、カレンダの週番号tgti(0から始まる)。余りが曜日番号tgtj(0から始まる)に該当します。 |
|
|
tgti = Int(uu / 7) |
|
|
|
tgtj = uu Mod 7 |
|
|
|
'下段は、予定本文と予定種類をあわせたデータでkeyをさがします。 |
|
|
|
'予定本文 |
|
|
|
caldata = Sheet2.Cells(i, 5) |
|
|
|
'予定種類 |
|
|
|
shurui = Sheet2.Cells(i, 6) |
|
|
|
If
shurui = "暦" Then |
|
|
|
'種類が暦のとき、本文を上段に転記します。 |
|
|
|
If Sheet1.Cells(6 + 3 * tgti + 1, 2 + 5 * tgtj) & "" =
"" Then |
|
|
|
Sheet1.Cells(6 + 3 * tgti + 1, 2 + 5 * tgtj) = caldata |
|
|
|
Else |
|
|
|
Sheet1.Cells(6 + 3 * tgti + 1, 2 + 5 * tgtj) = Sheet1.Cells(6 + 3 *
tgti + 1, 2 + 5 * tgtj) & vbLf & caldata |
|
|
End If |
|
|
|
ElseIf key & "" = "" Then |
|
|
|
'キーワードに何も指定がないとき、暦以外のデータ全てを下段に転記します。 |
|
|
|
If Sheet1.Cells(6 + 3 * tgti + 2, 2 + 5 * tgtj) & "" =
"" Then |
|
|
|
Sheet1.Cells(6 + 3 * tgti + 2, 2 + 5 * tgtj) = caldata |
|
|
|
Else |
|
|
|
Sheet1.Cells(6 + 3 * tgti + 2, 2 + 5 * tgtj) = Sheet1.Cells(6 + 3 *
tgti + 2, 2 + 5 * tgtj) & vbLf & caldata |
|
|
End If |
|
|
|
ElseIf InStr(caldata & shurui, key) Then |
|
|
|
'上段キーワードに指定した文字が、予定表本文または種類データに含まれるとき、転記します。 |
|
|
If Sheet1.Cells(6 + 3 * tgti + 2, 2 + 5 * tgtj) & "" =
"" Then |
|
|
|
Sheet1.Cells(6 + 3 * tgti + 2, 2 + 5 * tgtj) = caldata |
|
|
|
Else |
|
|
|
Sheet1.Cells(6 + 3 * tgti + 2, 2 + 5 * tgtj) = Sheet1.Cells(6 + 3 *
tgti + 2, 2 + 5 * tgtj) & vbLf & caldata |
|
|
End If |
|
|
|
End If |
|
|
|
End
If |
|
|
|
i = i
+ 1 |
|
|
|
Loop |
|
|
|
|
|
|
|
'カレンダへの転記が終わったら、お約束の印刷マクロを書き加えます。 |
|
|
|
www =
MsgBox("印刷しますか", vbOKCancel) |
|
|
|
If www = vbCancel Then |
|
|
|
Else |
|
|
|
Sheet1.Range("a1:ak24").PrintOut 1, 1 |
|
|
|
End
If |
|
|
|
|
|
|
■ |
ここまでで、一仕事完了です。 |
|
|
|
|
■ |
本来のお題目である「データベースとのやりとり」マクロには、ここまで何も手をつけていませんが、 |
|
|
「予定表」に書き込んで、これを「カレンダ」に出力して印刷する、 |
|
|
ということはできるわけです。 |
|
|
ここから先は、train0202頁に書き込みます。 |
|
|
sampleとしては、ここまで同様、このEXCEL「予定表」「カレンダ」にコードを書き込んでいます。 |
|
|
(このEXCEL上のVBAコード本体のほうでは、「カレンダ」は、Sheet3で処理していることに、ご注意) |
|
|
|
train0202 |
|
|
|
|
|
KOMPas |
|
|
|
|
TEL |
|
0949-29-2334 |
|
|
FAX |
|
0949-29-2332 |
|
|
携帯 |
|
090-6777-4241 |
|
|
email |
|
kompas@enissi.com (返事は期待しないでください) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|