先にセル書式が必要なのはなぜ ？

質問

以下のようなコードで時間相当(mm:ss 又は　hh:mm:ss)をファーマット変更して
別シートに書き出します。

A1:　00:00 イントロ

コード自体は、処理上問題ないのですが、
ここに行き着くまでに思うような書き出しが出来ずに試行錯誤しました。

肝は、
以下のコードで事前にD列の書式を文字列にしないと
wst.Range("D:D").NumberFormatLocal = "@" ' D列は文字列 <----- 最初にセル書式設定する (ここPOINT !!)

Chapter(wsc)シートに書き出すファーマットが
CHAPTER01=0.000 などになって

正解の
CCHAPTER01=0:00:00.000　になりません。

D列は、書き出時に以下のコード文字型に変換しているので
わざわざ事前にD列の書式を文字列に設定する必要は無いように思えるのですがなぜでしょうか？
wst.Cells(i, "D") = CStr

'-------------------------------------------

Sub Make_Chapter()

Dim chap1 As String, chap2 As String
Dim mlow As Long
Dim wst As Worksheet, wsc As Worksheet
Dim i As Long

Set wst = Worksheets("Target")
Set wsc = Worksheets("Chapter")
chap1 = "CHAPTER"

wst.Activate

mlow = wst.Cells(Rows.Count, 1).End(xlUp).Row

wst.Range("D:D").NumberFormatLocal = "@" ' D列は文字列 <----- 最初にセル書式設定する (ここPOINT !!)

Dim cj As Long
For i = 1 To mlow
cj = StrCount(Cells(i, "B"), ":") 'Functionで時間区切り「:」の個数をカウント
If cj = 1 Then '１時間以下の文字列
wst.Cells(i, "D") = CStr("0:" & Cells(i, "B")) '頭に「0:」を付けて時間数値を文字列に変換
Else '１時間以上の文字列
wst.Cells(i, "D") = CStr(Cells(i, "B")) 'そのまま時間数値を文字列に変換
End If
Next

Dim mj As String, mm As String
Dim k As Long
k = 1 'CHAPTERのカウンター

'書き出しシートの初期化
wsc.Cells.Clear

'整形
'形式は、２行で１組 (2*i-1)がPOINT
For i = 1 To mlow
mj = chap1 & Format(k, "0#") & "=" 'CHAPTER##=
mj = mj & wst.Cells(i, "D") & ".000" 'CHAPTER##=hh:mm:ss.000
wsc.Cells(2 * i - 1, "A") = mj

mm = chap1 & Format(k, "0#") & "NAME=" 'CHAPTER##NAME=
mm = mm & wst.Cells(i, "C") 'CHAPTER##NAME=指定文字列
wsc.Cells(2 * i - 1, "A").Offset(1, 0) = mm 'offsetで1行下に書き出す
k = k + 1 'CHAPTERのカウンター更新
Next

'処理が終わったのでセルはクリアー
wst.Range("B:D").ClearContents

'（他の処理が続きますが、相談内容には無関係なので割愛します。）

'処理終了
MsgBox "完了！"

Set wst = Nothing
Set wsc = Nothing

End Sub

回答No.2

kkkkkm · ベストアンサー

> D列の見た目は、変更しても変更しないでも
> 同じで文字列として書き込まれているので区別が付きません。

変更せずに標準のままだと、ユーザー定義に変更されると思います。00:00でしたらh:mm:ssとかで右寄せ。数値としては「0」です。

なので
mj = mj & wst.Cells(i, "D") & ".000"
で
CHAPTER01=0.000
となるのだと思います。

> 又、事前のD列の書式設定以外に対策はありますか　？

手動での説明ですが、事前に書式設定しておくという方法ですね。

数値の表示形式を設定する
https://support.microsoft.com/ja-jp/office/%E6%95%B0%E5%80%A4%E3%81%AE%E8%A1%A8%E7%A4%BA%E5%BD%A2%E5%BC%8F%E3%82%92%E8%A8%AD%E5%AE%9A%E3%81%99%E3%82%8B-f27f865b-2dc5-4970-b289-5286be8b994a

表示形式の設定
http://officetanaka.net/excel/vba/cell/cell08.htm

こちらでは、VBAの説明で「先に表示形式を設定しなければなりません」とありますので、方法はないのかもしれません。

数値関連を文字列として入力したい場合は、先に文字列設定しておくと思っていたらいいのではないでしょうか。

お礼

ありがとうございます。

書式設定以外に対策は無くて、
数値関連を文字列として入力したい場合は、
　先に表示形式を事前に文字列設定にすると言うことですね。

記事参考になりました。

データを入力してから表示形式を設定しても、逆に表示形式を設定してからデータを入力しても同じです。
ただし、「0123」のようなデータを文字列として入力したいようなケースでは、先に表示形式を設定しなければなりません。

入力すると自動的に表示形式が設定される日付データでも、
あらかじめ表示形式が設定されているセルの場合はExcelが自動的に設定することはありません。
先に設定している表示形式が有効になります。

セルにテキスト形式を適用すると、 この数値の 自動書式設定を防ぐことが可能です。

回答No.1

kkkkkm

エクセルの自動書式設定のせいじゃないでしょうか。
書式設定していないセル(標準とか)はエクセルの思惑で表示するみたいな。

お礼

kkkkkｍさん、お世話になります。

以下をコメントに変えて
wst.Range("D:D").NumberFormatLocal = "@"

以後を実行しても
D列の見た目は、変更しても変更しないでも
同じで文字列として書き込まれているので区別が付きません。

なのに別シートにD列を利用して書き出された結果が以下のように異なります。

CHAPTER01=0.000
CCHAPTER01=0:00:00.000　

どんな時にこんな分かるにくい状態が発生するかの
目安のような考え方は有るのですか？

（目安がわかれば、今回のような試行錯誤する時間が短縮されるような思えます。）

又、事前のD列の書式設定以外に対策はありますか　？