Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
225 / 3841 ﾍﾟｰｼﾞ ←次へ　｜　前へ→
【77962】Re:勤怠システムを自作したい
発言  ichinose  - 16/2/21(日) 11:15 -
引用なし
パスワード
   ▼作りたい初心者 さん：
>で、現時点ではリアルタイムな時刻表示をするにはどうしたらいいのか迷っています。

VBAの関数　Now関数または、ワークシート関数のNow()関数（VBA内では、[Now()]と記述)

この二つぐらいの投稿で十分だと思いますが、ちょっと私的新発見があったので
投稿します。


新規ブックにて

ユーザーフォームを作成してください（UserForm1）。

このUserForm1には、　ラベル(Label1）とコマンドボタン（CommandButton1）を
一つずつ配置してください。


UserForm1のモジュール


Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private 表示 As Boolean
'==============================================================
Private Sub CommandButton1_Click()
　　表示 = Not 表示
　　If 表示 Then
　　　　CommandButton1.Caption = "時刻表示中止"
　　　　dspclock
　　Else
　　　　CommandButton1.Caption = "時刻表示再開"
　　End If
End Sub
'================================================================
Sub dspclock()
　　Do While 表示
　　　 Label1.Caption = Now()
　　　 DoEvents
　　Loop
End Sub
'===============================================================
Private Sub UserForm_Activate()
　　CommandButton1 = True
End Sub
'=================================================================
Private Sub UserForm_Terminate()
　　表示 = False
End Sub


標準モジュールに

Sub test()
　　UserForm1.Show
End Sub

これでtestを実行して試してみてください


時計表示がされると思います。
このプロフラムに近い物を理由は不明ですが、10年以上前に作った覚えがあります。
Win2000+Excel2000か2002だったと思います。


私的新発見とは・・・。

時刻をラベルに表示し、あたかも時計が動いているように見せるためにラベルに時刻を表示し続けるのがポイントだという事はちょっとVBAを学べはわかります。

プロシジャーで言うと、

Sub dspclock()
　　Do While 表示
　　　 Label1.Caption = Now()
　　　 DoEvents
　　Loop
End Sub

これなんですが、

この中で　Doevents　という記述がありますが、これ入れないとUserForm1は、真っ白のままで何も表示されない状態になってしまいます。

そこでワンクッションのつもりでDoeventsをはさむと見事にデジタル時計が表示される
のですが・・・・。

Excel2000Or2002の時、作ったものだと　Doeventsだけを入れてループさせると
CPUの占有率が100%になってしまう現象が見られました。

時計を表示させるぐらいで100%ではやばい　ということでAPIのSleepをDoeventsと
一緒に使う事で占有率は、まあいいだろうという程度に下がりました。
この時の経験等で　DoeventsとSleepは、セットで使う癖が今でも付いています。

今回の質問で投稿した内容、Doeventsだけでも
CPU占有率は25%程度だったことが私的発見でした。

10年前に作成したストップウォッチでも同様の結果でした。
Win7+Excel2010で試した結果です。


因みに


Sub dspclock()
　　Do While 表示
　　　 Label1.Caption = Now()
　　　 DoEvents
　　　 Sleep 50
　　Loop
End Sub

なんてすると、占有率は、0%〜2%程度でした。


βさんのコード時計表示中に閉じるボタンクリックでコードが終わらない可能性がありますよ！！
・ツリー全体表示
【77961】Re:勤怠システムを自作したい
お礼  作りたい初心者  - 16/2/21(日) 11:04 -
引用なし
パスワード
   ▼β さん：
迅速に教えてくださりありがとうございます、サンプル実行したところ動作しました、ここまで分かれば自分なりにアレンジして使えそうです。
おそらく次はデータベース関係でご質問させていただくことになりそうですが、その際はまたよろしくお願い致します。
・ツリー全体表示
【77960】Re:勤怠システムを自作したい
発言  β  - 16/2/21(日) 8:30 -
引用なし
パスワード
   ▼作りたい初心者 さん：

１つの方法のサンプルとして。
ユーザーフォームに　Label1 と CommandButton1 を配置してください。

●ユーザーフォームモジュール


Private Sub CommandButton1_Click()
　　DoLoop = Not DoLoop
　　If DoLoop Then
　　　　CommandButton1.Caption = "時刻表示中止"
　　　　Application.OnTime Now(), "今何時"
　　Else
　　　　CommandButton1.Caption = "時刻表示再開"
　　End If
End Sub

Private Sub UserForm_Initialize()
　　CommandButton1.Caption = "時刻表示中止"
　　DoLoop = True
　　Application.OnTime Now(), "今何時"
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
　　DoLoop = False
End Sub

●標準モジュール

Public DoLoop As Boolean

Sub 今何時()
　　
　　Do While DoLoop
　　　　UserForm1.Label1.Caption = Now
　　　　DoEvents
　　Loop
　　
　　UserForm1.Label1.Caption = "時刻表示お休み中"
　　
End Sub
・ツリー全体表示
【77959】勤怠システムを自作したい
質問  作りたい初心者  - 16/2/20(土) 23:11 -
引用なし
パスワード
   今勤怠システムをつくるためにエクセルVBAのユーザーフォームを勉強しています。
名前をコンボボックスで選択し、オプションボタンで操作を指定、リアルタイムな時間表示を行い、操作ボタンをクリックするとその時刻で打刻されるエクセルシートにはデータを格納していくというプログラムを作りたいと考えています。

で、現時点ではリアルタイムな時刻表示をするにはどうしたらいいのか迷っています。

アイディアを頂きたく存じます。
・ツリー全体表示
【77958】Re:重複処理、合計計算
お礼  VBA初心者  - 16/2/17(水) 14:59 -
引用なし
パスワード
   ▼β さん：
>▼VBA初心者 さん：
>
>Ａ列、Ｂ列のデータ処理はアップしたコードでＯＫですよね。
>ただ、そこで作業列に使ったＤ列には、他のデータがありそうですから
>コード内でＤ列を使っているところをたとえば　Ｘ列に、Ｅ列を使っているところをＹ列に変更しましょう。
>
>で、今度は、Ｄ列、Ｅ列に対して同じことをしたいのですよね。
>であれば、まずは、Ａ，Ｂ列を処理しているコードをコピーして、
>このＡにあたるところをＤに、ＢにあたるところをＥに直せば
>処理できますよね？
>
>もちろん、同じような処理が２つ書かれるわけで冗長なコードですけど
>まずは、そのようにして対処されてはいかがでしょう。
>
>その後、もし、同じようなこの2つの処理をループで回す1つのコードにまとめたいということがあれば
>それはそれで、次のテーマとして対応されたらよろしいかと思います。
>
>まずは自分でやってみましょう。

ありがとうございます。　自分の為にも頑張ってみます。m−ーm
・ツリー全体表示
【77957】Re:重複処理、合計計算
発言  β  - 16/2/17(水) 14:41 -
引用なし
パスワード
   ▼VBA初心者 さん：

Ａ列、Ｂ列のデータ処理はアップしたコードでＯＫですよね。
ただ、そこで作業列に使ったＤ列には、他のデータがありそうですから
コード内でＤ列を使っているところをたとえば　Ｘ列に、Ｅ列を使っているところをＹ列に変更しましょう。

で、今度は、Ｄ列、Ｅ列に対して同じことをしたいのですよね。
であれば、まずは、Ａ，Ｂ列を処理しているコードをコピーして、
このＡにあたるところをＤに、ＢにあたるところをＥに直せば
処理できますよね？

もちろん、同じような処理が２つ書かれるわけで冗長なコードですけど
まずは、そのようにして対処されてはいかがでしょう。

その後、もし、同じようなこの2つの処理をループで回す1つのコードにまとめたいということがあれば
それはそれで、次のテーマとして対応されたらよろしいかと思います。

まずは自分でやってみましょう。
・ツリー全体表示
【77956】Re:重複処理、合計計算
発言  VBA初心者  - 16/2/17(水) 13:09 -
引用なし
パスワード
   ▼β さん：
>▼VBA初心者 さん：
> 再掲します。
>
> １．Ａ列を　Ｄ列にコピペ
>２．Ｄ列を選択して　データタブ　重複の削除（先頭がタイトル行にチェック）
> 　　アップされた例なら D1:D7 に縮まります。
> ３．Ｅ１にＢ１のタイトル項目をコピー
>３．Ｅ２：Ｅ７を選択し　　=SUMIF(A:A,D2,B:B)　これをタイプして　Ctrl/Enter。
> ４．Ｄ：Ｅ列を選択して　Ctrl/c 。そのまま　形式を指定して【値】貼り付け。
> ５．そのまま　Ctrl/x。
> ６．Ａ１を選択して Ctrl/v。
>
>
>この操作をマクロ記録し、それを(かなり）お化粧直ししたのが以下のコードです。
>
>Sub Sample()
>　　Columns("A").Copy Range("D1")
>　　Columns("D").RemoveDuplicates Columns:=1, Header:=xlNo
>　　Range("E1").Value = Range("B1").Value
>　　With Range("D2", Range("D" & Rows.Count).End(xlUp))
>　　　　.Offset(, 1).Formula = "=SUMIF(A:A,D2,B:B)"
>　　　　.Offset(, 1).Value = .Offset(, 1).Value
>　　End With
>　　Columns("D:E").Cut Range("A1")
> End Sub
>
>

早速の回答ありがとうございます。せっかくコードを教えてくださったのに最初の条件の追加でもうしわけございませんが、DとEコラムの重複も同時に無くしたいのですが、ご教授おねがいしてもよろしいでしょうか？DとEコラムも、Aコラムと同じ状態で両方ともRow2から始まります。
・ツリー全体表示
【77955】Re:重複処理、合計計算
発言  β  - 16/2/17(水) 8:15 -
引用なし
パスワード
   ▼VBA初心者 さん：
再掲します。

１．Ａ列を　Ｄ列にコピペ
２．Ｄ列を選択して　データタブ　重複の削除（先頭がタイトル行にチェック）
　　アップされた例なら D1:D7 に縮まります。
３．Ｅ１にＢ１のタイトル項目をコピー
３．Ｅ２：Ｅ７を選択し　　=SUMIF(A:A,D2,B:B)　これをタイプして　Ctrl/Enter。
４．Ｄ：Ｅ列を選択して　Ctrl/c 。そのまま　形式を指定して【値】貼り付け。
５．そのまま　Ctrl/x。
６．Ａ１を選択して Ctrl/v。


この操作をマクロ記録し、それを(かなり）お化粧直ししたのが以下のコードです。

Sub Sample()
　　Columns("A").Copy Range("D1")
　　Columns("D").RemoveDuplicates Columns:=1, Header:=xlNo
　　Range("E1").Value = Range("B1").Value
　　With Range("D2", Range("D" & Rows.Count).End(xlUp))
　　　　.Offset(, 1).Formula = "=SUMIF(A:A,D2,B:B)"
　　　　.Offset(, 1).Value = .Offset(, 1).Value
　　End With
　　Columns("D:E").Cut Range("A1")
End Sub
・ツリー全体表示
【77954】Re:重複処理、合計計算
発言  β  - 16/2/17(水) 8:09 -
引用なし
パスワード
   ▼VBA初心者 さん：

↑　1行目からデータだと勘違いした手順とコードでした。
後ほど、2行目からのものに対する手順とコードを再掲します。
・ツリー全体表示
【77953】Re:重複処理、合計計算
発言  β  - 16/2/17(水) 6:34 -
引用なし
パスワード
   ▼VBA初心者 さん：

トピの目的が、ループ処理の構文を身につけたい、勉強したいということなのか
このテーマを、自分でもわかる方法で実行したいのか、それがわかりませんが
後者で。

１．Ａ列を　Ｄ列にコピペ
２．Ｄ列を選択して　データタブ　重複の削除 アップされた例なら D1:D6 に縮まります。
３．Ｅ１：Ｅ６を選択し　　=SUMIF(A:A,D1,B:B)　これをタイプして　Alt/Enter。
４．Ｄ：Ｅ列を選択して　Ctrl/c 。そのまま　形式を指定して【値】貼り付け。
５．そのまま　Ctrl/x。
６．Ａ１を選択して Ctrl/v。

この操作をマクロ記録し、それを(かなり）お化粧直ししたのが以下のコードです。

Sub Sample()
　　Columns("A").Copy Range("D1")
　　Columns("D").RemoveDuplicates Columns:=1, Header:=xlNo
　　With Range("D1", Range("D" & Rows.Count).End(xlUp))
　　　　.Offset(, 1).Formula = "=SUMIF(A:A,D1,B:B)"
　　　　.Offset(, 1).Value = .Offset(, 1).Value
　　End With
　　Columns("D:E").Cut Range("A1")
End Sub
・ツリー全体表示
【77952】重複処理、合計計算
質問  VBA初心者  - 16/2/17(水) 4:18 -
引用なし
パスワード
   質問させてもらいます。宜しくお願いします。

Aコラムに名前が入力されてあり（名前の数、名前は変化するとします。）、そしてBコラムに時間（時間、時間が入力してある行も変化するとします）が入力されてあるとします。

例

Aカラム　　Bカラム
（名前と数字はそれぞれA2、B２から始まるとします）
佐藤　　　　１２
田熊　　　　３５
伊藤　　　　２３
本田　　　　８７
豊田　　　　２４
松田　　　　２３
松田　　　　１１
伊藤　　　　３４

このシート１に入力された情報を名前の重複を無くし、またそれと一緒に時間をそれぞれの名前にカウントしたいのです。下記が処理後にシート１に表示させたい情報です。

例
Aカラム　　Bカラム
（名前と数字はそれぞれA2、B２から始まるとします）
佐藤　　　１２
田熊　　　３５
伊藤　　　５７
本田　　　８７
豊田　　　２４
松田　　　３４

宜しくお願いいたします。
・ツリー全体表示
【77951】Re:一定以上の数値に色づけをしたい
お礼  初心者A  - 16/2/16(火) 16:29 -
引用なし
パスワード
   ▼β さん：
>▼初心者A さん：
>
>で、yutoさんや私のコード、試した結果はいかがでしたか？


▼β さんへ
yutoさんへ
お礼の返事が遅れてすいません。
大変参考になりました。
yotoさのｺｰﾄﾞでためしました。
また、ご教授お願いします。
・ツリー全体表示
【77950】Re:一定以上の数値に色づけをしたい
発言  β  - 16/2/16(火) 7:44 -
引用なし
パスワード
   ▼初心者A さん：

で、yutoさんや私のコード、試した結果はいかがでしたか？
・ツリー全体表示
【77949】Re:一定以上の数値に色づけをしたい
回答  初心者A  - 16/2/15(月) 21:59 -
引用なし
パスワード
   ▼β さん：
>▼初心者A さん：
>
>う・・・ん・・・
>
>データ、なにも A1 をさんしょうしなくても、じどうはんていはできるんですが
>まぁ、A1 がデータ数だとしましょう。
>>▼β さん：
>>>▼初心者A さん：
>
>う〜ん・・・・
>
>データ件数は　何も A1 を参照しなくても自動把握は可能ですが、
>まぁ、A1 がデータ件数だとしましょう。
>
>質問しているのは、
>
>アップしたコードを実行して、どんな不具合があったのですか
>
>ということです。
>
>まぁ、ループの最初で実行時エラーになったんだと思いますが。
>
>マクロ内で、セルに数式を入れる場合、その数式は、セルに入力するまんまの文字列を
>" で囲んで指定します。
>
>セットすべき数式は、たとえば =SUM(C1:G1) という文字列ですよね。
>
>"=SUM(Cells(" & i + 2 & ",1):Cells(" & i + 2 & ",7))"
>
>これでは、たとえば
>
>=SUM(Cells(3,1):Cells(3,7))
>
>こんな数式文字列になります。なので、エクセルは、こんな名前、知らないよ！
>ということで　#NAME? と表示してますよね。
>このエラー値になっているセルのValue を参照しようとすると実行時エラーになります。
>
>もし数式を入れるなら
>
>Cells(i, 2).Formula = "=SUM(C" & i & ":G" & i & ")"
>
>ですね。
>
>数式を入れず、ＶＢＡ内でワークシート関数のSUMを使って合計した値をB列にセットすることも
>もちろんできますが。
>
>しかし、比べる数字が、6 になったり 8　になったりするのでしょ？
>そのたびにマクロを修正するのですか？
>
>コメントしたように、データ件数は自動で把握できますので、むしろ A1 には　
>比べる数字をいれ、それを使ったほうがよろしいのでは？

>>▼β さんへ
早々の返信ありがとうございます。
説明が足りなくてすいません。
しかし、比べる数字が、6 になったり 8　になったりするのでしょ？
変動するのは確かですが、Inputboxで対応しようかと考えていました。
・ツリー全体表示
【77948】Re:一定以上の数値に色づけをしたい
発言  β  - 16/2/15(月) 21:24 -
引用なし
パスワード
   ▼初心者A さん：

処理例です。

コメントした通りデータ行数はA1を参照しなくても取得できますので A1 は無視しています。
比べるべき数字 5 であたり、10 であったり、変更のつど、マクロを修正するのは感心しませんので
セルに入力して指定します。仮に、そのセルを　A2 にします。

Sample1 は条件付書式に色塗りを任せます。Sample2は比較して色塗りします。
βとしては　Sample1 がおすすめです。

Sub Sample1()

　　With Range("A1", ActiveSheet.UsedRange)　　 'このシートのA1から使用領域最後までの領域
　　　　.Columns("B").Formula = "=SUM(C1:G1)"　 '使用領域のB列に、数式を一括設定。
　　End With
　　
　　With Columns("B")
　　　　.FormatConditions.Delete
　　　　.FormatConditions.Add Type:=xlExpression, Formula1:= _
　　　　　　　　　　　　"=AND($A$2<>"""",B1<>"""",SUM(C1:G1)>$A$2)"
　　　　With .FormatConditions(1).Interior
　　　　　　.PatternColorIndex = xlAutomatic
　　　　　　.ColorIndex = 7
　　　　　　.TintAndShade = 0
　　　　End With
　　End With

End Sub

Sub Sample2()
　　Dim c As Range
　　
　　With Range("A1", ActiveSheet.UsedRange)　　 'このシートのA1から使用領域最後までの領域
　　　　.Columns("B").Formula = "=SUM(C1:G1)"　 '使用領域のB列に、数式を一括設定。
　　End With
　　
　　Columns("B").Interior.ColorIndex = xlNone　 '処理前に色をクリア
　　
　　For Each c In Range("B1", Range("B" & Rows.Count).End(xlUp))
　　　　If c.Value > Range("A2").Value Then c.Interior.ColorIndex = 7
　　Next
　　
End Sub
・ツリー全体表示
【77947】Re:一定以上の数値に色づけをしたい
回答  yuto  - 16/2/15(月) 21:01 -
引用なし
パスワード
   ▼初心者A さん：
　要件が不明な点もありますが、参考までに以下のように
書いてみました。
　Excelのない環境で記述していますので、タイプミス等
ありましたら、すみません。

Sub Sample()
　Dim TargetRow As Long
　For TargetRow = 1 To 5
　　With Cells(TargetRow, 2)　’ 本来は、Sheetもきちんと修飾した方が望ましいです。
　　' 数式の設定は、RC形式で記載。
　　　.FormulaR1C1 = "=SUM(RC[1]:RC[5])"
　　' 数式を残す必要がなければ、以下のようにも書けます。
　　' .Value = WorksheetFunction.Sum( .Offset(,1).Resize(,5).Value)
　　　If (.Value >= 5) Then
　　　　.Interior.ColorIndex = 7
　　　End If
　　End With
　Next TargetRow
End Sub
・ツリー全体表示
【77946】Re:一定以上の数値に色づけをしたい
発言  β  - 16/2/15(月) 19:14 -
引用なし
パスワード
   ▼初心者A さん：

う・・・ん・・・

データ、なにも A1 をさんしょうしなくても、じどうはんていはできるんですが
まぁ、A1 がデータ数だとしましょう。
>▼β さん：
>>▼初心者A さん：

う〜ん・・・・

データ件数は　何も A1 を参照しなくても自動把握は可能ですが、
まぁ、A1 がデータ件数だとしましょう。

質問しているのは、

アップしたコードを実行して、どんな不具合があったのですか

ということです。

まぁ、ループの最初で実行時エラーになったんだと思いますが。

マクロ内で、セルに数式を入れる場合、その数式は、セルに入力するまんまの文字列を
" で囲んで指定します。

セットすべき数式は、たとえば =SUM(C1:G1) という文字列ですよね。

"=SUM(Cells(" & i + 2 & ",1):Cells(" & i + 2 & ",7))"

これでは、たとえば

=SUM(Cells(3,1):Cells(3,7))

こんな数式文字列になります。なので、エクセルは、こんな名前、知らないよ！
ということで　#NAME? と表示してますよね。
このエラー値になっているセルのValue を参照しようとすると実行時エラーになります。

もし数式を入れるなら

Cells(i, 2).Formula = "=SUM(C" & i & ":G" & i & ")"

ですね。

数式を入れず、ＶＢＡ内でワークシート関数のSUMを使って合計した値をB列にセットすることも
もちろんできますが。

しかし、比べる数字が、6 になったり 8　になったりするのでしょ？
そのたびにマクロを修正するのですか？

コメントしたように、データ件数は自動で把握できますので、むしろ A1 には　
比べる数字をいれ、それを使ったほうがよろしいのでは？
・ツリー全体表示
【77945】Re:一定以上の数値に色づけをしたい
回答  初心者A  - 16/2/15(月) 18:54 -
引用なし
パスワード
   ▼β さん：
>▼初心者A さん：
>
>あっ！
>A1 の 5 は　【5行目まで】という 5 でしたか？？
>で、比べる基準値の 5 は、固定なんですか？
>
>だとしたら、アップされたコードで、どこがどう具合悪かったのですか？

A1の5は変動します。説例で５としただけです。
A1はﾃﾞｰﾀｰ件数でﾃﾞｰﾀｰの件数により他ｼｰﾄから
A1には1から100までのいずれかの数値が自動で入力されます。

比べる数値は、6であったり4であったり任意です。
説例で５としただけです。紛らわしくてすいません。
・ツリー全体表示
【77944】Re:一定以上の数値に色づけをしたい
発言  β  - 16/2/15(月) 17:15 -
引用なし
パスワード
   ▼初心者A さん：

あっ！
A1 の 5 は　【5行目まで】という 5 でしたか？？
で、比べる基準値の 5 は、固定なんですか？

だとしたら、アップされたコードで、どこがどう具合悪かったのですか？
・ツリー全体表示
【77943】Re:一定以上の数値に色づけをしたい
発言  β  - 16/2/15(月) 17:07 -
引用なし
パスワード
   ▼初心者A さん：

なら

B1 に =SUM(C1:G1) をいれて、下にフィルコピー。
B列を選択して、条件付き書式　数式が =B1>=$A$1　書式で背景色を好きな色に。
・ツリー全体表示
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
225 / 3841 ﾍﾟｰｼﾞ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free