Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2016)
.leftで得られる数値と各列のピクセルの関係
投稿日時: 20/11/06 11:51:49
投稿者: m-i-kss

初めて質問します。
マクロ3個目くらいの初心者なのでお許しください。
 
各行のRows(行数).Topはそれまでの行高さのピクセル値の合計と一致するようですが、
各列の、Columns(列数).left で得られる数値は、2倍してもそれまでの列幅のピクセル値の合計と
一致しないのです。
 

For col = 2 to 10
    debug.print (columns(col).left - columns(col - 1).left ) * 2
Next col

これで、各列幅がピクセル値で得られると思ったのですが違うようです。
 
高さのピクセルと高さの数字 との関係と、
幅のピクセルと幅の数字 との関係が異なることもさっき知ったレベルなので、
思い込んでいた定義そのものが違うとしか思えません。
 
.leftってなんでしょう?単位とかあるのでしょうか?
 
左側に非印字エリアがあるとか、そういう条件でずれるのでしょうか?

回答
投稿日時: 20/11/06 13:33:16
投稿者: Suzu

m-i-kss さんの引用:
各行のRows(行数).Topはそれまでの行高さのピクセル値の合計と一致するようですが、
各列の、Columns(列数).left で得られる数値は、2倍してもそれまでの列幅のピクセル値の合計と
一致しないのです。

 
Top、Left、Height、Width の単位は ピクセル ではなく、ポイントです。
 
https://docs.microsoft.com/ja-jp/office/vba/language/glossary/vbe-glossary#point
『ポイント
1/72 インチ。 通常、フォント サイズはポイントで測定されます。』
 
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.top
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.left
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.height
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.width
 
Range("A2").Top - Range("A1").Top も「ポイント」になります。
 
行の高さは、マウスにて 画面左の 1〜の行番号の間の仕切りをドラッグする要領で
左クリックをしたままの状態にすると表示されます。
行番号を右クリック 行の高さ で表示されるのも、「ポイント」となります。
 
対して
Range("B1").Left - Range("A1").Left も 「ポイント」ですが
列番号を右クリック 列の幅 で表示されるのは、「文字幅」になります。
 
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.columnwidth
『1 単位の列幅は、標準スタイルの 1 文字の幅に等しくなります。 』
 
【ピクセル?ポイント?Excelで使われる単位の話】
https://allabout.co.jp/gm/gc/297706/
が参考になると思います。

回答
投稿日時: 20/11/06 13:42:07
投稿者: WinArrow
投稿者のウェブサイトに移動

Excelでは
 
セル列幅を指定する場合は文字数で指定します。
セルの列幅は、ポイントで取得できます。
セルの行高は、指定も取得もポイントです。
 
これからわかるように
セルの左端(LEFT)は、ポイントで取得となります。
セルの上端(TOP)は、ポイントで取得となります。
 

投稿日時: 20/11/06 14:20:56
投稿者: m-i-kss

ありがとうございます。
ポイントとピクセルの違いをまず勉強します。
 


Sub test()

    Dim Base As Double
    Dim Height As Double
    Dim freeform As FreeformBuilder
    Dim Row As Integer

    Set freeform = _
        ActiveSheet.Shapes.BuildFreeform(msoEditingAuto, Columns(2).Left, Rows(2).Top)

    For Row = 2 To 5

        Base = Columns(Row).Left
        Height = Rows(Row + 1).Top - Rows(Row).Top '.Heightとか知りませんでした
    
        freeform.AddNodes msoSegmentLine, msoEditingAuto, 250, Rows(Row).Top + Height / 2
        freeform.AddNodes msoSegmentLine, msoEditingAuto, Base, Rows(Row + 1).Top
    Next Row

    With freeform.ConvertToShape.Line
        .Weight = 0.5
        .ForeColor.RGB = vbRed
    End With

End Sub

 
見様見真似なのですが、こういうコードを作って、
頭の中ではジグザグの始点・終点・曲がり角がセルを囲むグレーの線の交点上に来て、
頂点とでも呼ぶべき点が各列の高さの中央に来るイメージでした。
 
A1〜E5まで、各列の列幅や高さがバラバラでも問題なく交点上に来るかと思いきや、
それができるシートと、
これを別のシート上で走らせると微妙にずれるシートがあるために今回の質問に至りました。
 
きっちり交点上に来るために、何が必要なのでしょうか?

回答
投稿日時: 20/11/06 15:15:08
投稿者: Suzu

m-i-kss さんの引用:

頭の中ではジグザグの始点・終点・曲がり角がセルを囲むグレーの線の交点上に来て、
頂点とでも呼ぶべき点が各列の高さの中央に来るイメージでした。
 
A1〜E5まで、各列の列幅や高さがバラバラでも問題なく交点上に来るかと思いきや、
それができるシートと、
これを別のシート上で走らせると微妙にずれるシートがあるために今回の質問に至りました。
 
きっちり交点上に来るために、何が必要なのでしょうか?

 
 freeform.AddNodes msoSegmentLine, msoEditingAuto, 250, Rows(Row).Top + Height / 2
 
こちら、X方向 250で固定なのですが良いのでしょうか?
 
 
 
ご希望の形状について想像がつかないのですが、なんとなくで。。
 
Sub Sumple()
 
Dim cnt As Long
Dim freeform As FreeformBuilder
 
With ActiveSheet.Range("B2")
    Set freeform = ActiveSheet.Shapes.BuildFreeform(msoEditingAuto, .Left, .Top)
 
    For cnt = 0 To 3
        With .Offset(cnt, cnt)
            freeform.AddNodes msoSegmentLine, msoEditingAuto, .Left + .Width / 2, .Top + .Height / 2
            freeform.AddNodes msoSegmentLine, msoEditingAuto, .Left, .Offset(1, 0).Top
        End With
    Next cnt
End With
 
 
With freeform.ConvertToShape.Line
    .Weight = 0.5
    .ForeColor.RGB = vbRed
End With
End Sub

投稿日時: 20/11/06 15:56:09
投稿者: m-i-kss

ありがとうございます。
250の部分は、今回とは関係ない変数なので、適当に固定してしまいました。
なので、ギザギザのイメージとしては、お寿司のバランを縦にしたような、
X座標はベースと定数の往復になると思いますが、それで大丈夫です。
 
また、記述の仕方も大変勉強になります。本件が解決したら改めて勉強します。
 
本題ですが、お示しいただいたコードでも、シートによってはきっちり交点に来ません。
cntの数を重ねるごとに、ずれがどんどん蓄積されている感じです。
画像をお見せできないのが心苦しいですが…。
合わないシートは決まって合いません。合うものもあります。
 
そのため、シートの設定などで何か変わるのでは?と思ったのですが…。
 
コードの問題とは別次元と言いますか、その辺りが何かあるのかないのか、
そんなレベルで首をかしげています。
つたない説明にお付き合いいただき申し訳ないです。

回答
投稿日時: 20/11/06 16:14:00
投稿者: WinArrow
投稿者のウェブサイトに移動

Excelでhポイントで指定するが、結局は、ピクセルに変換されて画面表示となります。
Excelでは、CADのような精度で図形を作成/更新できないので、限界かもしれません。
 
工夫としては、セル幅をピクセルピッチに合わせて調整するとか・・・・

回答
投稿日時: 20/11/06 17:05:07
投稿者: Suzu

引用:
本題ですが、お示しいただいたコードでも、シートによってはきっちり交点に来ません。
cntの数を重ねるごとに、ずれがどんどん蓄積されている感じです。
画像をお見せできないのが心苦しいですが…。
合わないシートは決まって合いません。合うものもあります。

 
という事は、「シート」 極端に言うと、「セル」の設定によって決まる。
違いを比べてみては如何でしょうか。
 
 
 
セルの設定で、位置や、幅・高さ に影響を及ぼす 事項として思いつくのは
 
行・列 が非表示になっている
セルの書式設定
 [配置]   文字の配置/文字の制御/方向
 [フォント] フォント名/文字飾り
 [罫線]   罫線種類

回答
投稿日時: 20/11/06 17:21:31
投稿者: チオチモリン

※ 高さの場合
Height と RowHeight の関係はイコールではないので、セルの高朝を設定する時は誤差の出ないようにピクセルと等しくなる値(一般的に0.75の倍数)で設定すればよいです。
 
Sub Test()
    Dim i As Single
    For i = 1 To 100
        Rows(i).RowHeight = 12 + i / 20
        Cells(i, 1).Value = 12 + i / 20
        Cells(i, 2).Value = Rows(i).Height
        Cells(i, 3).Value = Rows(i).RowHeight
        Cells(i, 4).Value = Cells(i, 1) = Cells(i, 2)
        Cells(i, 5).Value = Cells(i, 2) = Cells(i, 3)
    Next
End Sub
 
※ 幅の場合
幅も同様なのですが、幅は ColumnWidth で設定するので、標準フォントの影響を受けます。
よって難易度がぐっと上がります、
 

回答
投稿日時: 20/11/06 19:20:24
投稿者: simple

チオチモリンさんのTips興味深く拝見しました。
 
行の高さは、Rowheightで設定する(それしか無い)ことになりますが、
それがそのまま設定されるのではなく、実際の高さは、
Heightで表現されるものになるわけですね。
 
そうすると、コード中でHeightで測定し、
それを使って図形を書けば、齟齬は出てこない、
ということにはならないのですか。
単位もPointで整合しているわけですね。
 
今まで、こうした事象には当たらなかったのは幸いだったということなんでしょうか。

回答
投稿日時: 20/11/07 00:57:59
投稿者: チオチモリン

>実際の高さは、Heightで表現されるものになる
どうなんでしょ
私の理解(経験則)はセルサイズの設定は(ディスプレイ)環境の影響を受けるので1ピクセルのポイント数(0.75)の整数倍に設定(RowHeight)すれば誤差は出ないというものです。
 
Sub Test2()
    Dim i As Single
    Cells(9, 1).Resize(, 2).Value = Array("設定RowHeight", "取得Height")
    With Cells(10, 1)
        .RowHeight = 13.5
        .Value = .RowHeight
        .Offset(0, 1).Value = .Height
    End With
    With Cells(11, 1)
        .RowHeight = 14.25
        .Value = .RowHeight
        .Offset(0, 1).Value = .Height
    End With
    With Cells(12, 2)
        .Value = Rows("10:11").Height
    End With
     
    With Cells(13, 1)
        .RowHeight = 13
        .Value = .RowHeight
        .Offset(0, 1).Value = .Row
    End With
    With Cells(14, 1)
        .RowHeight = 14.25
        .Value = .RowHeight
        .Offset(0, 1).Value = .Height
    End With
    With Cells(15, 2)
        .Value = Rows("13:14").Height
    End With
    Range("A10:B15").NumberFormatLocal = "00.00"
End Sub
 

回答
投稿日時: 20/11/07 07:09:40
投稿者: simple

チオチモリンさんがおっしゃっているのは、
・行の高さは一定の刻みでしか設定できない
・従って、作成しようとする長方形の高さがその刻みに合わない場合には、
  長方形の境界が行の境界と一致しないことはあり得る
ということでしょうか。
今まで余り考えたことがなかった話なので、大変参考になりました。
 
任意の高さの長方形を想定したときは、確かにそのとおりかと思います。
 
で、私の立場は、むしろ、既にあるセルの行や幅は所与のものと考えるところにあります。
そこまではExcelの持つ制約でやむを得ないと考えるということです。
 
それを認めた上で、
そのセルたちのLeft,Top,Width,Heightに基づいて作図
(例えば、セル範囲に合わせた長方形描画)する限りは、
図形の端点などがシートの枠線から外れることはないはず、ということです。
 
 
■ひとつの追加実験
20/11/06 17:21:31に提示されたtest()に加えて、
下記のmacro1(),macro2()を実行してみてください。
 
これは、長方形の高さに注目して、ふたつの方法で長方形を描いています。
・macro1が、行の Heightを使って長方形の高さを設定するのに対し、
・macro2は、行のRowHeightを使って長方形の高さを設定したものです。
 

Sub Macro1()    'Heightをそのまま使用
    Dim shp As Shape
    Dim l#, t#, w#, h#
    Dim k As Long
    For k = 1 To 30
        l = 310 + (k - 2) * 20  ' ■■
        t = Cells(k, 1).Top
        w = 20
        h = Rows(k).Height      ' ■■
        Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, l, t, w, h)
        With shp.Line
            .Visible = msoTrue
            .Weight = 0.25
        End With
        shp.Fill.Visible = msoFalse
    Next
End Sub

Sub Macro2()    'Rowheightを使用(試験実施)
    Dim shp As Shape
    Dim l#, t#, w#, h#
    Dim k As Long
    For k = 1 To 30
        l = 360 + (k - 2) * 20  ' ■■
        t = Cells(k, 1).Top
        w = 20
        h = Rows(k).RowHeight   ' ■■
        Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, l, t, w, h)
        With shp.Line
            .Visible = msoTrue
            .Weight = 0.25
        End With
        shp.Fill.Visible = msoFalse
    Next
End Sub

【観察】
ズームで拡大すると分かる程度ですが、
macro2は接合点で若干の食い違いが生じます。
 
これに対して、macro1はそうした不一致はなく、スムーズです。
このことから、実際に行の高さとして設定されているのは、Heightに近いと思います。
(RowHeightは論理上の高さ、
 Heightは描画ピクセルが離散的なことを考慮して調整されたあとの、実際の高さ、
 とでも呼べるかもしれません。環境によって変化します。)
 
ですから、
Rangeに対して、Left,Top,Height,Widthなどのmetricsを一貫して適用して位置を測定し、
それに基づいて図形描写を行う限りにおいては、
質問者が指摘されるような齟齬は発生しないように思います。
(別の環境に移した時に挙動が変わるといった話は除きます。)
 
いやそうでもないというのであれば、
質問に至った事象が再現できるコード一式を提示していただくとよいと思います。

回答
投稿日時: 20/11/07 13:57:00
投稿者: チオチモリン

そうでした。
久しぶりの命題に思い込みによる勘違いがありました。
ご指摘ありがとうございます。
お手間を取らせて申し訳ありませんでした。
 
投稿日時: 20/11/06 17:21:31の趣旨は、
設定しようとした高さ(RowHeight:手動も)は0.75刻みのHeightに丸められるので、そこで誤差が生じることがある。
誤差が生じない高さ(ピクセル単位:0.75ポイントの倍数)で設定すれば誤差は生じないはず。
というつもりでした。
 
Sub TestB()
    Dim i As Single
    For i = 1 To 30
        Rows(i).RowHeight = 12 + i / 20
        Cells(i, 1).Value = 12 + i / 20
        Cells(i, 2).Value = Rows(i).Height
        Cells(i, 3).Value = Rows(i).RowHeight
        Cells(i, 4).Value = Cells(i, 1) = Cells(i, 2)
        Cells(i, 5).Value = Cells(i, 2) = Cells(i, 3)
         
        Cells(i, 7).Value = Range(Rows(1), Rows(i)).Height
        Cells(i, 8).Value = WorksheetFunction.Sum(Range(Cells(1, 1), Cells(i, 1)))
        Cells(i, 9).Value = WorksheetFunction.Sum(Range(Cells(1, 2), Cells(i, 2)))
        Cells(i, 10).Value = WorksheetFunction.Sum(Range(Cells(1, 3), Cells(i, 3)))
    Next
    Cells(31, 1).Resize(, 10).Value = Array("設定", "H", "RH", "比較1", "比較2", "", "範囲H", "積算設定", "積算H", "積算RH")
End Sub

回答
投稿日時: 20/11/07 15:01:13
投稿者: WinArrow
投稿者のウェブサイトに移動

RowHeight と Height の違いについて
【取得】
1行を対象にした場合は、RowHeight = Height です。
 
複数行を対象にした場合は、
Rowheight は、対象の先頭行の値
Heightは、対象行の行高の合計値
 
【設定】
対象行が1行の場合でも、複数の場合でも
RowHeight を使用します。
Heightは使用できません。
 
なお、複数行場合は、対象行のすべて同じ行高になります。

回答
投稿日時: 20/11/07 15:13:02
投稿者: WinArrow
投稿者のウェブサイトに移動

お穴痔大きさの図形をシートに作成する場合の工夫
 
最初に、手操作で
方眼紙のようなシートを作成します。
例えば、
全列幅を、ピクセル:10 にする
全行高を、ピクセル:10 にする
※行間、列間に図形を配置しなくてもよいよいサイズにしましょう。
 
手操作でも、VBAでも
図を作成した場合は、左上も右下も枠線にFitさせる。
 

投稿日時: 20/11/09 09:02:56
投稿者: m-i-kss

週末に確認ができなかったので、今朝確認し
大変更新が進んでいることに驚きました。
 
が、すみませんが初心者には理解の域を超えているようです。
 
時間が取れ次第、セルの設定をいじってずれがどのように変わるか確認しますが、
ひとまず私は「仕様」あるいは「私のコードが悪い」という認識で処理します。
 
初めての書き込みでしたが、初心者にはまだまだ理解がしにくいですね。
 
長時間お付き合いいただきました皆様、誠にありがとうございました。