無料VBA講座

掲載ファイルは、プログラムでEドライブから開きますから
どのファイルも、Eドライブに左側の名前で保存してください。
プログラムを変更すれば、他のドライブに保存可能です。

掲載参考 EXCEL ファイルダウンロード
1. グラフ自動作成.xlsm
2. 当月売上.xls 　　
3. 当月日付基準.xls

保

フォーム名は[プロジェクトエクスプローラー]の[フォーム]に表示される。ダブルクリックすると
開いて編集が可能になる。

Sub 解除( )
　　ActiveSheet.Unprotect　　　' シート保護の解除
　　Unload Inp　　　　　　　　 ' フォームを閉じる。
End Sub

[PS]ボタンを押すと、[パスワード表示]プログラムが実行して、PSフォームを表示する。

Sub パスワード表示( )
　　PS.Show vbModeless　　' PSフォームの配置を、マウスで移動できる状態で表示する。
End Sub

●パスワードを忘れないようにするために設置されていて、×で閉じる。

パスワードを入力して、[入力完了]ボタンを押すと、入力値が1234でないと[パスワード不良]と
テキストボックスに表示される。正しいと、[解除]プログラムが実行される。

[保護]ボタンを押すと、各シートの保護プログラムを実行する。
[解除]ボタンを押すと、[保護の解除]プログラムを実行し、Inpフォーム
　(パスワードの入力)が表示される。
[PS]ボタンを押すとパスワードが表示される。

　Sub 保護の解除( )
　　　Inp.Show vbModeless　　' パスワードを入力するフォームを表示
　End Sub

Sub 保護6()
　Range("A1:Q54").Select
　ActiveSheet.Protect DrawingObjects:=Treu, Contents:=True, Scenarios:=True
　ActiveSheet.EnableSelection=xlUnlockedCells
　Range("A1").Select
End Sub
●このように、他の人に参照されるシートは、保護を掛けている。だからそれぞれの
　プログラムは、ActiveSheet.Unprotect で保護を解除した後で書き込みをしている。
　書き込みが終わったら、このプログラムを実行している。
　尚、個別にシートを編集する場合を考えて、保護の解除ができるようになっている。

●データ領域の更新によって、グラフは自動更新される。

intTmon=当月の月数値
If intTmon>5 Then intCel=intTmon－1　　　' 月数値が5より大きい場合は、月数値から1を減算
　　　　　　　　　　　　　　　　　　　　　' した数値の位置に列がある。だから7月は6列目にある。

●売上目標と実績グラフは、"売上目標と実績グラフ更新"プログラムによって[売上高表]から
　データを取得してデータ表に記入される。

●データ領域の更新によって、グラフは自動更新される。

　 intTmon=当月の月数値
　If intTmon>5 Then intCel=intTmon－2　　' 月数値が5より大きい場合は、月数値から2を減算
　　　　　　　　　　　　　　　　　　　　　' した数値の位置に列がある。だから7月は5列目にある。

●売上実績グラフは "月別売上グラフ更新" プログラムによって[売上高表]からデータを
　取得してデータ表に記入される。

N=0
For i=6 To 16 　　　　　　　　　　　　　　' 201の合計計算
　N=N+datArr(i,3)　　　　　　　　　　　 ' 合計値をNに加算している。
Next
datArr(17,3)=N 　　　　　　　　　　　　　' 合計値を変数に代入する。
N=0
For i=18 To 20
　N=N+datArr(i,3)
Next
datArr(22,3)=N
datArr(24,3)=datArr(17,3)+datArr(22,3)

Dim datArr(25,10) As Long 　　　' これは、2次元配列の定義である。売上表は24行の
　　　　　　　　　　　　　　　　' 9列で出来ているので、すべてのセルのデータを
　　　　　　　　　　　　　　　　' Cellsで記入して行くと、右下はCells(24,9)になる。
Sheets("売上高表").Select
ActiveSheet.Unprotect　　　　　　　　　　　　　　　　　' シート保護を解除する。
Set rangT=Worksheets("売上高分類").Range("F5:F7")　　' 範囲を変数に代入する。
　intSum=WorksheetFunction.Sum(rangT)　　　　　　　 ' 範囲内の合計計算
　datArr(16,8)=intSum　　　　　　　　　　　　　　　　' 合計値を配列変数に代入

Set rangT=Worksheets("売上分類").Range("F8:F10")　　 ' 119 CV
　intSum=WorksheetFunction.Sum(rangT)
datArr(18,8)=intSum
datArr(15,8)=Worksheets("売上分類").Range("F11") 　　 ' 119 PT

●ワークシート関数を使用するときは、WorksheetFunction.Sum(rangT)　の様に書く。
　　

●売上高表は"売上高書込み"プロブラムで、売上分類シートからデータを取得して記入される。

Sheets("売上分類").Select
intTotal=0
intCon=5
Range("E" & intCon).Select　　　　　　　　　　　　　　　　　　　　' E5選択
For i=intCon To 11　　　　　　　　　　　　　　　　　　　　　　　　' 5行から11行まで
　Range("B3").Value=Range("E3")　　　　　　　　　　　　　　　　' 199をB3に記入
　Range("A3").Value=Range("E" & intCon) 　　　　　　　　　　　　' A3にE5を転記
　Set rangH=Worksheets("当月売上高").Range("B4:K500")　　　　　' 当月売上高表を選択
　intDs=WorksheetFunction.DSum(rangH,"金額",Range("A2:B3"))
　　　　　　　' 当月売上高表の金額列から、売上分類のA2からB3を使用して抽出する。
　　　　　　　' DSUMの抽出形式はこの形である。
　Range("B4").Value=intDs　　　　　　　　　　　　' DSum結果をB4に記入する。
　Range("F" & intCon).Value=intDs　　　　　　　　 ' それをF列に転記する。
　intTotal=intTotal+intDs　　　　　　　　　　　　　 ' コード199の合計をしている。
　intCon=intCon+1　　　　　　　　　　　　　　　　' 行番号を1加算している。
Next　　　　　　　　　　　　　　　　　　　　　　　 ' Forに戻る。
Range("B15").Value=intTotal　　　　　　　　　　　 ' For Nextが終わったら合計値を記入する。

売上高分類プログラムは、E列、H列、K列の担当者コードをB3に転記し、商品コードをA3に転記し、
プログラムで[DSum]計算をしてその金額をB4に記入する。それを取得して、F列、I列、L列に転記する。
これを、すべての項目に実行し、B23で売上高総額と、この分類の総合計を比較している。

●売上高分類シート

●日付取得シート、（Eドライブの当月日付基準.xlsから取得したデータである。）

Dim Cnn As ADODB.Connection　　　　　　　　　　　　' ここからは、ファイルを開かないでデータを取得
Dim Rec As ADODB.Recordset　　　　　　　　　　　　' する文法である。

Worksheets("当月売上高") .Cells .ClearContents 　　　　'取得したデータの書き込みシートを空白にする。

Set Cnn=New ADODB.Connection
Cnn.Provider="Microsoft.ACE.OLEDB.12.0"

　　'●Excel2007以下のブックはExcel8.0で設定する。
Cnn.Properties(Extended Properties")="Excel 12.0" 　 ' Excer207以上の設定値

Cnn.Open"E:\当月売上.xls"　
Set Rec=New ADODB.Recordset　
Set Rec.ActiveConnection=Cnn
Rec.Open "[商品別$]",,adOpenKeyset,adLockPessimistic,adCmdTable
　　　　　　　　　　　　　　　　　　　　　　　　' 相手のファイルの"商品別"と言うシートから取得する。
Worksheets("当月売上高").Range("A1").CopyFromRecordset Rec
　　　　　　　　　　　　　　　　　　　　' こちらのファイルの"当月売上高"と言うシートのA1に転記する。
Rec.Close　　　　　　　　　　' ここから下は、使用した変数を閉じている。
Cnn.Close　　　　　　　　　　' 閉じないと、Excelを閉じるまで有効になる。
Set Rec=Nothing
Set Cnn=Nothing

'Excel 2007以上のバージョンのファイル取得には、VBAの[ツール]-[参照設定]の中の
'Microsoft ActiveX Data Objects 2.1 Library 以上(2.8 は不可)にチェックを付ける。
これは、相手のファイルを開かないで、データを操作したり、データを取得する場合に
そのモジュール先頭に Option Explicit を書き込む。

●当月売上高シート（Eドライブの当月売上.xls から取得したデータである。）

　このファイルは、開けたときにマクロを実行して集計とグラフの更新を完了する。
●動作の概要
　1. Eドライブにある"当月売上.xls"と言うファイルからデータを取得して、このファイルの"当月売上高"
　　シートに転記する。(このHPから取得したファイルを、Eドライブに保存するときには、"当月売上.xls"と
　　名前を変えて保存する。又、xlsで保存できない場合は、xlsxで保存し、マクロコードを変更する事)
　2. Eドライブにある"当月日付基準.xls"から、今日が何月かが分かる日付を取得して、このファイルの
　　 "日付取得"シートに転記する。
　　これは、月末締め切りの場合、月が替わってからデータの集計を行うから、集計をしたい月は前月に
　　なってしまう。だから[当月]と言うのは前月のことで、当月が何月か分からなくならないように、締切
　　処理を完了した時点で"当月日付基準.xls"の月を変更する。尚、締切処理を終わらないで、次の月の仕入
　　などを行う場合は、来月分としてデータ保存をする必要がある。
　3. "当月売上の製番データ抽出"マクロは,、ここでは使用していない。
　4. "売上高分類"マクロは、品種別、営業所別、担当者別にデータを分類している。
　5. その分類したデータを、"売上高表"に記入している。
　6. "売上目標と実績グラフ更新"マクロで、"目標と実績グラフ"シートにデータを記入している。
　　データが変わるから、グラフは当然変更される。
　7. "月別売上実績グラフ更新"マクロは、[売上高表]を基に "売上実績グラフ" のデータを記入している。
　　これによりグラフは更新される。

● ファイルを開くと、Ｅドライブにある [当月売上.xls] から、ファイルを開かないでデータを取得して、
　 [売上高表]を作成し、それを基に棒グラフと折れ線グラフを作成する。

　
実践コース
ファイルを開くと、データを取得してグラフを自動作成

実際に使っているシステムの内容で解説
　　EXCEL VBA 講座