カテゴリ:Excel VBA( 53 )

DATE関数は第1引数に年、第2引数に月、第3引数に日を指定すると、その年月日のシリアル値を表示してくれる便利な関数。引数はセル参照でも直接入力でもどちらでもいいため、下の画像のように「=DATE(A1,B1,1)」という数式を入れると、「2017/1/1」と表示される。
d0036883_20213834.gif
d0036883_20214058.gif


この「第3引数」は日を入れるためのものなので、「0」や「マイナスの数値」を入れることは全く頭の中になかった。ところがあるサイトで「第3引数に0を入れると前月の最終日が表示される」という記述があった。それで試してみたのが下の画像で、見事に前月の最終日が表示された。これにはホンマに驚いたな~。さらにマイナスの数値も入れられるので、何かの役に立ちそうな感じがするな。
d0036883_20214098.gif

d0036883_20213994.gif


この「第3引数に0を入れる」のを何かに使えないかと思い作った数式が下の画像で、3月1日の前日の日の値からその年が閏年であるのかそうでないかの判定を行っている。役に立たないと思うが、こんな使い方もあるということで…。
カレンダー作りにも使えるよな~。
d0036883_20213951.gif

[PR]
by th6969 | 2016-12-20 20:43 | Excel VBA | Comments(0)

Excelを使う上で非常に便利で役に立つ「マクロ」機能を使えるようにするため、以下の手順で「マクロ」機能の設定を行ってください。

1)「開発」タブの表示
マクロ機能はリボンの「開発」タブに格納されているのですが、初期設定では表示されていません。
d0036883_19363147.gif


「開発」タブを表示するため、リボン上で右クリックし出てきたメニューの「リボンのユーザー設定」をクリックします。
d0036883_19363116.gif


すると「エクセルのオプション」ウインドウの「リボンのユーザー設定」が開きます。右側にメインタブのチェックボックスがありますので、そこの「開発」チェックボックスにチェックを入れ「OK」ボタンで確定します。
d0036883_19531699.gif


「開発」タブがリボンに表示されました。
d0036883_19363194.gif


2)マクロセキュリティの設定
マクロセキュリティの設定をするため、表示した開発タブをクリックしリボンの「メニュー」を表示します。左の方にある「マクロのセキュリティ」をクリックしてください。
d0036883_19363293.gif


クリックすると「セキュリティセンター」ウインドウの「マクロの設定」が開きますので、「警告を表示してすべてのマクロを無効にする」にチェックを入れ「OK」ボタンで確定します。
d0036883_1936335.gif


この設定にするとマクロ付のエクセルファイルを開いた時に、画像のような「セキュリティの警告」が出るようになります。ここで「コンテンツの有効化」をクリックするとマクロ付のファイルを使えるようになります。また、次回開く時からはセキュリティの警告なしに使えるようになります。
d0036883_19363088.gif


さらに、インターネットから入手したエクセルファイルを開く場合には、下のような警告が出るようになります。この場合は「編集を有効にする」をクリックした後に出る「コンテンツの有効化」をクリックしてご利用ください。
d0036883_19363216.gif

[PR]
by th6969 | 2016-09-22 21:42 | Excel VBA | Comments(0)

VBAコードを自動インデントしてくれるエクセルのアドオン「Smart Indenter」がメチャ便利で助かる。必要ない人には全く必要のないものだが、必要とする人にはこれほど便利なソフトは中々ないで。適当にインデントせずにコードを打ち込んでもワンクリック(いや、ツークリックか)で自動インデントしてコードを整形してくれるから全く苦労なし(嬉)。

 ○VBAコードを自動インデントしてくれるアドオン ←使い方はこちらが詳しい

 ○Smart Indenter v3.5 ←本家サイトはこちら

[PR]
by th6969 | 2015-09-06 08:31 | Excel VBA | Comments(0)

Shiftキー付きマクロのショートカットを設定していると、他のファイルを開いたところでマクロが停止して『何で~?!』という事態が発生していた。マクロのボタンやコードを直接実行では問題なく動作するので、VBAのコードには問題ないはずなのに止まってしまう、という事で非常に困っていた。それが最近検索してみてエクセルのバグである事が分かった。

 →Workbook.Open コマンド実行後、Excel のマクロが停止する

詳しい事は上のマイクロソフト・サポートのリンクで確認してもらうとして、私のやった対策としては「Shiftキーを使わない」事やった(簡単やね)。でもこれは結構難しい問題を含んでいて、「Shiftキーを使わない」エクセルのショートカット(Ctrl+英字キー)はほとんどすでに登録されてしまっていたのだ。よく使う「Ctrl+C(コピー)」、「Ctrl+V(貼付け)」、「Ctrl+S(保存)」など、ショートカットで登録されていないキーはほとんどなかった(汗)。それでさらに検索して調べてたら「q(キュー)」キーは登録されていない事が分かり、それを使ってみたところフツーに動く事が確認できた。あと「e(イー)」キーもエクセルでは使われていないようなので、この二つで何とか対応している。

d0036883_830925.gif
「Ctrl+Shift+Q」ではなく

d0036883_830962.gif
「Ctrl+q」を使う


これはホンマのバグやね~。
[PR]
by th6969 | 2015-08-27 08:35 | Excel VBA | Comments(0)

現場の作業チェックシートをスキャンして画像で保存する時に、エクセルのウインドウをいつもの半分程度の大きさにしてデスクトップの右に表示し、スキャンした画像を左に表示して名前の変更を行っているのだが、エクセルのウインドウを小さくしたり大きくしたりする度にウインドウの位置や大きさが微妙に変わってしまうのが難点やった。それでVBAで何かできんかと思って調べてたら、アプリケーションオブジェクトでサイズ・位置共に変更できる事が分かった。微妙なサイズや位置調整は実際にやってみんと分からんけどな。私の場合は、ウインドウを通常のサイズ(大)と小さくするマクロを作ってそれをボタンに割り当てたら、後は「大」と「小」のボタンを押すだけでウインドウサイズの切り替えができるようになり、超快適に作業できるようになった。
d0036883_2352984.gif
d0036883_2352861.gif
d0036883_2352937.gif
d0036883_2353032.gif

【参考コード】
Sub ウインドウ大()
  With Application
    .WindowState = xlNormal
    .Top = 40
    .Left = 70
    .Height = 830
    .Width = 1300
  End With
End Sub

Sub ウインドウ小()
  With Application
    .WindowState = xlNormal
    .Top = 40
    .Left = 765
    .Height = 830
    .Width = 670
  End With
End Sub

このコードで気を付けることはエクセルのウインドウが通常表示(最大化でない)の場合でないとエラーになること。それを回避するために『WindowState = xlNormal』として通常表示にしてからサイズと位置の変更を行うようにしている。
[PR]
by th6969 | 2015-05-02 23:03 | Excel VBA | Comments(0)

列の入れ替え

他人のエクセルファイルを編集して使うときに、列の位置を入れ替えて見やすくしたくなる事がある。今までそんな事は全くできなかったが、VBAに慣れてきたのとネットのおかげで結構簡単にできる事がわかった。F列とG列を入れ替えたいときは、変数fにF列を代入し変数gにG列を代入後、F列に変数gを代入しG列に変数fを代入する。配列とかややこしい事はよく分からんが、一応使えてるのでまあエエか。

【参考コード】
 Dim f As Variant, g As Variant
 f = Columns("F")
 g = Columns("G")
 Columns("F") = g
 Columns("G") = f

  ↑ この場合入れ替わるのは『値』だけなので注意。
[PR]
by th6969 | 2015-04-26 00:32 | Excel VBA | Comments(0)

昨日、一所懸命作ってた「エクセルからクリップボードに文字列をコピー」するマクロやけど、今日実際に使ってみたら「メッチャ楽チン~♪」と言う感じ(嬉)。ほぼ思った通りの事ができて大満足やった。

さらに色々考えてたら「同じ製番を別の機械で同日に加工」した時に、一覧表の上にある機械のデータしかクリップボードに送れない」という不具合を発見した。これは盲点やったな~、そこまで考えてなかったし。

それで考えたのがFindメソッドで入力した製番を上からだけ検索(デフォルトのまま)してたのを、上と下から検索して見つかったセルの行ナンバーで比較するようにしてみた。行ナンバーが同じやったら当該製番は一つしか存在しないことになり、行ナンバーが違ってたら当該製番は二つ以上存在することになるはず。

ここまでできたら後は簡単。Ifの条件分岐で行ナンバーが同じやったら今まで通りの処理を、違ってたら別の処理をするようにしてみた。「う~ん、うまく行くな~」 これでほぼ完成で、処理時間は10枚程度ならほんの数分でできそう。
[PR]
by th6969 | 2015-04-02 23:27 | Excel VBA | Comments(0)

会社の現場で使ってるチェックシートをスキャンして画像で保管しようということになった時に、「えっ、それオレがやんの?」と最初は思ったが、何とか簡単にできるかもしれんのでやってみることにした。

スキャンした画像は単純な連番のファイル名で生成されるので、ファイル名を分かりやすく探しやすいものに変更する必要があった。それで考えたのが、エクセルでそれっぽいファイル名を自動で作成し、それをクリップボードに送って「名前をつけて保存」画面でファイル名に貼り付けてみよう、というものだった。

一番問題だったのは「果たしてエクセルから簡単にクリップボードに文字列を送れるか」と言うことやったが、検索してみたら案外簡単に実行できそうなことが分かった。

【参考コード】
Sub test()

Dim a As Variant
Dim b As DataObject

a = ActiveCell.Value
Set b = New DataObject

b.SetText a
b.PutInClipboard

End Sub

(アクティブセルの文字列(a)をデータオブジェクト(b)としてクリップボードに送る)

実際に作ったコードはこれほど単純ではないが、製番を入れると(a)の部分に「日付_製番_機械番号」が自動で入るようにし、それをクリップボードに送ってみることにした。後はそれをスキャンした画像の「名前を付けて保存」画面でファイル名に貼り付けるだけなので、そんなに手間はないはず。実際にやってみるのは明日からだが、何とかできそうな気はするな。うまく行くかな?
[PR]
by th6969 | 2015-04-01 23:30 | Excel VBA | Comments(0)

会社のPC環境は専務(元K君(笑))のおかげで劇的に進化して完全にネットワーク化したので、ローカルのPCではなくほぼ全てのエクセルファイルをサーバ上に置いて仕事をするようになった。他の人のファイルを参照できるなどそれはそれで非常に便利で活用しているのだが、サーバやネットワークがご臨終した時には全く仕事ができなくなるという欠点があった。それで何かいい方法はないかと思案していたところ、サーバのファイルをローカル(自分)のPCにバックアップするということに気が付いた。しかしローカルにバックアップしたエクセルファイルをいざ使ってみようとすると、
d0036883_1729416.gif
というようなダイアログが出てほとんどの場合そのまま使用する事はできなかった。他のエクセルファイルを参照したりしていないものでは何の問題も生じないが、仕事で使う場合は逆にそういうことはほとんどないので困ってしまった。多分、参照先のファイルをローカルのCドライブで見つけようとして見つからないためと思い色々考えていたら、ふと「サーバとローカルのファイル構成を同じにしてみてはどうか」と思いついた。会社のサーバはZドライブとして認識されているので、

Z\工程管理\TH69\エクセルファイル ←(こんな感じ)

これをローカルでも

C\工程管理\TH69\エクセルファイル ←(こんな感じ)

でバックアップしてみたところ、ファイルの参照先は
d0036883_19583213.gif
元々Zドライブだったのが↓
d0036883_19583590.gif

Cドライブになってる~(嬉)↓
d0036883_19582727.gif

ということで、無事に他のエクセルファイルを参照しているファイルも使えるようになった。これは上の画像のように名前で参照先を指定したり、VLOOKUP関数等で直接参照先を指定する場合でも同じで、バックアップ元サーバのファイル構成とバックアップ先ローカルのPCのファイル構成を同じにすれば、サーバご臨終時でもローカルでエクセルファイルが使えるようになるようだ。また、別フォルダのファイルを参照している場合も、ファイル構成を同じにしてやれば問題なく使えることは実証済み。

ファイルの最新版管理を考えると問題はあるが、仕事が何もできないよりはかなりマシやろ。

この話には続きがあって、次回は「サーバのエクセルファイルをローカルにバックアップするなら、参照先ファイルの指定は相対パスで」という内容になる予定。
[PR]
by th6969 | 2015-02-21 20:33 | Excel VBA | Comments(0)

先日の記事で紹介した「編集したいBookが使用中の時の処理」の中では「On Error Resume Next」を冒頭に記述していたが、「On Error Resume Next」は以後すべてのエラーをスキップするため重要なエラーが発生していても把握できないという欠点があった。それで使ってみたのが表題の「On Error GoTo 0」で、これはエラー処理を終了してリセットしてくれるというもの。だからこれをエラー発生が予想される箇所のすぐあとに挿入してやれば、以降のコード内でエラーが発生すればきっちり教えてくれることになる。「On Error Resume Next」と「On Error GoTo 0」はセットで使うべきもので、完全に完成されたコード以外では「On Error Resume Next」を単独で使うのは避けた方がよさそう。

【参考コード】(修正版)
 On Error Resume Next
 Open "C:\Book1.xls" For Append As #1
 Close #1
 If Err.Number > 0 Then
   MsgBox "ファイルは使用中です"
   On Error GoTo 0
 Else
   (ここに実行したいコードを記述します)
 End If


とした方が後々のことを考えると賢明だろう。(実際にこのコードに修正してみたところ、隠れていたエラーを発見し修正することができた)
[PR]
by th6969 | 2015-01-31 14:16 | Excel VBA | Comments(0)