Ｓ・Ｍ・C (Ｓｉｍｐｌｅ.Ｍａｃｒｏ.Ｃｒｅａｔｅ)　～エクセルマクロ日記～

本広告は、一定期間更新の無いブログにのみ表示されます。
ブログ更新が行われると本広告は非表示となります。  

朝６時頃、散歩をしましたがとても涼しく感じました。

植物は敏感で、真夏の強い日差しで弱っていた花にも
新芽が出始めました。

日中はまだまだ暑いですが、草花に優しい秋が
確実に近づいているんだなぁって実感した朝でした。

＊=================================＊

それでは本題です。

昨日の「未収残高内訳チェックリスト」の続きです。

まず、経理ソフトからエクセルへ出力したデータをみてみると、
摘要欄に「誰の何月分」という形で入力されてますが
そのままの状態では、請求ソフトとの照合の時に
キーとして使えそうにありません。



キーとして使うには、名前と月に分解した方がよさそうです。
例えば、↓な感じです。



その後、月毎に並べ替えます。



こんなイメージでエクセルマクロ（ＶＢＡ）を使って、
未収金データを作っていきたいと思います。

今回のポイントは、セル内の文字列の抽出。

名前と月に分けるには、
経理ソフトでは、摘要欄に入力するとき
山●秀●（３月）というふうに、
名前を入力して、”（”を付けて月を入力するという
決まり事があるそうです。

なので、”（”を基準にして、
前は名前、後ろは月という判断で
関数を使い文字列を抽出します。

その関数が、Ｍｉｄ関数です。

mid(セル,抽出開始位置,抽出文字数）

セル 　　　　　　：抽出したい文字列が入力されているセルを指定します
抽出開始位置 ：抽出したい文字列の先頭文字の位置を、左から数えた文字数で指定します
抽出文字数 　 ：抽出したい文字数を指定します

さらに、Ｍｉｄ関数で指定する、抽出開始位置や抽出文字数も
関数を使って取得します。

それが、
InStr関数です。
指定した文字列の中から、指定した文字列を検索して、見つかった位置を返します。

InStr([start,]string1,string2[,compare])

引数startは省略可能です。検索を始める位置を指定します。
引数string1には、検索の対象となる文字列を指定します。
引数strign2には、引数string1の中から検索する文字列を指定します。
引数compareは省略可能です。文字列を比較するモードを指定します。

その二つを組み合わせて、

名前を抽出する時は、
Mid(Sheets("元帳").Range("f3"), 1, InStr(1, Sheets("元帳").Range("f3"), "（") - 1)

月を抽出する時は
Mid(Sheets("元帳").Range("f3"), InStr(1, Sheets("元帳").Range("f3"), "（"), 6)

となります。

ちょっと長くなりそうなので、続きは来週書きますね。
  

おはようございます。
今日は、早い投稿です。

先日の給与明細コメントの件は返事待ちで
しばらく保留です。・・・信用ないのかなぁ。

でも、新たに相談が入りました。
最近、何かと「こんなの作れないかなぁ」という
相談が多くなりました。

昨日は珍らしく、きちんと文書というか図にした依頼を受けました。



経理の未収金残高の内訳をチェックするリストが
ほしいようです。
イメージはわかります。

でも、データを見るとちょっと考えてしまいます。
前回みたいな、文字列の関数も使うみたいな的な。

夕方に、もう一度どの様にしたいのか聞いてみたいと
思います。

どのような結果がほしいのか。
プログラムを作る上で１番大切な事のような気がします。
  

高校野球決勝戦
見ごたえのあるいい試合でしたね。
ワンサイドゲームかと思いきや、
「まさかひゃ～」の
日本文理、最終回の粘りでした。

野球はツーアウトからとか
終わってみるまでわからない
といいますが、

「あきらめるな」という事を
改めて教えられた試合でした。

＊＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＊

では、本題です。

今回の依頼は、

給与明細書のコメント欄をパソコンから
入力できるようにしてほしいとの要望。



今は社会保険料のお知らせ等があった時に
手書きで記入しているそうです。
（私も見たことありますが・・・）

とは言っても、
市販の給与ソフトを使っているので
私には手がつけられません。
なので、販売会社に電話で問い合わせました。

回答は
「対応してませんが、出来るように
するには別途１５,０００円のカスタマイズ
費用が発生します」との事。

これから毎月作業していく事を考えると
この値段が高いとは思いませんが、
最初からついていてもいいんじゃないかなぁ
と思うような機能です。

なので、カスタマイズをする前に
ちょっとわがままを言って
ＶＢＡで作らせてもらえないか
お願いしているところです。
　　　　　
　　　　　　が、
　　　　　　・
　　　　　　・

従業員の給与データを、
簡単に扱う事はできないので
上司の許可が必要との事。

そりゃ、そうだ。
給与データともなるとかなりの信用が必要だ。
他人の給料がわかるわけですからね。

明日の返事待ちですが、
許可とか、面倒くさそうなので、
カスタマイズしてもらおうかな？  

朝、出勤して
花に水遣りをしようとホースリールに巻かれたホース
を１０メートルぐらい延ばしていると途中でリールが
回らなくなり延びない。
暑い中、汗もかきながら戻ってみると、
ホースが綺麗にまかれてなく
絡まっている状態になっている。
手元で全部引っ張りだし延ばして使った。

何故そうなったか。
前回使った人が綺麗に巻いていないから
次使う人が苦労する。

私は自分で経験しているから、次の使う時の事を
考えて片付ける時はきちんと巻くようにしている。

ただ、巻けばいい、片付ければいいではなく
自分が次使う時、次の人が使う時の事を
考えて行動すると、気持ちもいいし
作業も効率よく片付く。

普段の仕事の中でも共通する事はあると思う。
他の人が
読みやすいようにとか、
見やすいようにとか
取りやすいように、置きやすいように
とか等

一つ先を考えた行動を心掛けたいものです。

＊==============================＊

それでは、本題です。

前回の続きです。

「名前」の中に含まれる空白を除去する命令が
ポイントになります。
と、言ってもそんなに難しいものではありません。
エクセル関数でいうSUBSTITUTE関数で
文字列中の指定した文字を新しい文字で置き換える命令を
使います。

ＶＢＡではどうするかというと、
　　　・
　　　・
　　　・

それが～これだぁ！

＊半角を除去する。
　Replace(氏名, " ", "")
　" "の部分が半角スペースです。

＊全角を除去する。
　Replace(氏名, "　", "")
　"　"の部分が全角スペースです。

そうです。

Replace命令を使います。

例えば、
"山田　太郎"という名前の"山田"と"太郎"の間に半角の
空白があった場合

上の命令にかけると
（＊ｎａｍｅは変数）

name = "山田　太郎"

name = Replace(name, " ", "")・・・" "は半角のスペース

とすると、
ｎａｍｅの内容は"山田太郎"になります。

今回のプログラムの中では、
この様に使われています。





Replace命令、又一つ勉強になりました。

一つの物を作ろうとすると次々と新しい
問題が出てきてきます。
それを解決するために考えを巡らせ、調べて
結果をだしていく。

エクセルマクロ（ＶＢＡ）を勉強してから、
自分が考えているものが形になって
出来上がっていく事に喜びを感じます。

私が言うのもなんですが、プログラムはおもしろい。


＜参考までに今回のプログラムです＞

Sub 購入実績作成処理()

　　Application.ScreenUpdating = False

'-----　変数の定義　----
　　Dim gyo1 As Integer
　　Dim gyo2 As Integer
　　Dim 購入者名 As String
　　Dim obj As Object
'----- 個人マスター　キー編集

　　Call 個人マスターキー設定処理

'-----　販売実績をアクティブにする ------

　　Windows("購入実績.xls").Activate
　　Worksheets("購入実績").Select

'---　購入実績の先頭データ

　　gyo1 = 2

'---　名前がスペースになると処理を終了する

　　Do Until Range("a" & gyo1) = ""

　　　購入者名 = Worksheets("購入実績").Range("a" & gyo1)

　　'半角スペースの除去
　　　購入者名 = Replace(購入者名, " ", "")

　　 '全角スペースの除去
　　　購入者名 = Replace(購入者名, "　", "")

　　　'個人マスター検索
　　　Set obj = Workbooks("個人マスター.xls"). _
　　　　　　　　　Worksheets("sheet1").Cells. _
　　　　　　　　　Find(購入者名)
　　　　
　　　If obj Is Nothing Then

　　　　　Worksheets("購入実績").Range("d" & gyo1) = "個人マスター不一致"
　　　Else

　　　gyo2 = obj.Row

　　　Worksheets("購入実績").Range("d" & gyo1) = _
　　　　　　　　　　　　　　　　　　　Workbooks("個人マスター.xls"). _
　　　　　　　　　　　　　　　　　　　Worksheets("sheet1"). _
　　　　　　　　　　　　　　　　　　　Range("c" & gyo2)

　　　Worksheets("購入実績").Range("e" & gyo1) = _
　　　　　　　　　　　　　　　　　　　Workbooks("個人マスター.xls"). _
　　　　　　　　　　　　　　　　　　　Worksheets("sheet1"). _
　　　　　　　　　　　　　　　　　　　Range("e" & gyo2)
　　　End If

　　gyo1 = gyo1 + 1

　　Loop
　　Application.ScreenUpdating = True
End Sub　

Sub 個人マスターキー設定処理()

'-----　変数の定義　----
Dim g As Integer
Dim gyo1 As Integer
Dim gyo2 As Integer
Dim 氏名 As String
Dim obj As Object

'-----　個人マスターのオープン ------
Workbooks.Open Filename:= _
"C:\Documents and Settings\My Documents\お客様台帳\個人マスター.xls"

Worksheets("sheet1").Select

'最終行を求める
g = Range("A65536").End(xlUp).Row

'---　変数へ初期値設定
gyo1 = 2 '個人マスターの先頭データ

'---　セルＨ１に検索キーとタイトルを入れる
Range("h1") = "検索キー"

'---　氏名がスペースになると処理を終了する

Do Until Range("a" & gyo1) = ""

氏名 = Range("b" & gyo1)

'半角スペースの除去
氏名 = Replace(氏名, " ", "")

'全角スペースの除去
氏名 = Replace(氏名, "　", "")

Range("h" & gyo1) = 氏名

gyo1 = gyo1 + 1
Loop

Application.ScreenUpdating = True

End Sub


  

今日、午前中は定期的な病院受診の日でした。

いつもの様に、９時半予約の１１時診察、
薬もらって、会社にたどり着いたのが１時３０分。
３０分の遅刻。どうしてくれる。

＊====================================＊

では、本題です。

昨日の続きですが、

購入実績から個人マスターを読み込む際の検索キーと
して「名前」を使いたいのですが、苗字と名前の間の
空白が不特定な為不一致となり検索できません。

どうすれば一致するかを考えた場合、
購入実績と個人マスターの名前を同じ条件に
してあげる事だと思います。

同じ条件にする方法として、キーにする「名前」の
空白をなくせば購入実績と個人マスター両方の
「名前」が一致するはずです。

という事で、空白を除去する方法で考えてみました。



空白をなくすと言っても名前の内容を直接書き換えてはいけないと
思うので、あいてるセルにキー専用として編集します。



セルに関数を入れるのではなくプログラムで自動的に編集します。

それでは予想どおり動くか確認してみます。

＜ボタン押します＞


＜結果＞


予想通り検索キーが一致してうまく編集されました。

この方法がいいのか分かりませんが、
工夫しだいでなんとかなるものですね。

これを担当に提案してみよう～と。

スペースを除去する方法は明日説明したいと思います。
  

今日は、ＶＢＡでいろいろと試したい事があり
プログラムを作っていました。



作っていくうちに画面がちらついたりするので、
「画面更新のＯＮ、ＯＦＦ」命令のスペルを調べたく、
ネットで「ＶＢＡ　画面　ちらつき」で検索してみました。




あら、ま～びっくり。
どこかで見た名前がある。
「リストラ回避の悪あがき日記」
自分のブログでした。




いやぁ、こんな形で調べる時にも役立つなんて
ブログも結構いいかも。  

朝早く経理課から呼び出しがありました。
つり銭プログラムが印刷時にエラーになるとの事。

自分の仕事そっちのけで走りましたよぉ。
月曜日の朝っぱらからと思いながら。

確かにマクロの印刷の所でエラーになっています。





とりあえず終了して、
画面から直接プリンターを指定して印刷すると
正常に印刷できます。



やはりマクロの問題です。

急がばまわれで、印刷関係のトラブルを
ネットで調べてみる事に。

すると見事な解決策がヒットしました。
「ＹＡＨＯＯ知恵袋」

全くその通りの内容。

そのアドバイスにそって
ポートの確認をする事にしました。

現在は、「０１」です。



印刷のマクロを記録してみました。
するとポートの部分が「Ne02:」になっています。



更に「プリンターのプロパティ」で
ポートの確認をしました。
同じくプリンターのポートは「０２」
になっていました。




いつ、どこで、だれが変えたかわかりませんが？・・・

そういえば、先週プリンターの故障があって修理した様ですが。

まっいいか。

「02」に替えて無事解決。



用が済んだら急いで自分の仕事にもどりましょう。

今日も残業かな  

経理課で
つり銭を印字した封筒を出力するマクロを使って
いますがプリンターが故障して１日使えなかった
様です。
機械が壊れてはソフトも何の力も発揮できませんね。

「こんな時にコンピュータは困るのよね～」って
愚痴の一つでもこぼされると思っていましたが、

「久ぶりに手書きでやりましたが、
いつもは３０分程の作業が
午前中かかってしまいました。
以前はこれが当然の様に仕事を
してましたが今では考えられません。
ありがとうございます。」
と感謝の言葉を頂きました。
嬉しいものです。

＊==============================＊

では、本題です。

今日は、ＶＢＡで使うワークシート関数について
書いてみたいと思います。

エクセルのワークシートで使うＳＵＭ関数や
ＳＵＢＴＯＴＡＬ関数等がＶＢＡでも使えるので
プログラムを作る上でとても助かります。


例えば、次の処理で考えると

図１

商品毎の売上合計を計算して求めています。

単純にプログラムの流れを考えると、

日付毎に並んでいるデータを
商品毎に並び替えて、
１番目から順番に読んで（参照）いき
商品名が同じなら合計を計算して
商品名が変わった時点で、個数の合計と
売上の合計を出力して、次の商品の
処理を続けていきます。

しかし、ＶＢＡではオートフィルターと
ＳＵＢＴＯＴＡＬ関数を組み合わせると
繰り返しや分岐を使わなくても簡単に
処理ができます。

プログラムです。

Sub Macro1()
'

Sheets("Sheet2").Select
Range("c3:d6").ClearContents

Sheets("Sheet1").Select
Range("B2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="りんご"
Sheets("Sheet2").Range("C3") = WorksheetFunction.Subtotal(9, Range("D3:D22"))
Sheets("Sheet2").Range("d3") = WorksheetFunction.Subtotal(9, Range("e3:e22"))

Selection.AutoFilter Field:=2, Criteria1:="みかん"
Sheets("Sheet2").Range("C4") = WorksheetFunction.Subtotal(9, Range("D3:D22"))
Sheets("Sheet2").Range("d4") = WorksheetFunction.Subtotal(9, Range("e3:e22"))

Selection.AutoFilter Field:=2, Criteria1:="すいか"
Sheets("Sheet2").Range("C5") = WorksheetFunction.Subtotal(9, Range("D3:D22"))
Sheets("Sheet2").Range("d5") = WorksheetFunction.Subtotal(9, Range("e3:e22"))

　 Selection.AutoFilter Field:=2, Criteria1:="ぶどう"
Sheets("Sheet2").Range("C6") = WorksheetFunction.Subtotal(9, Range("D3:D22"))
Sheets("Sheet2").Range("d6") = WorksheetFunction.Subtotal(9, Range("e3:e22"))

End Sub

横に長すぎて、見づらいと思いますが、

プログラムの中で、
「WorksheetFunction.Subtotal」という命令が
ありますが、
ワークシート関数の「Subtotaｌ」の前の
「WorksheetFunction」が
ワークシート関数をＶＢＡで使う時に
記述する命令になります。

それでは昨日の記事に書いた、「Ｆ８」の
機能を使って一づつ流れを追ってみます。

売上一覧（シート１）があります。


「Ｆ８」



オートフィルターがかかります。


「Ｆ８」


りんごが抽出されます。


「Ｆ８」


シート２にりんごの合計が出力されました。


同じような流れで、「みかん」、「すいか」、「ぶどう」も集計していきます。

ワークシート関数が使えるとプログラミングも楽にできすね。

ＶＢＡって、なんて便利なんだろうとつくづく思います。  

新型インフルエンザの話を身近でも
聞くようになりました。

感染しないようにと日頃から
手洗い、うがい、早寝、早起き、
三度の食事もきちんととり、
病院受診の日はマスクをしています。

インフルエンザウイルスは湿度に弱いというので
加湿器も準備すればある程度の予防になるだろうと
ネットで、
「コンパクト 気化式 加湿器」を
注文し入金も済ませているんです。
ところが１ヶ月たっても未だ物が届きません。
電話もつながりません。

私は騙されたのでしょうか。
（２０００円ぐらいですけどね・・・）

＊==========================================＊

では、本題です。

プログラムの動きをテストしたい時
キーボードの「Ｆ８」を押すと１行ずつ命令
を実行しながら確認できます。

それに「ローカルウィンドウ」を開き
変数の値を表示しながら実行していくと
さらに効率よくテストが出来ます。

＜ローカルウィンドウの開き方＞


＜ローカルウィンドウ＞



自分の思い通りに動いているか
実際、目で追いながらテストが出来ます。

実行したいプロシージャの中にカーソル
をもっていき、「Ｆ８」を押します。



実行される行が黄色になります。



「Ｆ８」を繰り返し押していくと、１行づつ命令が
実行されていきます。



実行しながら、変数の上にカーソルをおくと
値が表示されます。


実行をやり直したい時には、中断やリセット
で終了します。



エクセルマクロにはこんな便利な機能があるので
楽しみながらテストができます。

例えば、
「判定」という変数が「１」だったらセルＡ１に「〇」を入れる
という条件の命令があると

頭の中では、
今は「判定」に「１」が入っているからＡ１に〇が入るな
って思いながらＦ８を押していくわけです。

ところが、ＴＨＥＮではなくＥＬＳＥに行ってしまった。

「おかしいなぁ～」って事でその場で実行を中断し、
じゃ～「Ｘ」には何が入ってるんだろうって、
今度は、最初から注意深く流れを追いながら実行していく。

そんな事の繰り返しで、自分が想像していたプログラムが
出来上がっていくわけです。

私も作ったプログラムこそ少ないですが、
思い通りに動かないものですから、
テストだけはかなり数をこなしています。

初心者でも、とてもわかり易いと思います。
  

昨日の記事で、例えが悪かったのか
「ＳＵＭ関数ですぐ計算できるよ」って
言われてしまいました。
たしかにその通りです。

でも私が言いたかったのは、計算そのものではなく
変数の指定についてちょっと書いてみたかったので
経験が少ないものですからこんな例えになってしまい
ました。あしからず。

そこで今日は、
「エクセル関数を使うと便利な時」
「マクロを使うと便利な時」について
書いてみたいと思います。

データの集計によっては、
エクセルの関数で簡単に出来るものを
わざわざマクロでつくる必要のないものがあります。

昨日のご指摘にあったように、
「ＳＵＭ関数」で済むものをマクロで作っては
かえって効率が悪くなります。

例えば、次の表ですと



収入合計と支出合計と残高を表の中で関数を使い
自動計算させています。
この表は、合計を表示する欄が決まっているので
エクセル関数で作る方が便利な使い方だと思います。

次の場合はどうでしょう。



合計を最後のデータの下に表示したい。

データが増えたり減ったりしてその都度行数が
変わっていきます。

予め合計の関数を入れておいて、行挿入、行削除を
繰り返してもいいと思いますが何らかの操作が必要に
なります。

ボタン一つで合計が自動的に表示できる
そんな機能がほしくなりますよね。



そこがマクロの得意とするところです。

それでは作ってみましょう。

Sub Macro2()

Dim 最終行
Dim 合計行

最終行 = Range("h65536").End(xlUp).Row

If Range("h" & 最終行) = "合計" Then
Range("H" & 最終行 & ":k" & 最終行).Select
Selection.ClearContents
Selection.Interior.ColorIndex = 34
最終行 = Range("h65536").End(xlUp).Row
合計行 = 最終行 + 1
Else
合計行 = 最終行 + 1
End If

Range("H" & 合計行 & ":k" & 合計行).Select
Selection.Interior.ColorIndex = 38
Range("h" & 合計行) = "合計"
Range("i" & 合計行) = WorksheetFunction.Sum(Range("i4:i" & 最終行))
Range("j" & 合計行) = WorksheetFunction.Sum(Range("j4:j" & 最終行))
Range("k" & 合計行) = WorksheetFunction.Sum(Range("k3") + Range("i" & 合計行) - Range("j" & 合計行))

End Sub

合計を表示するプログラムです。

合計を表示した後、次に入力したい場合
合計をクリアしてあげます。
そのプログラムが

Sub Macro1()
Dim 最終行
Dim 合計行
最終行 = Range("h65536").End(xlUp).Row
合計行 = Range("h65536").End(xlUp).Row + 1
If Range("h" & 最終行) = "合計" Then
Range("H" & 最終行 & ":k" & 最終行).Select
Selection.ClearContents
Selection.Interior.ColorIndex = 34
合計行 = 最終行
End If

End Sub

です。合計クリアのボタンを押すと
空白になり入力が出来ます。

それでは動きをテストしてみましょう。
合計ボタンを押します。


合計が表示されました。

クリアボタンを押してみます。



合計行がクリアされました。

７番目のデータを消して合計が
正しく表示されるか見てみます。



合計ボタンを押します。



正しく表示されました。

次に、７番目から３件データを追加してみます。



合計ボタンを押します。



正しく表示されました。

これが、「ボタン一つで！」という
便利な所ですよね。

エクセル関数とマクロを使い分けて・・・と、言うか
良い所を組み合わせて作業の効率化をどんどん
図っていきましょう。




  

エクセルマクロ（ＶＢＡ）の相談を受ける事が多くなってきました。

マクロの使い方というよりも、
「こんなもの作ってほしい」とか、
「この業務自動化できませんかね」という
作ってほしい要望です。

もちろんできる範囲でのお手伝いはさせて頂いて
ますが、最近相談があったのは、「給与計算」を
できませんか？との事。ちょっとびっくりです。

私にとっては、ちょっと荷が重すぎるので保留にして
ますが、プログラムの技術というよりも、
「給与業務」そのものを勉強しなければいけません。
それがわかった時点で挑戦してみようと思いますが
いつの事やら？

＊==================================＊

さて、本題です。
今日は、変数の強制宣言について書いてみたいと思います。

変数は宣言しなくても使えますが
プログラムを作っていくうちに変数の名前間違いや
入力ミスで思うように結果が得られない場合があります。

例えば、次の処理は
セルＣ２からＣ１１に入っている点数の合計を
セルＣ１２に入れるとします。



プログラムは

Sub テスト()
　　　goukei = 0
　For gyo = 2 To 11
　　　　　　goukei = goukei + Range("c" & gyo)
　　　 Next gyo
　　　　Range("c12") = gokei
End Sub

と記述したとします。

プログラムは正常終了しますが、
結果はというと

「goukei」という変数を
　セルＣ１２に編集するとき
入力ミスで「gokei」と記述してしまいました。
合計欄は空白のままでいつまでたっても合計が表示されません。



「おかしいなぁ～」「合ってるはずなのになぁ～」と思い込みながら
間違い探しをするのでなかなか気付かない事が多いものです。

プログラムが長くなればなるほどこういう勘違いはでてきて
見つけにくく時間がかかってしまいます。

そんな時の為に、変数の強制宣言を使う事をお勧めします。

各モジュールの最初に「Option Explicit」と記述します。

Option Explicit
Sub テスト()
Dim gyo
Dim goukei
goukei = 0
For gyo = 2 To 11
　 goukei = goukei + Range("c" & gyo)
　　Next gyo
Range("c12") = gokei
End Sub
それでは、実行してみましょう。



「変数が定義されていません」とエラーがでました。
変数の間違いにすぐ気付きます。

プログラムを作っている段階で気付くので、
バク探しをする無駄な時間がかなり省けます。

「Option Explicit」お忘れなく。
  

息子をそろばん教室へ迎えに行った帰り、

息子が、
「そろばん教室、クーラーが効きすぎて寒いよ」という話をしていたので、
クーラーだけに頭が「く～らくら」するだろうってダジャレをいった。
すかさず息子。なぞかけです。
お父さんのギャクとかけて、そろばん教室と解く。そのこころは
「どちらも寒い」だそうだ。
まいりやした。

＊＝＝＝＝＝＝＝＝＝＝＝＝＝＝＊

では、本題です。

プログラムにはエラーは付き物かもしれませんが
それは作っている人が、わかればいい事で、
ソフトを使っている人からみればエラー画面が表示されると
「なんじゃそりゃ」って事になりますよね。



昨日の例も、エラー処理がされていなかった為
イベントが停止して、プログラムが正常に動かな
かったわけです。



今日はプログラム上でエラーが発生した時に
エラー回避をしてあげる処理を考えてみたいと思います。

エクセルマクロ（ＶＢＡ）では、
プログラムでエラーが発生した事が認識できる
命令が用意されています。

On Error GoTo 　・・・という命令です。

エラーが発生したときエラー専用の処理を作ってあげて

例えば、


なんてメッセージを表示して

プログラムが正常に動く状態で終了してあげます。

では、昨日の例で実際にエラーの処理を組み込んでみます。

'データバックアップ
Private Sub CommandButton4_Click()

On Error GoTo ERR1 'このプロシジャー内でエラーが発生した場合の処理

Application.ScreenUpdating = False
Application.EnableEvents = False


Sheets("入力画面").Select
Sheets("入力画面").Copy

ActiveWorkbook.SaveAs Filename:="d:\ＶＢＡ開発フォルダ\伝票データＢＫ（Ｈ２１年）.xls"

ActiveWorkbook.Close
MsgBox ("バックアップが終了しました。")

Application.EnableEvents = True
Application.ScreenUpdating = True
Sheets("メニュー画面").Select
Exit Sub

'-------------------------------------------------------------------------------
' エラー時の処理
ERR1:
Application.DisplayAlerts = False
ActiveWorkbook.Close
Sheets("メニュー画面").Select

Application.ScreenUpdating = True
Application.EnableEvents = True

MsgBox "バックアップエラー:" & Error(Err)

End Sub



＜実行すると＞



丁寧にエラーのメッセージが表示されました。

最後のメッセージボックスで表示した
MsgBox "バックアップエラー:" & Error(Err)
の Error(Err)の部分がエラーの詳細を表示しています。


詳しくは
http://support.microsoft.com/kb/146864/ja
等参考にしてみてください。
  

先日息子の誕生日の事を書きましたが、
誕生日ケーキを買いに行ったときの話。

ＣＭの「ダイワハウチュ」みたいな出来事に
遭遇しました。

ケーキを２個買いメッセージを書いてもらいました。

店員さんが、
「メッセージの確認をお願いします」
とメッセージを読み上げた。

「〇〇君お誕生日おめれとうございます。」
えっ！今、「おめれとう」って言った？って心の中で
耳を疑っていましたが、

２個目も同じように
「確認お願いします」
と読み上げてくれた。

「○○君お誕生日おめれとうございます。」

やはり「おめれとう」だった。

なんで、「おめれとう」なんだ？って
「ダイワハウチュ」のＣＭが
頭の中で繰り返し浮かんできました。

＊－－－－－－－－－－－－－－＊

では、本題です。

自分で作ったプログラムが実際に現場で使われているのを
見ると嬉しいものです。

私の場合、勉強のつもりでやっているのでもちろん無償です。
だからといって、作りっぱなしではいけないと思っています。
フォローはきちんとしてあげたいですね。
もちろん無償ですが
空き時間での対応という事にさせてもらってますし
ある程度のミスも許してもらってます。
それなので、独学で結構楽しく勉強させてもらってます。

＊--------------------------------------*

昨日、突然電話がありました。

「毎日データを入力していてとても助かっています。・・・が
突然、残高が表示されなくなって困っています。
なんとかできないでしょうか。」



って、優しくお願いされると
単純な私は、「なんとかしてあげたい」って思ってしまいます。
で早速対応させて頂きました。それも、夜の８時から。

私が作った会計のプログラムですが

今まで手書きでやっていたものを、
収入と支出のデータさえ入力すれば、
出納帳、総勘定元帳、予算執行状況等が
自動的に計算されて出てきます。

素人が作っているので、画面上見栄えは
きれいではありませんが、機能的には
これで充分と喜んで使ってもらってます。

エラーは、
動きを見てすぐ見当がつきました。
残高計算のイベントプロシージャが動いていない様です。

メンテナンス用に準備していたモジュールの中の
イベント発生用のプロシージャを実行したら
うまく計算されました。

Sub イベント発生()

Application.EnableEvents = True

End Sub



しかし、残高を計算させるという対応はそれでいいのですが、
原因を見つけなければ又、同じ現象が発生します。

どこかにイベントを停止させる原因が潜んでいるはずです。

一つずつボタンを押しながら処理を確認していきました。
すると、みつかりました。
データバックアップです。
バックアップ用のファイルが存在しないとのメッセージ。



何度もテストしたはずなのに？何故
ここでエラーが発生してイベントを停止させた状態のまま終了したようです。




これは明らかに私のプログラムの修正ミスでした。
テスト中のフォルダーとファイルを使っていて本番用に修正して
いませんでした。

今回の対応は、新にバックアップ用のフォルダーとファイルを作ったところ、
無事正常終了しました。






でも、エラーが出た場合の対処もプログラムに組み込んでおく
必要がありそうです。

その辺は次回書きたいと思います。  

雨、風が強くなってきましたね。
沖縄本島は暴風域からそれるようですが
事務所から外を見ていると隣の家の
物干し竿が飛んだりアンテナがくるくる
回ったりしています。ついつい家の事が心配に
なります。
今日は５時起きで台風対策はしたものの
６時には会社の台風対策に出かけた為
中途半端に終わってしまいました。

心配ですが被害が無いことを祈ります。

それでは、今日の本題です。

私が、パブリック変数の意味も理解できないまま
作ったプログラムでは、モジュール間の変数の受け渡しは
新たにシートを作りセルを利用して参照していました。

例えば、
行番号５の玉城サブロウさんをダブルクリックすると
変数保存用シートのセルＣ１へ編集します。



次に、「入力画面」で入金額を入力した後、「請求一覧」へ
戻る時に、変数保存用シートのセルＣ１から戻る行番号を取得します。





できました。
動きは完璧です。

＜プログラム＞

===== 請求一覧　ワークシートモジュール　====

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

行 = ActiveCell.Row

Sheets("sheet1").Range("c1") = 行 '変数保存用の
　　　　　　　　　　　　　　　　　　　　　　　　　ワークシートへ行番号を編集する

Sheets("入力画面").Range("c5:e5").Value = ""
Sheets("入力画面").Range("b5").Value = Sheets("請求一覧").Range("b" & 行)
Sheets("入力画面").Range("c5").Value = Sheets("請求一覧").Range("c" & 行)

Sheets("入力画面").Select

End Sub

===== 入力画面　ワークシートモジュール　====

Private Sub CommandButton2_Click()

行 = Sheets("sheet1").Range("c1") '変数保存用の
　　　　　　　　　　　　　　　　　　　　　　　　　ワークシートから行番号編集

Sheets("請求一覧").Range("d" & 行).Value = Sheets("入力画面").Range("d5").Value
Sheets("請求一覧").Select

End Sub
===========================================

ある意味正常に動いて、変数の値をワークシートから
見ながら動きが確認できるので素人的にはこれも有りかなと
思っています。

まずはいろいろ考え工夫して自分なりの発想でやってみる事も
継続して勉強していくには大切かもしれません。

でも基本的な事はマスターしなければいけませんね。  

台風が近づいていますね。

職場の鉢植えの木を片付けようか迷ってましたが、
備えあれば憂いなしで夕方一気に片付けてしまい
ました。
家の対策はどうしよう。心配です。

今日は、変数の「Ｐｕｂｌｉｃ宣言」について書いてみたいと思います。

シートが２枚あり、
１枚目に「請求一覧」
２枚目に「入力画面」があります。

「請求一覧」から対象者のセルをダブルクリックすると
「入力画面」へ移り、
入金額を入力後「一覧表へ」のボタンを押すと
「請求一覧」の対象者セルへ入金額を編集し
「請求一覧」の画面に戻る動きのプログラムをつくります。

「請求一覧」と「入力画面」で、どの対象者の処理
なのかを受け渡す必要があります。
つまり何行目のデータが選択されたかを取得して
モジュール間で参照できる事が必要になります。

そこで、思いつくのがモジュール間で参照可能な
Ｐｕｂｌｉｃ変数です。

でも、その「Ｐｕｂｌｉｃ変数」
指定する場所を間違えると値が消えてしまいます。
注意して下さい。

これは、私の失敗談です。

次のプログラムですが、ワークシートモジュールで「行」という
変数を宣言しました。

-------- ワークシートモジュール　請求一覧　 ----　

Public 行　　'パブリック変数

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

行 = ActiveCell.Row　　'選択されたセルの行番号を取得

Sheets("入力画面").Range("c5:e5").Value = ""
Sheets("入力画面").Range("b5").Value = Sheets("請求一覧").Range("b" & 行)
Sheets("入力画面").Range("c5").Value = Sheets("請求一覧").Range("c" & 行)

Sheets("入力画面").Select

End Sub


--------　 ワークシートモジュール　入力画面 　----　

Private Sub CommandButton2_Click()　’請求一覧のボタンを押す動く

Sheets("請求一覧").Range("d" & 行).Value = Sheets("入力画面").Range("d5").Value
Sheets("請求一覧").Select

End Sub


実行してみます。

金城ハジメさんのデータをダブルクリックします。

「入力画面」が表示されます。

入金額を入力し「一覧表へ」のボタンを押します。

するとエラーがでました。

デバッグをみてみます。


Ｐｕｂｌｉｃ変数「行」に何も入っていないと認識しているようです。

ｐｕｂｌｉｃで宣言し、「請求一覧」のモジュールで
選択された行番号を編集して「入力画面」へ移動している
はずなのに「入力画面」では空になっている。

何故だろう？

最近、参考書を読んでいてわかったのですが、

ｐｕｂｌｉｃ変数は標準モジュールで宣言する

と書かれていました。

早速、試してみました。

-------- 標準モジュール１　 ----　

Public 行　　'パブリック変数


-------- ワークシートモジュール　請求一覧　 ----　

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

行 = ActiveCell.Row　　'選択されたセルの行番号を取得

Sheets("入力画面").Range("c5:e5").Value = ""
Sheets("入力画面").Range("b5").Value = Sheets("請求一覧").Range("b" & 行)
Sheets("入力画面").Range("c5").Value = Sheets("請求一覧").Range("c" & 行)

Sheets("入力画面").Select

End Sub


--------　 ワークシートモジュール　入力画面 　----　

Private Sub CommandButton2_Click()　’請求一覧のボタンを押す動く

Sheets("請求一覧").Range("d" & 行).Value = Sheets("入力画面").Range("d5").Value
Sheets("請求一覧").Select

End Sub


正常に動きました。


さて問題です。
私は、今まで
同じような処理のプログラムを何個か作ってきましたが
正常に動いています。
どのように作っていたのでしょうか。
（おはずかしい）

続きは、明日書きますね。
  

おはようございます。

うちの会社ではメモ帳をよく使いますが、
最近メモ帳が不足しています。
以前は取引業者さんからたくさんもらえたのですが
最近は少なくなりました。

そんな時はよく裏紙を利用してメモ帳を作っていますが
重ねた紙をホッチギスやダブルクリップではさんで
使っている所も多いと思います。

そこで、ちょっとした事ですけど

メモ帳の上になる部分に液体のり、もしくはボンドを少量塗り
その部分にティッシュをつけ、のりが乾くまで（３０分程度）
ダブルクリップで固定しておきます。

乾いた後にダブルクリップをはずすと
市販のメモ帳みたに綺麗にめくれて使い勝手でいいです。

試してみてください。

それでは本題です。
プログラムを作っていると、私の場合必ずエラーに悩まされます。
解決策を調べていくうちに、そのほとんどが基本的な事を理解
していないためだと気付かされます。同じミスを繰り返さない為にも
今日は復習しておきたいと思います。

「変数」の取り扱いでの失敗例ですが、
売上データに「得意先コード」を８桁の数字として記録しています。


プログラムで、「得意先コード」という変数を指定して、売上データの「得意先コード」を
変数の「得意先コード」に代入します。



すると「エラー」が発生しました。



何故でしょう。

わかれば単純ですが、「変数」には「型」があって
「数値」を入れるなら「数値」専用の型、
「文字」を入れるなら「文字」専用の型があるそうです。
数値の型で指定した変数に文字を入れるとエラーになります。

では、今回のエラーの原因はというと、
得意先コードの変数が

Dim 得意先コード As 　integer

と指定されています。

integerとは、-32,768～32,767の整数が使える変数の指定です。

今回エラーが出た、代入元の売上データの「得意先コード」は
８桁の数字で記録されています。
integerの最大値32,767より大きな数字、
つまり得意先コード１００１１１０１を
代入しようとしたために
オーバーフローというエラーになってしまいました。

そこで、もっと大きな数値を取り扱える
longという型に変更してみました。
longとは、-2,147,483,648～2,147,483,647の整数が扱える型です。

Dim 得意先コード As Long
と変えてみました。

すると




うまくいきました。

変数の型はたくさんありますが、よく使うと思うものを載せておきます。

　型名　　　　　　　　　型指定　　　　　　　　データの範囲
整数型　　　　　　　　　integer　　　　　-32,768～32,767の整数
長整数型　　　　　　　 long　　　　　　 -2,147,483,648～2,147,483,647の整数
日付型 　　　　　　　　date　　　　　　　日付　西暦100年１月１日～西暦９９９９年１２月３１日
　　　　　　　　　　　　　　　　　　　　　　　時刻　０：００：００～２３：５９：５９
文字列型　　　　　　　string　　　 　　 任意の文字の長さ
オブジェクト型　　　　object　　　　　　 オブジェクト
バリアント型　　　　　variant　　　　　　すべてのデータ


最後のバリアント型はすべてのデータが使えて、
省略すると自動的にバリアント型になります。

省略してバリアント型でうまく動くなら省略した方が
いいかもしれませんが基本的な事なので、
わかっておくとエラー等の対処がスムーズにできますね。