年号をcells形式で抽出

質問

office2010

sheet1のD10に2017/05/22という書式の日付が入っています
sheet2のAG59に上記日付の年号部分を取り出します
これを学習マクロで実施すると

Worksheets("sheet2").Range("AG59") = "=DBCS(YEAR(Sheet1!R[-49]C[-29]))"
と記録されます
R[-49]C[-29]の部分はD10セルです。
D10をcellsで表現すると、Worksheets("Sheet1").Cells(10, 4)
なので、
記録マクロへ代入したら
Worksheets("sheet2").Range("AG59") = "=DBCS(YEAR(Worksheets("Sheet1").Cells(10, 4)))"
で表現されると思うのですが、

コンパイルエラー：
修正候補：ステートメントの最後
というエラーが発生します。

何がおかしいのでしょうか？
エラーにならない書式を教えて頂きたく

回答No.1

tsubu-yuki · ベストアンサー

> 年号をcells形式で
> 学習マクロ
初めて聞く言葉が多いのですが・・
「マクロの記録」で良いですか？
で、「（西暦）年」を得たい、ですね？
さらに（R1C1形式は慣れないので）Cellsプロパティを使いたい、
そういうことですね？

> Worksheets("sheet2").Range("AG59") =
> "=DBCS(YEAR(Worksheets("Sheet1").Cells(10, 4)))"
> で表現されると思うのですが、
惜しいんですが、ちょっとだけ違うようです。
　　Worksheets("sheet2").Range("AG59") = "=Dbcs(YEAR(" & _
　　　　　　　　Worksheets("Sheet2").Cells(10, 4).Address & "))"
辺りにしてみると動くんじゃないかな、と思いますよ。

例えば、新規ブックのＡ１セルに適当な日付を入力し、
Sub SAMPLE()
　　Cells(1, 2) = "Worksheets(""Sheet1"").Cells(1, 1)"
　　Cells(2, 2) = Worksheets("Sheet1").Cells(1, 1)
　　Cells(3, 2) = Worksheets("Sheet1").Cells(1, 1).Address

　　' 下３行はあえて「＝」を抜いてます。
　　' 必要ならマクロ実行後、セルの編集（手作業）で「＝」を追加してみてください。
　　Cells(1, 3) = "YEAR(Worksheets(""Sheet1"").Cells(1, 1))"
　　Cells(2, 3) = "YEAR(" & Worksheets("Sheet1").Cells(1, 1) & ")"
　　Cells(3, 3) = "YEAR(" & Worksheets("Sheet1").Cells(1, 1).Address & ")"

　　' で、結局
　　' Cells(1, 4) = "=YEAR(Worksheets("Sheet1").Cells(1, 1))"　　' これはコンパイルエラー
　　Cells(2, 4) = "=YEAR(" & Worksheets("Sheet1").Cells(1, 1) & ")"
　　Cells(3, 4) = "=YEAR(" & Worksheets("Sheet1").Cells(1, 1).Address & ")"
End Sub
この違い、使ってご理解くださいませ。
　※各セルに入力された値（文字列）の違いにご注目くださいね。

お礼

回答ありがとうございます。
具体例をやってみて、動作を確認することが出来ました。

※
学習マクロの記載、失礼しました。
R1C1形式は慣れないというのもありますが、変数を使用するので
cellsプロパティでの表現にした次第です。

回答No.3

imogasi

>2017/05/22という書式の日付が入っています
セルの値は、日付シリアル値で入っています。これを十分知らないね。
Ｇｏｏｇｌｅで「エクセル　日付シリアル値」で照会し、適当な記事を読んで勉強すること。ＶＢＡをやる域になっても、このことを知らない人がいるようだ。
年号が欲しい場合は年号を出す表示形式で日付を表示させて（プログラムはＭＳが責任を持つ）
すべての表示形式と関数は日付シリアル値を対象にしている。
そこから年号を知る方法によるべきだ。
また表示形式の設定を適当に選ばないと年号は捉えられない。その年号の範囲を西暦で何年何月何日から何日までと、日付まで調べて、テーブル化して、索引するプログラムは組めますが、素人はこういうことをしてはいけない。関数の場合はＴＥＸＴ、ＶＢＡなら
Sub test01()
MsgBox Format(Cells(1, 1), "gee/mm/dd")
End Sub
のような実行結果の文字列の最初の」１文字のＨ，Ｓ，Ｔ，Ｍなどで判別してはどうか。
例
西暦　　　年号方式
2017/5/22 H29/05/22
1920/2/3 T09/02/03
1901/4/5 M34/04/05
1939/7/7 S14/07/07
自分のコーディングをただすより、エクセルとしての普通の方法の見聞を広めよ。
エクセルＶＢＡなのだから、他の言語プログラムなどを影響受けてはまずいばあいがあるのだ。

お礼

回答ありがとうございます。
日付シリアル値で入っています、は知ってたのですがね。

回答No.2

tsubu-yuki

失礼、１です。
> > Cells(2, 4) = "=YEAR(" & Worksheets("Sheet1").Cells(1, 1) & ")"
なぜ、コレがダメなのかの解説だけ補足しておきますね。

ご存知の通り、一般的に「Cells(1, 1)」と書くと、
ＶＢＥはなんとなく「.Value」を省略したんだろうな、と考えてしまいます。
つまり、内部的に「Cells(1, 1).Value」と補記し、
該当セルの「値」を拾ってきて、そのまま返します。
つまり、冒頭の式で言うと「2017/05/22」と言う値が返ってきます。

一方、ワークシート関数「YEAR」は一般的には
「シリアル値から『年』を取り出す」関数です。
　※シリアル値 ＝ 日付を数値で表したもの
　　なお、2017/5/22 は 42877 です。
今日の日付から「年」を取得したいのであれば
この「42877」あるいは「42877 に置き換えできる日付文字列」
またはそれらが入っている「セル番地」を引数として与えなければいけません。

さて、これらを組み合わせてみます。
Cellsプロパティは「2017/05/22」を返して、YEAR関数に与えます。
つまり「=YEAR(2017/05/22)」を処理しようとします。
で、ちょっと待った、「2017/05/22 = 18.33636・・・（割り算）」だよね。
じゃぁ「＝YEAR(18.33636・・・)　⇒ YEAR("1900/1/18")」じゃん！
ってことは「1900」を返せば良いね！！
・・・というエクセルとの擦れ違いが生じてしまうのです。

それを回避するため、YEAR関数には正しく「該当セル番地」を与えましょ。
　× Cells(2, 4) = "=YEAR(" & Worksheets("Sheet1").Cells(1, 1) & ")"
　〇 Cells(2, 4) = "=YEAR(" & Worksheets("Sheet1").Cells(1, 1).Address & ")"
と、Addressプロパティを使いましょうね。
あるいは、文字列としてYEARに与えるために頑張って加工
　Cells(2, 4) = "=YEAR(""" & Worksheets("Sheet1").Cells(1, 1) & """)"
などでも良いですね。
・・・というお話でした。

忘れがちですが、結構大事なんです。

お礼

回答ありがとうございます。
とても参考になりました。
日付なので、シリアル値が何かしら影響しているのでは？
と思っていたので、何故おかしくなるかの例が分かりやすかったです。