excelVBAのオートシェイプ幅設定について

質問

いつもお世話になっております。

以前こちらでお教えしてもったＶＢＡを試してみたのですが、うまくいきません。
自分なりに調べてきたつもりですが、オートシェイプの幅を時間と結びつけるやり方がよくわかりません。

A2=14:30
Ａ3=15:15
Ａ4=45
　　　　　Ｂ　　Ｃ　　Ｄ　…
3行目　０　　　1　　2　　…
で、4行目に、Ａ2～Ａ３のオートシェイプを時間の列と対応するよう作成したいのですが、幅が長くなりすぎてしまいます。

Columns("A:Z").ColumnWidth = 9

変数S = Cells(4, 2)
変数E = Cells(4, 25)
★変数Width = 変数E.Left + 変数E.Width - 変数S.Left
変数Height = 変数E.Height
変数Top = 変数S.Top
★変数Min = 変数Width / 1440

★変数Start = Cells(1, 2).Value * 1440 * 変数Min + 変数S.Left
変数End = Cells(1, 3).Value * 24
★変数2Width = Cells(1, 4).Value * 変数Width

ActiveSheet.Shapes.AddShape(msoShapeRectangle, 変数Start, 変数Top, 変数2Width, 変数Height).Select

となっていますが、
★部分がよくわかりません。
変数Width = 変数E.Left + 変数E.Width - 変数S.Left を求めて、なぜ、A4に*ことになるのか。
1440はどこからきた数字なのか。

そもそもこの式自体が間違っているのでしょうか。
トンチンカンな質問になっていたら申し訳ございません。
よろしくお願いします。

回答No.3

tsubuyuki · ベストアンサー

なるほど（笑）。
見たことがある案件だなぁとは思ったのですが（笑）。

掻い摘んで要件を。

★１について
ここは、四角を置く行を指定しています。
「下側から該当日付を探し、上側Ｅ列のスケジュール番号から１引いた分だけ下の行」
を指定するための式を想定しています。（ちょっとわかりづらいですね。）
つまり、図だと１２／１の１番の予定だと、
・下から１２／１を探す→１１行目が返る
・スケジュールが１番なので、１－１＝０行下を見る→１１行目
あるいは図の４行目だと
・１２／１を探す→１１行目が返る
・スケジュールが２番なので、２－１＝１行下を見る→１２行目に四角を置く
としたいので、オフセットを使って書いています。

★２について
Format関数は、数値を指定の文字列に変換する関数です。
関数についての詳細は別途お調べくださいませ。
書式を「hhnn」とすることで「１２時１５分→1215」で返してきます。
もちろん分（Minute)として「hhmm」でもちゃんと判断してくれますが、
月（Month）を表す「m」と区別したいための・・
いわゆる古い世代の人間の癖と思っていただければ目安になるかと（笑）。

ちなみにここは、四角形に「名前」を付けるための処理ですね。

★３について
ここは全体（１日分）の列幅の合計をカウントしています。
SHEndに代入されたセルの左端の座標に同セルの幅を足して、１日分の終端を、
SHStartに代入されたセルの左端の座標を１日の始端をそれぞれ計算し、
終端－始端　で１日の幅を決めています。
コードの冒頭でセルの幅を可変にするように指定していますから、
　　Columns("A:Z").ColumnWidth = 9 '列幅です。適宜。
この処理が必要、という事です。
（列幅が常に変わらないのであれば、固定値でもＯＫではあります。）
コレを１４４０（６０分×２４時間）で除することで、
１分の幅（SHMin）が決まる、と言う流れです。

行高に関しても同様と思ってください。

★４について
ここは時間を表す数値に関する話ですね。
エクセル（に限った話ではないですが）、
「０時＝０」「２４時＝１→日に繰り上がり」として計算します。
これより「２４時間＝１」「１２時間＝０．５」と計算できます。
つまり、２４時間分の四角の幅に「予定時間」を乗することで、
この予定を示す四角の幅を求めるわけです。
全体（１日）の幅が１００とし、予定が６時間（＝６÷２４＝０．２５日）であれば、
１００×０．２５＝２５　が予定の幅、と計算できるわけです。

以上、駆け足で申し訳ありませんが、解説でした。

お礼

いつもありがとうございます！
私はまだまだ勉強不足のようです。

★１については、つまり今回の件は、offsetの方が感覚的にわかりやすいので使っているということですね。cells(TagNo,1)でも問題はないと思って良いですか？
★２については、hhnnはhhmmでも可能なのですね。hhnnについて少し調べました。こちらが一般的のようですね。
★３について、こちらようやく理解できました。セルの右端を表すコードがないためこういった計算を採らないといけないようですね。1分の幅についての解説もわかりやすいです。
★４について、＞＞「０時＝０」「２４時＝１→日に繰り上がり」として計算します。 これより「２４時間＝１」「１２時間＝０．５」と計算できます。
の解説を読んで理解できました。これならどんな幅にしても対応できますね。

すべてに解説をいただき本当にありがとうございます。助かりました。
これでご教授してもらったコードについては一通り理解できましたので、これから応用を利かせていきます。
また不明点等ありましたらこちらのカテゴリに質問させていただくと思います。
見かけたらどうぞよろしくお願いします！
回答ありがとうございました！

回答No.4

tsubuyuki

> ★１については、つまり今回の件は、offsetの方が感覚的にわかりやすいので使っているということですね。cells(TagNo,1)でも問題はないと思って良いですか？

言ってしまえば、Offset以外に思いつかなかっただけです（笑）。

何と言いましょうか・・逃げ口上の一つですが、
「正しい書き方」と言うものは存在しないと思っています。
（正規表現と言うものはありますが。）
どんな書き方でも、正しい（望む）結果が得られて、実用に耐えるモノであれば、
それが自分（業務）にとっては「正しい書き方」なのだと思っています。
今回、私は試していませんが、cells(TagNo,1)でお望みの結果が得られれば、
それで全く問題は無いと言えます。

真似をして書いていくうちに段々と自分が書きやすい書き方が見えてきますよ＾＾

お礼

なるほどです笑。

＞＞どんな書き方でも、正しい（望む）結果が得られて、実用に耐えるモノであれば、
それが自分（業務）にとっては「正しい書き方」なのだと思っています。
私も同様に考えております。力技は好きです。

今回のコードではcells(TagNo,1)でも問題なく動作しましたので、問題無さそうです。
まずは、真似からですね。少しずつアレンジを加えたりしてレベルアップしていきたいです。
何度もありがとうございました！＾＾

回答No.2

tsubuyuki

お気づきの通り、
> トンチンカンな質問
と言わざるを得ません。
マクロの一部だけを見せられても、
式が間違えているかどうかの判断はつきません。

その一部分だけを見て思った事ですが・・

変数S・変数Eは何型で宣言しているのでしょうか？
例えばRangeで宣言していて、このセル番地（Ｂ４）を格納したいのであれば、
　　Set 変数S = Cells(4, 2)
としてやる必要があります。

それ以外の例えば整数型などで宣言していると
　　変数S = Cells(4, 2)
では、Ｂ４セルの「中身」を格納します。

セルの幅によって四角形の（１分当たりの）幅も変わってしまいますから、
ここで計算が狂っている可能性は大いにあります。

１４４０の意味は、
「１列を１時間と見立て、２４時間分（２４列）用意した列幅の合計」（変数Width）から
「１分の場合の直線の幅」（変数min）を計算するために
「２４時間×６０分＝１４４０分」で除している（割り算している）、
と推測できます。
コレに実際の時間（分間）を掛けることによって、
四角形の幅（変数2Width）を決めているのでしょう。

質問が曖昧なため、この回答で的を射ている自信は皆無です。
とりあえず、参考までに。

お礼

回答ありがとうございます。

意味不明な質問になってしまい、申し訳ございません。
変数S・変数EはRangeで宣言していますので、オブジェクト変数?です。
オートシェイプはちゃんと作成されるようになりました。私のミスでおかしくなっておりました。申し訳ございません。
１４４０の意味、よくわかりました。
ちなみに以前の質問はこちらです。↓
http://oshiete.goo.ne.jp/qa/7849456.html

コードはこちらです。
Sub test1()
Dim SHStart As Range, SHEnd As Range
Dim SHWidth As Single, SHHeight As Single, SHTop As Single
Dim SCStart As Single, SCEnd As Single, SCWidth As Single
Dim SCName As String
Dim TagRow As Long, TagDay As Date, TagNo As Long

Columns("A:Z").ColumnWidth = 9

TagDay = Cells(Selection.Row, 1).Value
TagNo = Cells(Selection.Row, 5).Value
★1TagRow = Range("A11:A15").Find(What:=TagDay, LookAt:=xlPart).Offset(TagNo - 1, 0).Row
★2SCName = Format(TagDay, "yymmdd") & "_" & TagNo & "_" & Format(Cells(Selection.Row, 3), "hhnn")

Set SHStart = Cells(TagRow, 3): Set SHEnd = Cells(TagRow, 26)
★3SHWidth = SHEnd.Left + SHEnd.Width - SHStart.Left
SHHeight = SHEnd.Height: SHTop = SHStart.Top
SHMin = SHWidth / 1440

SCStart = Cells(Selection.Row, 2).Value * 1440 * SHMin + SHStart.Left
SCEnd = Cells(Selection.Row, 3).Value * 24
★4SCWidth = Cells(Selection.Row, 4).Value * SHWidth

ActiveSheet.Shapes.AddShape(msoShapeRectangle, SCStart, SHTop, SCWidth, SHHeight).Select
With Selection.ShapeRange
With .Fill
.ForeColor.RGB = RGB(255, 255, 255)　　
End With
With .Line
.ForeColor.RGB = RGB(0, 0, 0)　　
.Weight = 0.5
End With
.Name = SCName
End With
End Sub

★部分以外は大体理解できたのですが、
★1のOffset(TagNo - 1, 0)は、Cells(TagNo,1)ではだめですか？あとで編集をしやすくするための措置でしょうか？
★2のhhnnは時刻の表示形式ですか？
★3のコードがよくわかりません。後ろで/1440としているので、時間のシリアル値が算出されるのでしょうか？
★4は、SHWidthが分のシリアル値になるはずだと思うのですが、Cells(Selection.Row, 4).Valueも分のことであり、分*分…？となるのですが…。よくわかりません。

長くなり申し訳ございません。
1つだけでもお教えいただければ幸いです。

回答No.1

web2525

1440は時間のシリアル値を分表示に変更するための係数

以前の問い合わせということですが、ご自身の質問履歴非公開のためどんな内容の回答から作成されたマクロなのか全く見えてこない

再質問するのであれば前回の質問/回答のリンクを張るか、質問履歴を公開設定されたほうが良いかと思われます

お礼

回答ありがとうございます。
時間のシリアル値を分表示にするための係数だったのですね！
幅の件は、色々触っていると不具合は解消できました。私の単純ミスでした。
しかし、まだコードの理解自体はできておりません。

申し訳ございませんでした。
改めて出直したいと思います。
http://oshiete.goo.ne.jp/qa/7849456.html