<4D F736F F D C BE48A C8E97E189EF95C58F6B8FAC2E646F63>

Transcription

1 AC パソコン倶楽部 2 月例会 資料 1912 年 02 月 23 日 ( 水 ) 10:00~ 芦屋市民センター 401 室 元公民館長前田文也 1 はじめに マクロ入門 ~ 面倒なことはマクロを使おう ~ 資料サイト 掲示板 ID: member PW: 芦屋川を一ひねり a4yagawa 2 こんな時に 1) 毎回同じ操作をするのが面倒列の非表示 再表示 背景色とフォントをまとめて変更 2) 初心者が操作するとデータを壊しそう名簿の並べ替え ( 範囲選択を誤ると役に立たぬデータになってしまう ) 3 マクロとは 1) DOS 版 Lotus の頃キー トレース ( キー操作の記録 ) Ex:[R]{R}{D}{?}{D} 2) 現在は VBA(Visual Basic for Applications) プログラム Excel で慣れると Word やその他でも応用できる 3) 高度なことも出来るやりたいことを ネットで検索する Ex: 満点になったら音を出す ( 音を出す機能は Excel にはない ) 4) 一太郎シリーズでも 4 モグラはかせのドリル を見てみると小 中学生が使うことを前提に作った教材集です マクロの見本 大人ドリル としてお使い下さい 加減乗除のほかに ローマ字打ち ( ブラインドタッチの練習に ) 百人一首もあります ( 大人ドリル : たまには2 桁 -2 桁のベストタイムをねらってみる ) ほかに とても大きなキッチンタイマーなどもあります 1) 使ってみるなら マクロを有効 にして読み込む 2) マクロが無効な場合は マクロを有効にする方法 が表示されます 3) マクロプログラムを覗きたい場合は マクロを有効 で読み込み シートタブ 数式バー 列行見出し などを表示して ( スタートボタンを押してしまうと何もできない ) 4) Ctrl+M を押すと 問題を印刷するためのページが出ます 5) とじる ボタンで閉じないと いつものツールバーが消えたままになります 1

2 5 用紙に印刷するときに Ex: 問題用紙 を印刷し 答えがついた 解答編 も印刷したい が ワンタッチ! Ex: 会計係 さん立替のレシート 消耗品費と事業費の伝票を起こしたい Sheet1 入力表 Sheet2 伝票 立替金領収書 1 行会計帳 Sheet3 1 行会計帳記録 Ex: 会員名簿 + 会費領収書名前入り領収書を作る Sheet1 3 人分領収書 Sheet2 会員名簿 Sheet3 予備会員名簿 3 人分印刷すると 名簿の上位 3 人部分を 4 人目以下の会員に置き換えて 次の 3 人を印刷する 一番上が空欄ならば終了する 6 マクロプログラムは 自動記録 で書く ツール マクロ 新しいマクロの記録 記録停止は ボタン(2003 まではシート上の窓に 2007 以降は下のバー内または開発タブ内 ) 2007 以降は リボン 内の 開発 タブにマクロがある 開発 タブがない場合は ネットで調べて VBA を知らなくても プログラムが書けるのが良いところ 7 マクロプログラムは ネットからコピペ自動記録で書けないプログラムは 手で書く ( とミスをするので ネットで似たようなのを探してコピーペーストする ) Excel マクロ起動時 などのキーワードで検索してみる 8 よく使うマクロは ボタンに登録 ( これが活用するポイント ) ボタンは 表示 ツールバー の フォーム ( 同じようなのがコントロールツールボックスでも表示されるので注意!) のボタンを 普通の画像にもマクロを登録できる ショートカットキーに登録 ( すでに使われているショートカットキーがつぶれるが ) も出来る マクロ名を付けるときにショートカットキーを登録できる 9 ブック読み込み時に自動で読み込むマクロ起動時に読み込むマクロは名前が決まっている その他に 再計算をしたら実行されるマクロ など 多様変数の宣言 変数の種類など 勉強することはいっぱい ( 課題があればこそ 勉強ができる 難題は勉強のチャンス ) 10 ちょっとしたこつ並べ替え範囲指定を普通にやると B3:F12 などと記録される データが増えると 範囲を書き直す必要が出てくる End キーを使って指定すると データの有効範囲が指定される (Null のセルが無いなら ) コメントアウト機能や注意点についてマクロプログラム内にメモしておく コメントアウトは をつけると その行の最後まで無視される 別シートにマニュアルどう使うか 作った本人が忘れるので 説明書を新しいシートに書いておく 2

3 11 マクロを使えるようにする (1) Excel ツール マクロ セキュリティ でセキュリティレベルを 中 に (2) Excel マクロ をリボンに表示する オフィスボタン Excel のオプション 2 セキュリティの設定 開発 マクロのセキュリティ で 3 信頼できる場所の設定 PC 内のあるフォルダに使用するマクロ入りのファイルを置くと警告無しに開ける (3) Excel マクロ をリボンに表示する ファイル オプション リボンのユーザー設定 で 開発 タブを表示する 2 セキュリティの設定 開発 マクロのセキュリティ で 3 信頼できるファイルの設定 一度マクロを開いたファイルは次回から開ける セキュリティセンターの信頼済みドキュメントでリセット 無効化できる 宿題 名刺を作る ファイルに 1 印刷 ボタン 1 印刷範囲の設定 21 部印刷 2 閉じる ボタン 1 上書き済みにする 2Excel を終了 3 飾り罫変更 111 箇所を範囲指定 2 さて どうします? 創造して下さい 出来上がったら 掲示板に貼って 感想 意見交換を まえだぶんや maeda@cafenadi.com 3

4 < 資料編 > モグラはかせのドリルで使用している関数 マクロプログラムです これ以外に入力規則などの機能を使って います < 資料 1> 関数 使用している関数についての説明 1 =" "&JIS(FIXED(HA4,0,TRUE))&" + "&JIS(FIXED(HC4,0,TRUE)) たしざん1 の問題提示欄 元の問題データのセルには 半角数字で書いてあるので FIXED(HA4,0,TRUE) で 文字列 ( 数値を文字としての数字 ) に直して JIS( ) で 全角の文字に直して " "& 全角数字 &" + "& 全角数字で 文字列の足し算をしてます 先頭の = は 数式であることを表しています 2 =IF(D4="","",IF(D4=HE4,+HG4,+HF4)) モグラはかせのひとこと 欄の数式です IF(D4="","", 表示 ) で 回答欄が空欄なら 表示は Null そうでなければ表示 IF(D4=HE4,+HG4,+HF4) で 回答欄と正解欄が等しければ よろしい 表示を そうでなければ まちがい 表示を 3 =IF(Mondai!D2< ,"100 点 しかも! はやい!"," おめでとう! 100 点 ") manten シートの 100 点称賛のメッセージの数式です Mondai!D2< は 問題シートの得点合計欄が =2 分 30 秒より小さいか です 日時時刻は 1 が 1 日です 24 で割って 60 で割って を小数点以下に 10 進数で表します 分 秒表示にするには セルの書式設定 の 表示形式 を 時刻 にして ユーザー設定 でひらがな表示に変えて使っています 4 =NOW() manten シート等の 日時欄です Now() は 現在の日時時刻を呼び出す関数です セルの書式設定で月 日 ( 曜 ) の表示にしています 5 =Mondai!B2 manten シート等の 問題欄です Mondai シートの B2 セルの内容を参照しています これで Mondai シートの B2 を変えると Manten シート Start シート Tokuten シート Timeup シートの問題欄の表示が変わります これによって書き換え忘れがちょっとは減ります 6 =IF(D21="","",IF(D21=HE21,+HI21,0)) 得点 欄の数式です 無回答時は空欄にするために IF 関数が入れ子になっています 正解時は HI21 の数値を参照しています 7 =SUM(F4:F48) 得点合計 欄の数式です SUM は範囲の合計を求める関数 問題数を変えると 範囲を変更する必要あり 8 =HG2-HH2 経過時間 欄の数式です HG2 の =now()( 現在時刻 ) から スタート時刻の HH2 を引き ユーザー定義の時刻表示 9 =(Mondai!F2/Mondai!HI4)&" もん /"&Mondai!A1 Tokuten シート Timeup シートの 問題数 欄の数式です 問題シートの得点合計を 一番目の問題の配点で割って 正解した問題数を求めています 分母は 問題シートの問題数を参照ローマ字など問題数の多いシートで授業時間が終わりになってとか 四字熟語などギブアップした時の正解した問題数を表示するためです 4

5 < 資料 2> マクロ ' モジュール宣言部に記述ここから ' 音楽演奏のモジュール宣言 Public Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" ( _ ByVal pszsound As String, _ ByVal hmod As Long, _ ByVal fdwsound As Long _ ) As Long Public Const SND_ASYNC = &H1 ' タイマー関連の共通変数の宣言 Public starttime ' Application.OnTime 時刻変数を宣言 Public OnTimerStop As Boolean ' Application.OnTime 停止変数を宣言 ' スリープ関数 (API) の宣言 Declare Sub Sleep Lib "kernel32" (ByVal dwmilliseconds As Long) ' モジュール宣言部に記述ここまで Sub Auto_Open() ' 読み込み時実行マクロ atode() rename Auto_Open() ' マクロ記録日 : 2011/7/25 ユーザー名 : Bunya_Maeda ' 数式バー ステータスバー 行列番号 シート見出しを非表示にし入力後の移動方向を下に設定 ' スタートシートへ移動 Sheets("Start").Select ' 数式バー ステータスバー 行列番号 シート見出しを非表示に Application.CommandBars("Formatting").Visible = False ' 書式バーを消す Application.CommandBars("Standard").Visible = False ' 標準バーを消す Application.CommandBars("Drawing").Visible = False ' 図形描画を消す With Application.DisplayFormulaBar = False ' 数式バーを消す.DisplayStatusBar = False ' ステータスバーを消す Call EraseWorkbookTabs ' シート見出し 列行番号を消す ' 入力後の移動方向を下に設定 With Application.MoveAfterReturn = True ' 入力後移動を有効に.MoveAfterReturnDirection = xldown ' 入力後下へ 1セル移動 Sub start_ontime() ' スタートボタンマクロ start_ontime Macro ' マクロ記録日 : 2011/7/25 ユーザー名 : Bunya_Maeda ' 問題シートへ移動 回答欄のクリア Sheets("Mondai").Select ' 問題シートへ移動 Range("D4:D103").Select ' 回答欄を選択 100 問目まで Selection.ClearContents ' クリア Call EraseWorkbookTabs ' シート見出し 列行番号を消す ActiveSheet.ScrollArea = "D4:D54" ' ScroolArea 46 問目まで ' Manten シートの準備 Worksheet_Calculate にともなってここに移動 Sheets("Manten").Select '"Manten" シートに移動 Call EraseWorkbookTabs ' シート見出し 列行番号を消す Range("HA4:HB9").Select ' メッセージを並べ替え Selection.sort Key1:=Range("HA4"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _ :=xlpinyin Sheets("Mondai").Select ' 問題シートへ移動 ' スタート音 Call start_music ' スタート音 ( 時報 ) を鳴らす ' 問題の並べ替え 並べ替えをしない場合は このセクション全行をコメントアウトに Range("GZ4:HI4").Select '1 行目を選択 Range(Selection, Selection.End(xlDown)).Select ' データの最終行までを選択 Selection.sort Key1:=Range("HH4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _ :=xlpinyin 'HH4 乱数列をソートキー列として並べ替え ' タイマーのセット Range("HG2").Select 'HG2 現在時刻を選択 Selection.Copy ' コピーする Range("HH2").Select 'HH2 スタート時刻を選択 Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ 5

6 False, Transpose:=False Application.CutCopyMode = False ' 形式を選択して貼り付け ( 値 ) ' 制限時間のセット starttime = Now + TimeValue("00:10:15") ' 制限時間を変更する場合は TimeValue を書き換え OnTimerStop = False ' Application.OnTime 停止変数の初期化 Application.OnTime starttime, "usertimeup" ' 最初の解答欄に移動 ' 左上を選択 Range("D4").Select '1 番目の回答欄を選択 Sub yameru() ' やめるボタンマクロ ' 保存せずに Excel を終了 ' マクロ記録日 : 2010/11/9 ユーザー名 : Bunya_Maeda ' やめるのか確認ボックスを表示し " はい " なら Tokuten シートに移動 Dim intret As Integer Dim strmsg As String strmsg = " やめますか?" intret = MsgBox(strMsg, vbyesno + vbquestion, " おわるの?") If intret = vbyes Then Sheets("Tokuten").Select '" はい " の場合 Tokuten シートに移動 Call EraseWorkbookTabs ' シート見出し 列行番号を消す End If '" いいえ " の場合は何もしない Sub CloseFileQuitExcel() ' とじるボタンマクロ ' 保存せずに Excel を終了 ' マクロ記録日 : 2010/11/9 ユーザー名 : Bunya_Maeda ' 数式バー ステータスバー 行列番号 シート見出しを表示に Application.CommandBars("Standard").Visible = True ' Application.CommandBars("Formatting").Visible = True Application.CommandBars("Drawing").Visible = True With ActiveWindow.DisplayHeadings = True.DisplayWorkbookTabs = True With Application.DisplayFormulaBar = True.DisplayStatusBar = True ' 入力後の移動方向を右に設定 Application.MoveAfterReturnDirection = xltoright ' 上書き済にして Excel を終了 ThisWorkbook.Saved = True Application.Quit Sub usertimeup() ' 制限時間が来たら起動するマクロ ' Excel の終了を呼び出す ' マクロ記録日 : 2010/11/9 ユーザー名 : Bunya_Maeda If OnTimerStop Then Exit Sub ' ゲームオーバーシートの呼び出し Sheets("Timeup").Select Call EraseWorkbookTabs ' Application.OnTime 停止変数が True ならこの Sub を実行しない ' シート見出し 列行番号を消す Call timeup_music ' 制限時間のアナウンスを鳴らす MsgBox " しゅうりょうします " & vbcrlf & " どこまでやったのか メモしましょう " Call CloseFileQuitExcel Sub sound_music() ' 満点時の音楽を演奏するマクロ ' マクロ記録日 : 2011/08/12 ユーザー名 : Bunya_Maeda Dim WrkSndFile As String WrkSndFile = ThisWorkbook.Path & " sound wav.wav" PlaySound WrkSndFile, 0, SND_ASYNC Sub start_music() 6

7 ' スタート時の音楽を演奏するマクロ ' マクロ記録日 : 2011/08/12 ユーザー名 : Bunya_Maeda Dim WrkSndFile As String WrkSndFile = ThisWorkbook.Path & " sound start.wav" PlaySound WrkSndFile, 0, SND_ASYNC Sub timeup_music() ' 満点時の音楽を演奏するマクロ ' マクロ記録日 : 2011/08/12 ユーザー名 : Bunya_Maeda Dim WrkSndFile As String WrkSndFile = ThisWorkbook.Path & " sound timeup.wav" PlaySound WrkSndFile, 0, SND_ASYNC Sub EraseWorkbookTabs() ' EraseWorkbookTabs Macro ' マクロ記録日 : 2011/9/3 ユーザー名 : Bunya_Maeda With ActiveWindow.DisplayHeadings = False ' 行列番号を消す.DisplayWorkbookTabs = False ' シート見出しを消す Sub manten() ' Worksheet_Calculate がCall する Worksheet_Calculate にともなって Owatta を書き換え OnTimerStop = True ' Application.OnTime 停止変数を 停止 に設定 Sheets("Manten").Select '"Manten" シートに移動 ' 左上を選択 Call sound_music ' 賞賛の音楽を鳴らす Sub MugenMondai() ' ' MugenMondai Macro ' マクロ記録日 : 2011/9/18 ユーザー名 : Bunya,Maeda ' ' Keyboard Shortcut: Ctrl+m ' Sheets("MugenMondai").Select Sub MugenSort() ' 無限問題の問題変更ボタンマクロ MugenSort Macro ' マクロ記録日 : 2011/9/18 ユーザー名 : main ' Mondai シートで 乱数列による並べ替えを行う Application.ScreenUpdating = False ' 画面を更新しない Sheets("Mondai").Select ' Mondai シートに移動 Range("GZ4").Select ' 問題部分を選択 Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.sort Key1:=Range("HH4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _ :=xlpinyin ' 乱数列をキーにして並べ替え Sheets("MugenMondai").Select ' MugenMondai シートに戻る Application.ScreenUpdating = True ' 画面を更新する Sub MugenErase() ' MugenErase 解答消去ボタン回答欄を白文字にするマクロ ' マクロ記録日 : 2011/8/27 ユーザー名 : B,Maeda Sheets("MugenMondai").Select ' MugenMondai シート Application.ScreenUpdating = False ' 画面を更新しない Range("G7:G26,N7:N26").Select ' 回答欄を選択 Range("N26").Activate With Selection.Font.Name = "MS Pゴシック ".FontStyle = " 標準 ".Size = 20.Strikethrough = False 7

8 .Superscript = False.Subscript = False.OutlineFont = False.Shadow = False.Underline = xlunderlinestylenone.colorindex = 2 ' 文字色を白に Application.ScreenUpdating = True ' 画面を更新する ' 左上に戻る Sub MugenDisplay() ' MugenDisplay 解答表示ボタン回答欄を赤文字にするマクロ ' マクロ記録日 : 2011/8/27 ユーザー名 : B,Maeda Sheets("MugenMondai").Select ' MugenMondai シート Application.ScreenUpdating = False ' 画面を更新しない Range("G7:G26,N7:N26").Select ' 回答欄を選択 Range("N26").Activate With Selection.Font.Name = "MS Pゴシック ".FontStyle = " 標準 ".Size = 20.Strikethrough = False.Superscript = False.Subscript = False.OutlineFont = False.Shadow = False.Underline = xlunderlinestylenone.colorindex = 3 ' 文字色を赤に Application.ScreenUpdating = True ' 画面を更新する ' 左上に戻る Sub MugenPrint() ' MugenPrint 印刷するボタン解答付き 解答無しの 2 枚を印刷するマクロ ' マクロ記録日 : 2011/8/27 ユーザー名 : B,Maeda Sheets("MugenMondai").Select ' MugenMondai シート Range("B5:O26").Select ActiveSheet.PageSetup.PrintArea = "$B$5:$O$26" ' 印刷範囲を設定 Call MugenDisplay ' 解答を表示 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' 印刷 Call MugenErase ' 解答を消去 ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ' 印刷 Sheet2 に書き込んでいるマクロ Public Sub Worksheet_Calculate() ' シート計算時に起動する 満点チェック Macro ' マクロ記録日 : 2011/9/11 ユーザー名 : Bunya,Maeda ' MicrosoftExcelObjects の Sheet2(Mondai) 下に置くこと If Range("F2").Value > 99 Then ' 100 問以上の場合は要調整 Sleep 1500 ' 回答の結果を見るために 1.5 秒待つ End If Call manten ショートカットキーで起動するマクロ Sub mugen() ' mugen Macro ' マクロ記録日 : 2011/9/18 ユーザー名 : main ' Keyboard Shortcut: Ctrl+m Sheets("MugenMondai").Select ' Manten プロシージャを呼び出す 8