Excel (VBA)

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

 
(Windows 10 Pro : Excel 2016)
マイナス時間を格納して転記したい
投稿日時: 19/06/07 19:35:05
投稿者: めんたん

こんにちは。
 
詳細オプションの1994年から計算オプションを使用しています。
Excel2010のときは問題なく動いていましたが
 
Dim d As Date
d = Rnage("A1").Value 'A1にはマイナスの時間 -1:30:00 が入っている 
Range("B1").value = d 'B1の値は -1:30:00 AM
 
 
Excel2016になるとエラーを吐き、
 
Sub その1()
   Dim d As Date
   d = Rnage("A1").Value
   Range("B1").Value = d '実行時エラー1004 アプリケーション定義またはオブジェクト定義のエラー
End Sub
 
型を変えると一応走ります。
 
Sub その2()
   Dim d As Variant
   d = Rnage("A1").Value
   Range("B1").Value = d 'エラー起こさず。ただしセルの値は -0.062497
End Sub
 
マイナスの時間を格納できなくなったのか思い調べてみたけど、変数に格納する段階では
エラーを起こしていないので、セルの書き込み時のルールが変わったようですがよく分からず。。
 
正しい書き込み方法を教えてくださいm(_ _)m

回答
投稿日時: 19/06/08 10:07:15
投稿者: mattuwan44

>'A1にはマイナスの時間 -1:30:00 が入っている
 
と言われてますが、入れているのは数式ですか?
単純に入力しても、
「入力した数式は間違っています。」とエクセル君に叱られました。
文字列で入力したら入力は出来ますが、あくまで日付型の値として扱いたいのですよね?
 
多分、計算の結果が、「-1:30:00」と見た目表示されているとして、
この時セルの値としては、-0.062497となります。
これを、「セルの表示形式」の設定で、「時刻」にしているので、
「-1:30:00」と表示されているのです。
 
2016が無いので、想像になりますが、
時間は内部で保持する値は24時間が1になりますので、
基本的に小数になります。
なので、Value2プロパティに対して読み取りや書き込みをしてはいかがでしょうか?
当然、表示はセルの書式設定で時刻に直します。(事前に設定しておいてもいいし、マクロで直すのもありかとは思います。
 

Sub test()
    Dim d As Double
    d = Range("A1").Value2 'A1にはマイナスの時間 -1:30:00 が入っている
    Range("B1").Value2 = d
End Sub

 
VBAのヘルプより抜粋 さんの引用:
Excel 開発者用リファレンス
Range.Value2 プロパティ
セルの値を設定します。値の取得および設定が可能です。バリアント型 (Variant) の値を使用します。
構文
 
式.Value2
 
式 Range オブジェクトを表す変数です。
 
備考
 
 
Value2 プロパティでは、通貨型 (Currency) および日付型 (Date) のデータ型を使用しない点のみが、Value プロパティと異なります。倍精度浮動小数点型 (Double) を使用することにより、これらの 2 種類のデータ型の値を浮動小数点数として返すことができます。

回答
投稿日時: 19/06/08 10:17:58
投稿者: simple

回答ではありません。質問者さんに追いつくための補足情報です。

引用:
詳細オプションの1994年から計算オプションを使用しています。

1994年じゃなく、1904年基準ですね。
私は使ったことなかったですが、このオプションを使うと、
負の時間表示ができる(できた、が正確ですか。2010では少なくともできます)。
 
https://www.helpforest.com/excel/1_book/ex010049.htm
によると以下のようなことのようです。
引用:
では、そもそもこの1900年と1904年という設定がなぜ存在するかというと、
次のようなことがExcel 誕生当時にあったようです。
 
1. もともと Excel は Mac版 が最初で1904年起点だった
2. 当時 Lotus1-2-3 という表計算ソフトが優位だった
3. Win版Excel は、Lotus に合わせた
そのほか、1900年設定のおもしろ現象として、「1900/2/29」という日付、
1900年はうるう年ではないのですが、このない日が存在したり、
数値0のセルを日付表示にすると「1900/1/0」というこれまた存在しない日付にななったりと、
これらは Lotus のバグです。(一度入力して確かめてみてください。)
このバグは Lotus との互換性を保つための必然的に引き継がれ、
1900年と1904年の設定は、Win版とMac版との互換性を保つための必要な設定ということなのです。
 
最後に、Macでの1904年の設定があるのは、負の時刻を扱いたかったからではなく、
うるう年の算出のためなのです。
1904年から2069年までを扱うならば、うるう年が4年に1度の法則が成り立つのだそうです。
 
そしてMac版 Excel2011 以降、既定が1900年となっており、互換性が保たれています。
最後の下りからすると、
想像するに、Mac版も1900年基準になったこともあって、1904基準への力の入れようが
低下したのかも知れない。そのことが動作の変更になってよい理由にはならないと思いますが。
 
どのような回避策があるのか、皆さんからのコメントを待ちたいと思います。
私は、-1:30:00 という入力すらできず沈没でしたww (Excel10を使用)
 
# 時間は逆向きには流れないから、突然負の時刻が必要になるわけはなく、
# ふたつの時刻の差として出てくる話なんでしょう。
# それなら、差を正負ふたつでわけて、変数?を2つ持てばいいんじゃないか、
# などと思ってみたり。
# もしくは、中間の項目は実数でもって、最終結果の表示を工夫すればよいのかも。

回答
投稿日時: 19/06/08 13:34:02
投稿者: WinArrow
投稿者のウェブサイトに移動

simlpeさん、面白い話をありがとうございます。
 
マイナス時間、1900/2/29の件は、以前、勉強したことがあります。
 
私が、会社で大型コンピュータ関連の仕事をしていた時は、
基準となる「年」は、西暦1年です。
そこからうるう年を計算してカレンダーを作っていました。
遊びではなく、リードタイムを計算するためのカレンダーです。
事業所や分工場ごとに休みが違うので、お互い(10個くらい)のカレンダーを持ち、
発注側、発注先、どちらかが休みの日を除いて納期を決めるのが、結構大変だった記憶があります。
 
余談でしたが、
 
マイナス時間は、本来、「時刻」ではなく「時間」なんでしょうね?
私見ですが、「時刻」のマイナスはNG、だけど、「時間」のマイナスはOK
という感じで考えていますが、Excel上で、「時刻」と「時間」を区別することは難しい。
「時間」という概念ならば、合計することを考えれば、数値セルと表示セルを分けた方が運用しやすいと思います。
 
 

=TEXT(ABS(A1),IF(A1<0,"-hh:mm","hh:mm"))
 
 

回答
投稿日時: 19/06/08 13:35:06
投稿者: 半平太

1904年オプションは、ミスの元だと思っているので使ったことはなく、
何か語れる人間じゃないですけど・・
 
Solo365 でやってみると同じエラーになりますね。
XL 2013 でも同じエラーになりました。
実行時エラーに変更になったなんて冗談じゃないですね。
 
XL2010でやると、確かにエラーにはならないです。
 
けど、これって、文字列ですよね? しかもAMなんて付いています。
    ↓
> -1:30:00 AM
 
私の勝手な思い込みでは、
 数値データじゃないのは致命的と感じます。エラーじゃなくても使えない。
  また、時間なら「AM」は不要だし、時刻なら「1903/12/31 22:30」であるべきとも思うのですが。
 
そうなると、こんな2段階処理にするかも知れないです。(やるとすればですけど)
       ↓
Sub TEST()
   Dim d As Date
   d = Range("A1").Value
   Range("B1").Value2 = d
   Range("B1").NumberFormat = Range("A1").NumberFormat
End Sub

回答
投稿日時: 19/06/08 14:31:32
投稿者: WinArrow
投稿者のウェブサイトに移動

余談ですが、
1904年オプションは、ブック単位に設定するものですが、
  
1900年ベースで運用しているブックをある日から1904年に切替する場合、
  
ご存知とは思いますが、
 既に入力されている日付は、全部、再入力、しないと使えません。
  
ブック単位だからといって、マイナス時間のために特定ブックだけ1904年オプションを使う
 と、(後継者を含めて)混乱すると思います。
  
すくなくとも「業務」または、「事業所」の運用ルールとして
(マイナス時間の運用も含めて)決める必要があると思います。
  
  

投稿日時: 19/06/08 23:40:06
投稿者: めんたん

こんばんわ。
皆様返信ありがとうございます!
 
まずは、1904年オプションでした。。
 
で、マイナス時間は数式の結果として表示されています。
会社で使う労働時間管理のブックなんですわ。
 
出社 8:30
退社 17:30
休憩 1:00
労働時間 8:00
 
みたいな表示をしてるので、8時間労働に満たない場合にマイナスの時間が
表示される→早退とか時間休取ってるのが分かる、とまぁそんなブックです。
そんな訳でこのブックに限りマイナス時間が取り扱えないとかえって困るのです。
 
家のPCはいまだにExcel2003なので週明けValue2プロパティでの記入を試してみますー
 
-1:30:00 AM は確かに文字列なんですよね。最終的にはさらに数式で処理して
▲1:30 なんて表示しています。
 
今回のエラーが出てから分かったんですけど、別にVariantで扱っておいて -0.062497と
ちゃんとした?データとして記入してもブックの挙動には影響はなかったっぽいです。
ただ、時刻扱うのにVariant型で変数に入れるのがなんとなくイヤなだけで^^;
 
そんなことで正しいマイナス時間の扱い方を知りたかったんです。

回答
投稿日時: 19/06/09 13:19:12
投稿者: WinArrow
投稿者のウェブサイトに移動

いわゆる「時刻」ではなく「時間」と考えて、
「値」を数値で持ち、別セルでマイナス表示する
>=TEXT(ABS(A1),IF(A1<0,"-hh:mm","hh:mm"))
こんな対応でよければ
敢えて、1904オプションにこだわる必要ないと思いますが・・・・
 
 

回答
投稿日時: 19/06/09 15:17:13
投稿者: WinArrow
投稿者のウェブサイトに移動

前レスの文章訂正します。
 

引用:
1900年ベースで運用しているブックをある日から1904年に切替する場合、
   
ご存知とは思いますが、
既に入力されている日付は、全部、再入力、しないと使えません。

 
と書きましたが、実際に日付ではなく、「時間」で運用している限り
再入力する必要はないと思います。
  

投稿日時: 19/06/10 11:17:34
投稿者: めんたん

こんにちは。
 
Value2プロパティを使ってDate型のままでいけました〜
 
WinArrowさんの書かれている1904オプションを使わない方向での検討ですが、
もう2010年から使ってるブックなので、今から数式やら触りたくないなぁーという
ナマケゴコロが出まして、ちょいちょいとプロパティいじって逃げようかと思います^^;
 
それにしても毎回オフィスのバージョンが変わるときはいらぬ苦労がありますね。
前使えていたものをエラーにしなくてもいいでしょうにー
 
皆様ありがとうございました!