VBA実行後の特定の列の書式設定が適用されません。

Question

前提・実現したいこと

マクロ実行後（レコードを並べ替える操作）に、特定の列の書式設定が反映されません。
L列に2018年1月1日と値が設定されていて、セルの書式設定ではユーザー定義で
m"月"d"日"(aaa)　と設定していますが、そのまま例えば「2018年1月1日」と表示されています。
「１月１日（日）」のように表示したいです。

対象のセルをダブルクリックあるいはセルを選択後、F2+Enterで設定した書式の値になることは
確認できています。

発生している問題・エラーメッセージ

VBA実行後の特定の列のセルにおいて、セルの書式設定が反映されない。
以下のソースコードを追加してもセルの書式設定が反映されず、セルをダブルクリックすると
設定している値の形式となる。

該当のソースコード

説明
Ｌ列に日付のデータが入っています。データ自体はL12から下に入っていますが、
データ量は可変のためL列全体を対象にしています。

Sub run()
～既存の処理～
End Sub

'セルの書式を再設定（今回追加）
Sub sample()
Range("L:L").NumberFormat = "m月d日(aaa)" '○○月○○日（曜日）形式にしたい
Range("L:L").Formula = Range("L:L").Formula '再設定
End Sub


---------------ThisWorkbookのソース変更------------------------

Private Sub Workbook_Open()

    '既存処理
    run

    '今回の処理
　　sample
  
End Sub

試したこと

上記の書式の再設定や
以下のURLを参考にしましたが、挙動に変わりはありません。
https://qiita.com/eiryu/items/0d9413622c84d9514c27

値を分解する方法やリフレッシュする方法も現在平行して調べています。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

Answer 1

値で貼付けをしてませんでしょうか？

文字列として張り付いている可能性が高いように思います。

書式をセットした後に該当箇所で
Range.Value = Range.Value
と実行してあげると、
F2+Enterと同じ結果になるはずです。

Comments

[KSwordOfHaste]

おぉ・・・そんなテクニックがｗ；

[skjdujr9djhf]

Sub sample()
Range("L:L").NumberFormat = "m月d日(aaa)" '○○月○○日（曜日）形式にしたい
'Range("L:L").Formula = Range("L:L").Formula '再設定
Range("L:L").Value = Range("L:L").Value
End Sub

で実行してみましたが、結果は変わりませんでした。

[ExcelVBAer]

こちらの環境(Win7+2010)で、文字列型の書式にしたセルに日付を入力し、
上記のコードを試したところ、書式は反映されました。

[ExcelVBAer]

まさかなんですが、セルに「2018年1月1日」がそのまま入力されているのでしょうか？
「年」「月」「日」という値が入ってしまっていると、
文字列でしかないので、表示形式では変わりません。

日付型の値（数値）のみ表示形式で見せ方を変えれます。

[skjdujr9djhf]

セルに「2018年1月1日」と入っています。
数式バーも確認しましたが同様に「2018年1月1日」と入っています。

そうなると、現在はセルに「2018年1月1日」と出ていますが、初期状態でyyyy/mm/ddの形式で出すようにしてその後、

Sub sample()
Range("L:L").NumberFormat = "m月d日(aaa)" '○○月○○日（曜日）形式にしたい
Range("L:L").Value = Range("L:L").Value
End Sub

とするのが一番良いでしょうか？

[ExcelVBAer]

そうです！
例：DateSerial(2018,1,2)

[KSwordOfHaste]

なるほど・・・
Range("B:B").NumberFormat = "m月d日(aaa)"
Range("L1:L1").Value="2018年1月1日"
Range("L:L").Value=Range("L:L")
とやると確かに2018年1月1日のままですね。F2+ENTERやって初めて1月1日(月)になりました。
そうするとRange.Value = Range.ValueはF2+ENTERとはいくぶん違う動きということになるんですね？

[skjdujr9djhf]

年月日が入っているとCdate関数では日付型のデータにはできないのですね。
色々と勉強になりました。

[ExcelVBAer]

「2018年1月1日」が F2+Enter で日付に自動変換されるんですね。
そっちの方が意外というか、知りませんでした。

ただ Excel の自動変換に頼るのはリスクがあるので、
できるだけ素直にプログラムで処理する方が無難ですね。

[ExcelVBAer]

日付は Excel では本来シリアル値（43158 → 2018/2/27等）で扱われていて、
セルに文字列を入力した時には、Excel が自動判別して
シリアル値＋表示形式を自動調整してくれます。

しかし、VBAには、その自動調整(どういう調整か不明)を再現するメソッドは用意されてません。
なので、数値等(年、月、日)から日付型に変換する等、
素直に日付型で出力した方が無難です。

また、逆のパターン（日付で出力するけど、セルに変な自動調整されたくないから文字列で出す）
の場合もあるかもしれませんね。

[KSwordOfHaste]

> 素直にプログラムで処理する方が無難
おっしゃるとおりと思います。
> 年月日が入っているとCdate関数では日付型のデータにはできない
あれれ・・・自分はExcel2003(古！)ですが、VBAでCDate("2018年1月1日")とするとDate型に変換できますよ？

[ExcelVBAer]

あまりやらないのですが「CDate("2018年1月1日")」で変換できますね。
ただ、引数にはセル１つ分の値しか渡せないので、
後から変換するなら、元の処理で値を調整した方が早いでしょう。

KSwordOfHaste さん、ありがとうございました！

[KSwordOfHaste]

今回のQ&Aで同じ文字列でも
Range.Value="2018年1月1日" '(A)
Range.Value="2018/1/1" '(B)
Range.Value=Range.Value
としたとき、(B)は日付に変換されるのに(A)は日付に変換されないというのが意外で、落とし穴にはまりそうな仕様に思えました。
> 元の処理で値を調整した方が早い
たしかにそれが一番確実ですね！
こちらこそありがとうございました。

Answer 2

手元の環境では再現しませんでした。
run関数が何かしら影響しているのではないでしょうか。
試しにrun関数は実行しないようにしても、結果は同じなのでしょうか？（L列にはあらかじめデータを入れておく）

Comments

[skjdujr9djhf]

run関数では特に形式等をいじってないので、影響することはないと思っています。
run関数を実行しないようにしても結果は同じでした。

[ttyp03]

いや、形式をいじっているかどうかではなく、値の設定の仕方や画面更新を抑制していたりとか、そのあたりどうなっているのかな、と。
まあでも、run関数を実行しなくても結果は同じなんですよね…。

Answer 3

L列に設定されている式の結果が日付型になっていないのではないでしょうか。
例えば="2018/2/27"なんて設定されているといくら日付の書式を指定しても「セルの値である文字列そのもの」しか表示されませんので。

Comments

[skjdujr9djhf]

Sub kata()
r = Cells(12, 12).Value
result = VarType (r)
Debug.Print (result)
End Sub

という関数を作って、L12の値の型を調べましたが、８が返ってきました。
文字列になっているみたいです。

Sub sample()
Range("L:L").Value = CDate(Range("L:L").Value)
End Sub

でＬ列の値を日付型に変換しようとしていますが、「型が一致しません」というエラーになります。
L列の値を日付型に変換するにはどうしたら良いでしょうか。

[KSwordOfHaste]

CDateに渡せるのは文字列か数値ですのでRangeオブジェクトは渡せません。
ExcelVBAerさんの方法がスマートだと思いますが、もしそれで結果がかわらないのなら「そのセルの値が日付として妥当でない文字列だった」のだろうと思います。自分もやってみましたけど、

Sub te()
　Range("B:B").NumberFormat = "m月d日(aaa)"
　Cells(1, 2) = 100
　Cells(2, 2) = "2/2"
　Cells(3, 2) = "2018-3-3"
　Cells(4, 2) = "2018-4-4-"
　Range("B:B").Value = Range("B:B").Value
End Sub
こうしたところB列は上から順に
4月9日(月)
2月2日(金)
3月3日(土)
2018-4-4-
になりました。4つ目は「日付とはいえない」ためF2 ENTERとしても文字列のままですよね・・・

[skjdujr9djhf]

検証ありがとうございました。
まだ型も色々と分かっておらず、色々と混同していました。