マクロの処理速度を調べる Time関数 ループの処理速度を比較する

マクロの処理速度って地味に気になりますよね。

簡単な処理なら一瞬ですが、分岐やループの長さ等により徐々に遅くなります。

処理時間を調べるにはTime関数を使用します。

 

ちなみに私は

For NextでA列の1行目から最後の行(1,048,576)w  まで行番を割り当てる処理を計測しました。

マクロを実行させたらフリーズしました!ショック!

と思いきや、8分3秒で処理できましたww

是非みなさんも1行目から最後の行まで処理計測をしないことをオススメしますw

 

サンプルは1行目から5000行目まで行番を割り当てる処理の処理時間を調べるマクロです。

最後の行までではないので安心して確認してみてくださいw

ちなみにこれは2秒で処理されましたw

アクティブシートの画像を全て削除する マクロ VBA For each構文 Shape.Typeプロパティの一覧表

たくさんの画像を扱っていると一気に画像の削除をしたくなるときがあります。

 

都度、手作業で削除する方法は

 

①画像を選択してdeleteキーで消去する

 

全て選択して消去する場合は

 

②Controlキー + G  → セル選択 → オブジェクトをにチェック → OK で画像全てが選択されるので deleteキー!

 

本題のマクロで一気に消去するには For each 構文を使います♪

For eachはお初目ですねww

For each構文はオブジェクトの中身を調べてくれる優れものです♪

For eachでShapeオブジェクトを対象にして削除しちゃいましょう!

 

サンプルは下記

shapeオブジェクトはたくさんのオブジェクトを含んでいます。

下記は備忘録として記載しておきます。

画像は msoLinkedPicture もしくは msoPicture を指定します。

オブジェクト名 詳細
msoShapeTypeMixed -2 他のオブジェクトと混ざっている状態
msoAutoShape 1 グラフ
msoCallout 2 吹き出し 引き出し線
msoChart 3 グラフ
msoComment 4 コメント
msoFreeform 5 フリーフォーム
msoGroup 6 グループ化された図形
msoEmbeddedOLEObject 7 埋め込みOLEオブジェクト
msoFormControl 8 フォームコントロール
msoLine 9
msoLinkedOLEObject 10 リンクOLEオブジェクト
msoLinkedPicture 11 リンクしている画像
msoOLEControlObject 12 ActiveXコントロール
msoPicture 13 画像
msoPlaceholder 14 プレースホルダー
msoTextEffect 15 テキスト効果
msoMedia 16 メディア
msoTextBox 17 テキストボックス
msoScriptAnchor 18 スクリプトアンカー
msoTable 19
msoCanvas 20 キャンバス
msoDiagram 21 図表
msoInk 22 インク
msoInkComment 23 インクコメント
msoIgxGraphic 24 スマートアート
msoWebVideo 26 ウェブビデオ
msoContentApp 27 コンテンツアプリ
msoGraphic 28 グラフィック
msoLinkedGraphic 29 リンクグラフィック

エクセルトラップ~エクセルあるある~その2 隣のセルに文字が表示される 隣の列に文字を表示させない方法 VBA マクロ

これを見た私はせめてもう少しA列を広げてくれ~~ってなりますww

 

隣のセルに文字が表示されるのはエクセルの仕様なので仕方ないです。

これを解決する方法は私が知っている限りだと3つです。

1、隣の空白のセルにシングルクオーテーション「’」を入れる

2、隣の空白のセルに半角・全角スペースを入れる

3、セルの文字配置を「繰り返し」にする

 

私は折り返し表示・セルの結合は嫌いなのでやりませんwww

ちなみに3もあまり好きではありませんw

ちなみにマクロ書く人は少なくともセルの結合は嫌いだと思いますwww

 

上記画像をマクロで解決する場合は下記のサンプルとなります。

あ~スッキリした(-ω-)/

ハイパーリンクを設定する VBA マクロ セルにハイパーリンクを設定する

ハイパーリンクといえば WEBサイトのリンクを持ってくることができる機能ですね

あまり知られていませんが、フォルダやファイルをリンクにして開くこともできる便利機能なんです♪

 

マクロで書く場合手作業の場合をご紹介します♪

エクスプローラーでデスクトップを開くリンクをセルに設定するサンプルです。

 

まずはマクロだと、

Hyperlinks.Addメソッドを使用します。

サンプルコードは下記です。

Anchor:= ハイパーリンクを設定するセルまたは、オートシェイプのShapeオブジェクトを指定します。

Address:= 接続先のURLやファイルパスやメールアドレスなどのアドレスを文字列で指定します。

 

下記の引数もありますが私は使ったことないです。上記2種わかれば十分じゃないですかね。

SubAddress:= 省略可。ハイパーリンク先のURLのWebページ内にあるブックマークやワークシートのジャンプ先のセルを指定します。

ScreenTip:= 省略可。ハイパーリンク上にマウスカーソルを置いたときに表示するヒントを指定します。

TextToDisplay:= 省略可。ハイパーリンクに表示される文字列を指定します。セルの場合のみ有効です。オートシェイプの場合は無視されます。

Hyperlink:= 戻り値としてHyperlinks.Addで追加したハイパーリンクが返されます。

以上がマクロでハイパーリンクを設定する方法です。

 

下記は手動で行う場合

任意のセルを選択 → 右クリック

→ リンクを選択

 

ハイパーリンクの挿入画面が出てきます♪

赤枠のアドレス欄に “C:\Users\任意の文字列\Desktop” のパスを入力しましょう。

表示文字列の欄にはセルに表示される文字列です

 

OKを押すと、、

しっかりリンクが反映されました!このリンクをクリックするとエクスプローラーでデスクトップが開きます。

 

社内フォルダにはたくさんのフォルダやファイルがありますね

よく使うフォルダやファイルはハイパーリンクを設定しておいてサクッと表示できれば仕事も楽になり、生産性も上がるんではないでしょうか

 

ではでは♪

エクセルトラップ ~エクセルあるある~その1 表示形式を変えても元のセルの値が変わらない セルの表示形式を反映させるマクロ

エクセルの仕様で困るのが表示形式を変えても元のセルの値が変わらないことです。(‘Д’)

 

エクセルあるあるですが、

 

文字列形式 → 数値形式  にしたとき、 セルの表示形式は数値でも、セルの値は文字列のままです。

 

それによる影響は

・マクロによる処理の影響(文字列なのに数値と勘違いしてデバッグあるある笑)

・Vlook関数の参照先の表示形式によって、Vlook関数の返り値の表示形式にも影響がでてしまいます。(-ω-)

 

これを手作業で直すには、セル内をアクティブにしてEnterを押す作業が必要になります。めんど!

1つのセル、2つのセル程度を直すだけならいいですが、

リスト全体を直したいというときは手作業だと途方に暮れますねwww

 

そんなときはやはりマクロを使用してサクッと解決しましょう♪

 

マクロの内容は非常にシンプルです。下記サンプルをご参考ください♪

※表示形式を変更したいセルを選択して 実行してください。


簡単に表示形式を変更できます♪

 

ではでは♪

印刷設定をするVBA マクロ すべての列を1ページに印刷する シートを1ページに印刷する 印刷の向きを設定する

マクロ処理した後に印刷したい!ということもあるかと思います。

 

印刷をする前に印刷設定をたびたび変更するってまた手間になりますよね。

私の場合は すべての列を1ページに印刷する設定をマクロでよく使用しています。

マクロで設定する場合は

PageSetUpプロパティを使用します。

サンプルは下記♪

 

印刷設定もマクロもビシッと決まると気持ちいいものですよね笑(‘ω’)ノ

 

ではでは♪

ダイアログボックスを閉じたとき デバッグをさせないようにする ダイアログをキャンセル VBA マクロ

ダイアログボックスを呼び出したのはいいけど、閉じたいときにデバッグ発生しちゃうよ!!

っていうときは IF文で分岐させるべし!です。

ダイアログボックスの閉じるボタンを押したとき変数には ”False”  が返されます。

ですので

IF 変数=”False” Then Exit For

この一文を入れるだけでデバッグを回避できます。

簡単ですね(‘ω’)ノ

サンプルは下記ご参照あれ♪

表示された行のみに番号を割り振る・計算をする VBA マクロ 関数

行の非表示は計算しないで合計を出したい!っていうこともあるかと思います。

マクロなんか使わず簡単に済ませたい!って方には便利な関数あります。

SUBTOTAL関数 です。

=集計方法(候補が上がるので数値を選びましょう),参照範囲 となります。

重要なのはココです。

※集計方法の3桁の数字は非表示は含まないという設定ができます。

下記画像はサンプルです。

これで簡単に合計は出せますね♪

 

しかし、合計は出せても

表示セル部分のみにIDやインデックス番号を関数で出すのはちょい面倒です。

というより関数でいろいろ組み合わせるより、マクロに慣れてしまった私は関数で表示させる方がマクロ書くより時間かかりますwww

てなわけでマクロでIDや合計算出するには下記のサンプルをご確認ください。

For ~Next構文でサクっとだせます。セルAの表示セルにID番号 セルB12には合計 を出します。


sample

こんなにマクロの投稿してるのにForNext構文全然出してませんねw

ではでは♪

行・列を表示・非表示にする VBA マクロ 表示・非表示の切り替え OnOff

リストを見やすくするために行の表示・非表示はよく使うかと思います。

 

マクロで行を表示・非表示するには

 

Rows.Hiddenを使用します。

列の場合も同様です。

 

表示の切り替えをいちいち行う場合はどうするのかというと、

Not演算子が出てきます。


 =Not と入れることで左辺の反対(TrueもしくはFalse)を返します。

いちいちIf文を書く手間が省けますね♪

 

実はNot演算子はOn/Offなどできる便利な演算子だったりします♪

 

次回は表示・非表示のリストの計算について投稿したいと思います♪

家計簿便利ツール♪ みずほ銀行の家計簿ツール みずほ銀行 無料家計簿 収支一覧表 明細を取込む

みずほ銀行を使われている方は結構いるかと思います。

 

毎月の収支等の家計簿を作りたいなと思ったので、自分で作りました♪

 

せっかくなので見に来ていただいている方にも使用していただきたいなと思い、

みずほ銀行の家計簿ツールを公開します。

有料で帳簿つけるなんて本当にもったいないです。

 

ツール使用にあたり先に必要なものを上げておきます。

 

・Excel

・みずほダイレクトに登録していること

ofx2excel をダウンロードしていること。ココからダウンロードできます。https://yamagata.int21h.jp/tool/ofx2excel/

ではツールの使用方法を書いていきます。

ツール本体は下記の説明を読みながら、下記のリンクからダウンロードしてください

 

みずほダイレクトにログイン

入出金明細照会ページからMicrosoftMoney画像をクリックし、ダウンロード範囲の期間等を設定してください

 

取り込むボタンでOFXファイルがダウンロードされます。

 

ofx2excel.exeを起動

→ファイルメニューの ”OFXファイルの読み込みを選択”

→先ほどダウンロードしたOFXファイルを選択(画面一覧にデータが表示されます)

→ファイルメニュー ”クリップボードにコピーする”を選択

 

→みずほ銀行帳簿.xlsm を起動 →これです。サンプル用みずほ銀行帳簿

→ファイルを起動させて見出し日時の下のセルにペーストする

→帳簿の実行ボタンを押す

 

という流れで簡単ではありますが収支が見られるようになっております。

 

 

ご質問や更にこうした方がいいとかアドバイスあればどんどんコメントください(‘ω’)ノ

 

ではでは♪