ExcelのVBAについて。

質問

ExcelのVBAについて。

画像のようなコードを利用してセルに入力時の日付時間を自動的に付加させたいのですが、、

VBA初心者な為に上手くいきません。

コード自体はサイトのを切り貼りして使えるのですが、これ以上の応用例に行き着けません。目的としては、データベース的に、あと入力数値から特定文字を抜き出して変数を当て嵌め文字列の語呂を当てます。様式はまだ作成中ですが、ある種の図表化してプリントアウトまでしたいと思っています。

何が上手くいかないか、まず自動的に付加させる所は何となく出来ているので、これをシート1とした場合に、シート2に図表化した転記内容を構成し、、シート1入力、シート2表示、またシート1A3セルに戻るというコードにしたいです。

この記述は不味いとかこうこうああした方が良いというアドバイスや指摘をお願いします。

よろしくお願いします。

回答No.5

kkkkkm · ベストアンサー

> 教えてくれてるのになんですが、

いえ、変だと思ったらつっこんでください。教えているというほどの事はしていないと思いますので気にしないでください。

> "5"の所はそもそも文字列扱い(セル書式で先に変更)にしようが、宣言なので5は文字列扱いにする(何を言いたいかだけ、実際上できるできない？)かとか、別問題です。

宣言ではないと思いますが…。文字列扱いにしたければそれでいいですよ。

ただ
str_Left = Left(Cells(4, 5), Cells(4, 8))
で第2引数がH列を参照してます。第2引数は文字数なので数値の方が違和感がないと思います。
Left(string, length)
length 必須。Variant (Long)。
のせいなのか文字でもエラーにはならないみたいですが。

お礼

限りなく間違っている訳ですが、E4あたりもそうです。

散々ですが、ありがとうございます^^

補足

こういう場合もあり得るようです。。
参考までに。
http://mt-soft.sakura.ne.jp/kyozai/excel_vba/300_vba_kiso/30_autoDate/index.html

画像のコード(再掲)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim time7 As Range
For Each time7 In Target
If time7.Column = 1 Then
time7.Offset(0, 4).Value = Format(Now, "Short Time") & vbCrLf & _
Format(Now, "yyyy/mm/dd hh:nn:ss AM/PM")
End If
Next time7
Application.EnableEvents = True
'

End Sub

Private Sub Worksheet_Activate()
Application.Goto ActiveSheet.Range("A3"), True
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Range("H3").Select
ActiveCell.FormulaR1C1 = "5"
Range("E3").Select
Selection.ClearContents

Dim str_Left As String

'セルE2に文字列、セルH2に数字を予め入れておくこと。
str_Left = Left(Cells(4, 5), Cells(4, 8))

MsgBox str_Left & vbCrLf & " " & "OKボタンを押してください!"

Range("A3").Select

End Sub

回答No.7

nishi6

添付図を見ての感想ですが、まず、インデントをちゃんと付け、処理の切り替わりは空白行を入れるとかして、見やすくすべきです。

Worksheet_Changeイベントの中にシート2（Sheet2）に何かをする行が見当たりません。「time7.Offset(0, 4).Value =・・・」がSheet2に対する処理ではありませんよね。
これが回答を考える上で、一番の疑問点です。「Application.EnableEvents = True」も不思議で、元々Trueのはずで、意味がないでしょう。「time7.Offset(0, 4).Value」がSheet1に書き込んでいるのであれば、Worksheet_ChangeイベントがWorksheet_Changeイベントの中で呼び出されます。かなり危ない動きになります。最初に「Application.EnableEvents = False」を書くべきです。

Sheet1では、セルA3への変更が処理の起点になっているように思えますが、「For Each time7 In Target」となっているため、複数セルを選択して変更すると、（Targetは複数セルになり）複数セルの中のA列がChangeイベントを呼び続け、スタックオーバーフローを起こしかねません。

Worksheet_Activateイベントの中で、セルH3、セルE3を更新していますが、その都度Worksheet_Changeイベントが実行されます。状況によってはスタックオーバーフローなどでフリーズします。１行挿入しているようですが、これもChangeイベントを呼び出します。「Application.EnableEvents = False」と「Application.EnableEvents = True」をこのイベントの最初と最後に書くべきです。
str_Left = Left(Cells(4, 5), Cells(4, 8)) もよく分かりません。コメントと関連が切れたのでしょうか。よくあります。

上記は、Sheet1でChangeイベントを起こし、手動でSheet2をアクティブにし、手動でSheet1をアクティブにして気が付いた問題点です。

また、コーディングの中にセル番地やOffsetの引数が「実際の値」として書かれています（Offset(0, 4)、H3、E3、Cells(4, 5)、Cells(4, 8)など）。このような書き方は、マジックナンバーと言います。コーディングが完成に近づくほど、変更が面倒になります。シートの方で１行、１列の挿入・削除で誤作動したり、動かなくなったりします。マジックナンバーはなるべく使わない方がいいでしょう。回避方法としては、セルに名前を付けるのが簡単です。セルの挿入や削除に強いコードになります。

２つのシート間で入力、出力を行いたいらしいので、そのヒントですが、
　Dim ws1 As Worksheet　　　'// Sheet1
　Dim ws2 As Worksheet　　　'// Sheet2
　Set ws1 = Worksheets("Sheet1")
　Set ws2 = Worksheets("Sheet2")
のようにセットし、RangeやCellsには明示的にws1やws2を付けることでシート間の移動が不要になったりします。実際、Worksheet_Activateイベントはほとんど使ったことがありません。（本当は使ったことがありません）
例えば、Changeイベントの「time7.Offset(0, 4).Value = Format(…」をSheet2の同じセルに書きたいときは、「ws2.Range(time7.Address).Offset(0, 4).Value = Format(…」とします。読んでみると「Sheet2の、セルtime7と同じ番地のセルから右に4つ動いた同じ行に右辺を書く」となります。至極当然の書き方です。

コードを書いているのはSheet1のコードウインドウなので、操作するセルの住所（シート：WorkSheetとセル座標：Range）を明確にセットし記述するようにすれば、Sheet2に書いて→Sheet1で処理をして…→…　がActivateイベントなしで書けるはずです。

当方、Win10、Excel2010です。ご参考に。

お礼

助言ありがとうございます。マジックナンバーの点ですが、試用中ですらややこしく、最初の頃は間違って使っていました。今も他のアドレスとで違和感があります。できれば何れかという1つを使いたいですが、、まだまだ勉強中です^^
メッセージボックスですが、作った本人は分かりますが、間違いはないだろうが、動きをつけてます。どこを自動的にして手動はどのあたりにするか？

今日はこれにて。ありがとうございます^^

回答No.6

HohoPapa

横から失礼します。
次のような動作になることを期待していますでしょうか？

01)操作者がSheet1を選択されたらVBAがSheet1の"A3"を選択する。
02)操作者が外部入力装置を操作すると
　　Sheet1の"A3"に読み取ったデータ(※１）が埋まる。
03)埋まったら、そのイベントを使いVBAが次の処理を行う
04)Sheet2の3行目に行挿入する。
05)※１の値と実行日時を文字結合する。（※２）
06)Sheet2の"A3"に※２を埋める
07)Sheet1の"A3"を選択
08)Sheet1の"A3"を空欄にする。
以下、02)の操作を待つ

つまり、
外部入力装置で得たデータを加工し
次々とSheet2に追記していく動作です。
（行の下方向に追記するのではなく、
　3行目に順次挿入する追記です）

そうであれば、次のようなコードとなります。

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

　Dim GetData As String

　If Target = Me.Range("A3") Then
　　GetData = Target.Text
　　With Sheets("Sheet2")
　　　.Rows(3).Insert _
　　　　Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
　　　.Range("A3").Value = GetData & vbCrLf & _
　　　　Format(Now, "yyyy/mm/dd hh:nn:ss AM/PM")
　　End With
　End If

　Me.Range("A3").Select
　Application.EnableEvents = False
　Me.Range("A3") = ""
　Application.EnableEvents = True

End Sub

Private Sub Worksheet_Activate()
　Me.Range("A3").Select
End Sub

お礼

回答ありがとうございます。型としてはかなり近いのですが、、やはりモヤモヤした質問、完成形としてそれが逸れた原因か、、。もう少し勉強してみて、ああだこうだしてみてみます。ありがとうございます^^

回答No.4

kkkkkm

> ソフトウェアとしてはわりとポピュラーではないかと思います。

なるほど、勉強不足なのでそのようなソフトウェアを知りません。
文章の羅列を文書にする感じで一覧情報にして、シート1が数字のみならシート2は漢字とかの文字。
にするような、たとえばポピュラーなソフトウェアとはどれでしょう。

> 入力(シート1)→シート移動(シート2)→入力(シート1)…の手順には遠いような気がします。

入力(シート1)
Sheets("シート1").Range("入力したいセル番地").Value=入力したいデータ

シート移動(シート2)
Sheets("シート2").Activate

入力(シート1)
Sheets("シート1").Activate
Sheets("シート1").Range("A3").Value=入力したいデータ

としかいいようがありません。
入力時の日付時間を自動的に付加する以外具体的な説明がないので。
> 入力数値から特定文字を抜き出して変数を当て嵌め文字列の語呂を当てます。
具体的ではないですよね。

> GoTo文はあまり好ましくない言いがありますが

それは今回のGotoとは違う話だと思われます。

なにかの判定結果などで
GoTo dokoka
が実行されて
dokoka:
に飛んでいくコード

補足

GoTo文のお話はかなり古いプログラミングの様で、、自分もコンピューターの登竜門的時代にたまたま耳にしただけです。関係はなくもないと思います。面白さと、よりコンパクトにスムーズには、、違いますよね。

キーボードはコードがあると思います。それが文字コード文字自体のように表示され、、きりがない。要は普通に使ってる。

進展があれば、再質も検討してみます。

回答No.3

kkkkkm

> シート1の入力(数値になるか文字列になるかまだ決めていません。)を様式を決めて、文章の羅列を文書にする感じで一覧情報にします。
> シート1が数字のみならシート2は漢字とかの文字。
> わりとポピュラーな形式だと思いますが、

どの世界でポピュラーなのか知りません
広辞苑第五版
ポピュラー【popular】
　一般向き。大衆的。また、普及しているさま。
　広く親しまれる洋風の歌や音楽。「―‐ミュージック」「―‐ソング」　クラシック

結局何をやりたいのか分かりませんが

Application.EnableEvents = False
が先頭にないのに
Application.EnableEvents = True
があっても無意味だと思います。

Application.Goto ActiveSheet.Range("A3"), True
Gotoはアクティブではないシートのセルを選択する場合に使うみたいですが、ActiveSheetに指定してるのはなぜでしょう。スクロール的なもの？
ActiveSheet.Range("A3").Select
との違いはスクロールだけのように思えます。

Range("H3").Select
ActiveCell.FormulaR1C1 = "5"

Formulaはセルに数式をセットする場合に使います。値ならValueで
以下のように一行ですませます。"5"のように文字で指定してもエクセルは数値として扱います。
Range("H3").Value = 5

Range("E3").Select
Selection.ClearContents
は
Range("E3").ClearContents
一行で

お礼

GoTo文はあまり好ましくない言いがありますが、確かにエラーが多い書きのこの記述に先の文は好ましくないとは思います。改善出来るようならば、適切な文に置き換えたいです。。

補足

Excelの範疇であるかは差し置いて、ソフトウェアとしてはわりとポピュラーではないかと思います。

初心者として、使えるコードは使う。その上で重大な問題があるならば、初心者としても直すのは当然です。

今の記述だと一文で修正できる範囲かも知れませんが、おっしゃいの点は教えて欲しい部分にはあまり関係ないのではないかと思います。

十分に確認して、修正したいとは思いますが、入力(シート1)→シート移動(シート2)→入力(シート1)…の手順には遠いような気がします。

もしくは、シート移動せずに、、記述がより難解に高度になりそうな。。

今さらかも知れませんが、言葉足らずは差し置いて、しかし手順は最低限必要と思います。

ご協力お願いします。。

回答No.2

kkkkkm

> 外部入力装置→Excelに入力(シート1)→シート2に表示形式

外部入力装置とは？そこからの入力をVBAで制御するのですか？
シート2に表示形式？シート1の表示形式をシート2に移すんですか？
Excel 表示形式とは
https://www.jpita.or.jp/simin/index.php?excelziten4

説明不足も何も…
> シート1入力、シート2表示、またシート1A3セルに戻るという
> 外部入力装置→Excelに入力(シート1)→シート2に表示形式
ほぼ同じこと言ってますが…。

転記
転記先=転記元
A1にA2のデータを転記
Range("A1").value=Range("A2").value
変数
変数=変数にセットしたい値(セルの値など)
変数hensuにA1の値をセット
hensu=Range("A1").value

> サイトのの借り物ですから

引用したサイトを明記するとか、そのままならリンク貼ればいいのでは。
改行がなぜないのでしょう。

Private Sub Worksheet_Activate()
↑のコードと今回の質問とどのような関係があるのでしょう。

お礼

教えてくれてるのになんですが、、"5"の所はそもそも文字列扱い(セル書式で先に変更)にしようが、宣言なので5は文字列扱いにする(何を言いたいかだけ、実際上できるできない？)かとか、別問題です。しかし、見落としやしらなさを忠告してくださりありがとうございます。

極力焦点や的が合うようにお願いします。

補足

外部入力装置の制御は製品が対応しており問題はないかと思います。外部入力装置はキーボードとバーコードリーダーにしています。

シート1の入力(数値になるか文字列になるかまだ決めていません。)を様式を決めて、文章の羅列を文書にする感じで一覧情報にします。

シート1が数字のみならシート2は漢字とかの文字。

わりとポピュラーな形式だと思いますが、プログラミングするとなると大変で、、

そのままリンクできるサイトのがあれば、既に完成してますよ^^;

Excelで可能ならば入出力が半自動からほぼ自動的に完了したいと思っています。

語弊がありますが、シート2から戻るのに手動してるのが今のコードの記述です。

回答No.1

kkkkkm

シート2に何を転記したいのか不明ですが入力時間なら
time7.offset(0,4).value=Format(Now,以下略
のところ
time7.offset(0,4).value
を
シート2の適当なセルを指定して
Sheets("シート1").Activate
Sheets("シート1").Range("A3").Select
でシート1A3セルに戻ればいいと思いますが。

コードはわざわざ画像にしなくてそのまま質問などにコピペしたほうが見やすい。

補足

「外部入力装置→Excelに入力(シート1)→シート2に表示形式」という順に入力やさらには転記や変数などを用いたいのですが、説明不足でした。

※サイトのの借り物ですから、あまり載せたくないのですが、以下は画像のシート1のコードです。

Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim time7 As Range For Each time7 In Target If time7.Column = 1 Then time7.Offset(0, 4).Value = Format(Now, "Short Time") & vbCrLf & _ Format(Now, "yyyy/mm/dd hh:nn:ss AM/PM") End If Next time7 Application.EnableEvents = True ' End Sub Private Sub Worksheet_Activate() Application.Goto ActiveSheet.Range("A3"), True Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove Range("H3").Select ActiveCell.FormulaR1C1 = "5" Range("E3").Select Selection.ClearContents Dim str_Left As String 'セルE2に文字列、セルH2に数字を予め入れておくこと。 str_Left = Left(Cells(4, 5), Cells(4, 8)) MsgBox str_Left & vbCrLf & " " & "OKボタンを押してください!" Range("A3").Select End Sub