vbaで標準モジュールの変数を引用したい

質問

vbaを勉強しています。以下のようなコードを書きました。（長くなってしまい申し訳ありません）
ユーザーが指定した日付で工程表を作成したいのですが、イベントプロシージャ？（と呼ぶかどうかよくわからないのですが）がうまく動きません。標準プロシージャのイン尾うっとボックスで取得した日付をイベントプロシージャで使うにはどうしたらよいでしょうか。
また、インプットボックスで日付を指定する際に、既存にある日付を削除してから記述をしたいのですが、どのようにしてよいかわかりませんでした。

↓標準モジュール

Const HIDUKE_ADRS As String = "E2" '日付セル位置
Const MONTH_OFST As Integer = 0 '月の行の位置
Const DAY_OFST As Integer = 1 '日の行の位置
Const WKDAY_OFST As Integer = 2 '曜日の行の位置

Const FIRST_DAY As Integer = 1 '月の変わり目の日
Dim myDate As Date '処理中の日付を表す変数
Dim baseCell '基点セル
Dim LC As Long '最終列
Dim LR As Long '最終行
Sub 日付描画()
Dim orgDate As Date
Dim dstDate As Date
orgDate = InputBox("開始年月日を入力してください。例:2012/5/1")
dstDate = InputBox("終了年月日を入力してください。例:2013/3/31")

Set baseCell = Range(HIDUKE_ADRS) '基点セルを日付にセット

　↓以下の作業をする前に、既に記述されている日付を削除したい。

'----------- 月と日と曜日を描画 -----------
baseCell.Activate '基点セルをアクティブセル化

myDate = orgDate 'myDateを開始年月日にセット

Do Until myDate > dstDate '処理中の日が終了年月日に達するまでループを回す

With ActiveCell
'月の変わり目の日と表の最初の行のみ月を描画
If Day(myDate) = FIRST_DAY Or .Column = baseCell.Column Then
.Value = Month(myDate) & "月" '月を入力
.Borders(xlEdgeLeft).Weight = xlThin 'セル左辺に罫線を引く
End If

.Interior.Color = vbBlue
.Offset(DAY_OFST, 0).Value = Day(myDate) '日を入力
.Offset(DAY_OFST, 0).ColumnWidth = 3
.Offset(WKDAY_OFST, 0).Value = WeekdayName(Weekday(myDate), True) '曜日を入力

'日の列～スケジュール欄の列に格子状の罫線を引く
Range(.Offset(DAY_OFST, 0), .Offset(WKDAY_OFST, 0)).Borders.Weight = xlThin

'日曜のセル背景を黄色にする
If Weekday(myDate) = vbSunday Then
Range(.Offset(DAY_OFST, 0), .Offset(WKDAY_OFST, 0)) _
.Interior.Color = vbYellow
End If

myDate = DateAdd("d", 1, myDate) '処理中の日付を1日進める
.Offset(0, 1).Activate 'アクティブセルを1行進める
End With
Loop

End Sub

↓イベントプロシージャ（と呼ぶのでしょうか？）

Private Sub Worksheet_Change(ByVal target As Range)
End Sub
Private Sub Worksheet_SelectionChange(ByVal target As Range)
' 工程ライン作成
Dim orgDate As Date
Dim myDate As Date '処理中の日付を表す変数
Dim X1 As Single
Dim Y1 As Single
Dim X2 As Single
Dim Y2 As Single
Dim kiten As Range
Dim Kikan As Long
Dim start As Long
Dim i As Long
Dim LR As Long '最終行
LR = Range("D65536").End(xlUp).Row
Set kiten = Range("E4")
If target.Column = Range("C:D").Column Then
Call 日付描画(orgDate)　　←ここが駄目なようです。。
myDate = orgDate
On Error Resume Next
For i = 5 To LR
ActiveSheet.Shapes("KOUTEILine " & i).Delete
Next i
For i = 5 To LR
start = Cells(i, 3).Value - myDate
Kikan = Cells(i, 4).Value - Cells(i, 3).Value
X1 = Range(Cells(1, 1), Cells(1, 4 + start)).Width
Y1 = Range(Cells(1, 1), Cells(i - 1, 1)).Height + Cells(i, 1).Height / 1.1
X2 = Range(Cells(1, 1), Cells(i, start + 5 + Kikan)).Width
Y2 = Y1
With ActiveSheet.Shapes.AddLine(X1, Y1, X2, Y2)
.Name = "KOUTEILine " & i
.line.EndArrowheadStyle = msoArrowheadTriangle
.line.ThemeColor = xlThemeColorAccent1
.line.Weight = 3
End With
Next i
End If

End Sub

よろしくお願いいたします。

回答No.5

TAKA_R · ベストアンサー

３です。
以前の回答を自分で読んでいて分かりにくく思ったので、コード全体を貼り付けてみました。

Const HIDUKE_ADRS As String = "E2" '日付セル位置
Const MONTH_OFST As Integer = 0 '月の行の位置
Const DAY_OFST As Integer = 1 '日の行の位置
Const WKDAY_OFST As Integer = 2 '曜日の行の位置

Const FIRST_DAY As Integer = 1 '月の変わり目の日
Dim myDate As Date '処理中の日付を表す変数
Dim baseCell '基点セル
Dim LC As Long '最終列
Dim LR As Long '最終行
Sub 日付描画()
Dim orgDate As Date
Dim dstDate As Date
orgDate = InputBox("開始年月日を入力してください。例:2012/5/1")
dstDate = InputBox("終了年月日を入力してください。例:2013/3/31")
range("e2",cells(4,activesheet.usedrange.column.count)).clear 'ここで日付のテーブルを消します
Set baseCell = Range(HIDUKE_ADRS) '基点セルを日付にセット
'----------- 月と日と曜日を描画 -----------
baseCell.Activate '基点セルをアクティブセル化

myDate = orgDate 'myDateを開始年月日にセット

Do Until myDate > dstDate '処理中の日が終了年月日に達するまでループを回す

With ActiveCell
'月の変わり目の日と表の最初の行のみ月を描画
If Day(myDate) = FIRST_DAY Or .Column = baseCell.Column Then
.Value = Month(myDate) & "月" '月を入力
.Borders(xlEdgeLeft).Weight = xlThin 'セル左辺に罫線を引く
End If

.Interior.Color = vbBlue
.Offset(DAY_OFST, 0).Value = Day(myDate) '日を入力
.Offset(DAY_OFST, 0).ColumnWidth = 3
.Offset(WKDAY_OFST, 0).Value = WeekdayName(Weekday(myDate), True) '曜日を入力

'日の列～スケジュール欄の列に格子状の罫線を引く
Range(.Offset(DAY_OFST, 0), .Offset(WKDAY_OFST, 0)).Borders.Weight = xlThin

'日曜のセル背景を黄色にする
If Weekday(myDate) = vbSunday Then
Range(.Offset(DAY_OFST, 0), .Offset(WKDAY_OFST, 0)) _
.Interior.Color = vbYellow
End If

myDate = DateAdd("d", 1, myDate) '処理中の日付を1日進める
.Offset(0, 1).Activate 'アクティブセルを1行進める
End With
Loop
range("e1").value=orgdate 'orgdateをセルに記入
End Sub

↓イベントプロシージャ（と呼ぶのでしょうか？）

Private Sub Worksheet_Change(ByVal target As Range)　　'_changeで使う
' 工程ライン作成
Dim orgDate As Date
Dim myDate As Date '処理中の日付を表す変数
Dim X1 As Single
Dim Y1 As Single
Dim X2 As Single
Dim Y2 As Single
Dim kiten As Range
Dim Kikan As Long
Dim start As Long
Dim i As Long
Dim LR As Long '最終行
LR = Range("D65536").End(xlUp).Row
orgdate=range("e1").value '日付描写（）でセルに書き出した日付を読み込む
Set kiten = Range("E4")
If target.Column = Range("C:D").Column Then　　　'call～消しました。
myDate = orgDate
On Error Resume Next
For i = 5 To LR
ActiveSheet.Shapes("KOUTEILine " & i).Delete
Next i
For i = 5 To LR
start = Cells(i, 3).Value - myDate
Kikan = Cells(i, 4).Value - Cells(i, 3).Value
X1 = Range(Cells(1, 1), Cells(1, 4 + start)).Width
Y1 = Range(Cells(1, 1), Cells(i - 1, 1)).Height + Cells(i, 1).Height / 1.1
X2 = Range(Cells(1, 1), Cells(i, start + 5 + Kikan)).Width
Y2 = Y1
With ActiveSheet.Shapes.AddLine(X1, Y1, X2, Y2)
.Name = "KOUTEILine " & i
.line.EndArrowheadStyle = msoArrowheadTriangle
.line.ThemeColor = xlThemeColorAccent1
.line.Weight = 3
End With
Next i
End If

End Sub

お礼

全部記載してくださってありがとうございます
わかりにくくなんてないですよ。いつもわかりやすくて、なるほどって思っています。
しかも頑張れとの応援頂いて更に勉強楽しくなりました。ありがとうございます。
おかげさまで思っているように動きました。

回答No.4

TAKA_R

えっとこの間の人です。
お礼のところで　＞インプットボックスを使わないでどこかのセルに記入してもらい・・・
とありましたが、いえいえインプットボックスはそのまま使ってください。
標準モジュールでインプットボックスで受け取った日付データを適当なセルに書き出して、
使いたいとき（イベントモジュール使用時）にその都度、そのセルを読み出すってことです。

では、頑張って！！

回答No.3

TAKA_R

２です。
補足に対する回答をし忘れていたので、書きます。
１の作業をすると、インプットボックスは呼び出されるけれど、イベントプロシージャの続きは実行されなかったと思います。
もしイベントプロシージャの続きを動かしたいなら、（今回はインプットボックスを登場させたくないとのことなのであてはまりませんが）subプロシージャではなくfunctionプロシージャにしないといけません。
さくっと言えば、あるプロシージャからイベントプロシージャに移るには、このfunctionプロシージャを使うしかなく、このfunctionプロシージャは呼び出されて、呼び出された先に計算した結果を返す、そういう働きをします。
そしてこのfunctionプロシージャは「関数」「ユーザー関数」と呼ばれています。
（変数とは少し違うんですね）
subプロシージャとの書き方の違いはsub→functionに変える、( )の続きに戻すデータの型を書く、endの１行前に　プロシージャ名=戻したい値　がいる、です。

回答No.2

TAKA_R

結論からすると、無理です。
というか、メンドイことをいろいろとしないといけません。

が、ダサくて良ければ、とっておきの方法があります。
その前に、前回、削除する方法を言いましたが、下の方が一発でした。
range("e2",cells(4,activesheet.usedrange.column.count)).clear

標準モジュールは単独で使ってください。
ボタンか何かをマクロのスイッチにするとよいと思います。
（また別に、イベントプロシージャを作ってもいいです）
標準モジュールの日付描画(　)はまた空欄に戻しておきます。
それから、orgdate　を邪魔にならないセルに入れます。

イベントプロシージャはworksheet_changeの方を使ったほうがよいと思います。
（希望の使い方からすると・・。）
次にorgdateを書き出したセルから読み込みます。
それから、call 日付描画（） を削除します。

書き込まれた値が気になるようなら、フォントを白にしておくのとか・・・
イベントプロシージャを書き換えると、エクセルを再起動させないと反映されないかもしれません。

お礼

すぐご回答頂いているのにお礼が遅くなってしまって申し訳ありません。
全然ださくないです。インプットボックスを使わないで
どこかのセルに記入してもらい、そのセルのvalueを使うということですよね？
ちょっとやってみているのですが、未熟なためうまく行かなくて。。
もうすこしお時間ください。。（^ ^;）
worksheet_changeの使い方もググって出直します！

あと、range("e2",cells(4,activesheet.usedrange.column.count)).clear
もまだ使えずじまいです。

回答No.1

TAKA_R

呼ばれる方（標準モジュール側）に引数が書かれていないので、動かないのだと思います。
Sub 日付描画(orgdate)で呼び出せます。

＞また、インプットボックスで日付を指定する際に、既存にある日付を削除してから記述をしたいのですが、どのようにしてよいかわかりませんでした
＞↓以下の作業をする前に、既に記述されている日付を削除したい。

１アイテムずつ（セル値、背景、罫線）消してもよいかと思いますが、削除-挿入というのも１つの手だとは思います。

お礼

さっそくのご回答ありがとうございます。
動いてくれました。でも私の認識があやまっていたようです。
sub()の（）はプロシージャそのものを変数にしてしまうものなのでしょうか。
標準モジュールの日付描画プロシージャで使用しているインプットボックスはあくまで工程表上部の日付書き換え用で、
C・D列にはそれぞれの工程項目の開始日と終了日をセルにユーザーが入力します。
CとDの値が変更されれば矢印を変形させるイベントプロシージャを書いたのですが、
今回のコードではC列の値を変更するとインプットボックスから始まって動いてしまいます。
本当は開始日のorgDateの値だけ取り出し、上部にある日付が変更されても矢印の開始点を自動修正できるようにしたかったのですが、
可能でしょうか。

説明長くなってしまってすみません。