末日の日付を調べる 関数 EOMONTH関数

VBAで末日を求める方法はご紹介しました

今月末日・先月末日・来月末日 を返すマクロ VBA

 

ただ単純にセルに任意の末日表記をしたい場合に便利なのがEOMONTH関数

 

使い方は簡単♪

=EOMONTH(開始日,開始日の月を基準にして、1とか-1の数値を指定する)

 

下記の画像のようにTODAY関数と組み合わせれば簡単に任意の月の末日を返すことができます(‘ω’)ノ

ちなみにサンプル画像のTODAYは11/1ですので+1の月で12月の末日が返されています。

 

 

 

ではでは♪

ピボットテーブルを作る マクロ VBA

クロス集計で便利なピボットテーブル。どこの会社でも使わない会社はないかと思います。

 

ただ、ピボットテーブルを作ることに時間をかけていては本来業務が進みません。

しかも、毎回毎回ピボットテーブルを作るのも面倒です。

ということでピボットテーブルを作成するマクロを紹介します♪

 

マクロでピボットテーブルを作るには手順があります。

1、元データを準備

2、セルに見出しラベルを設定する

3、シートを作成する

4、ピボットテーブルを作成する(範囲を指定する)←ココがピボットキャッシュ

5、作ったピボットテーブルをどのように設定するかを決める←ピボットテーブルスコレクションのプロパティを決める

ピボットテーブルのメソッドやプロパティを1から覚えるより、

マクロの記録で必要部分を変更させるほうが早いかと思います。

てなわけで早速マクロの記録で1~4を記録させましょう♪

 

記録させた内容を見ていくと、こんな感じ

長ったらしいコードですが、ピボットテーブルのキャッシュはコードの1~4行目で設定されています。

コードの1~4行目のソースデータの範囲を変数にすれば汎用性のあるピボットキャッシュができます。

コードの5行目以降はほとんど既定値のプロパティなので下記のように整理すればスッキリしたコードになるかと思います♪

ちなみに商品コードを行フィールドに加えるというマクロを追加しています。

ではでは♪

そもそもマクロって何ができるの?という方向け VBA マクロ で何ができるか

・マクロって何!?

・自動化できるって聞いたけどどんなものなの?・・

・実際に見てみたい!

・興味あるけど難しそう・・

・よくわからん!

っていう方は見てみていただければ♪

例題を沿ってどんな処理をさせているのかを実際にコードを書きながら動画にしてみました。

解説というより紹介動画なのであしからずw

動画撮影に慣れていないのでおどおどしゃべっているのはどうかご容赦くださいw

 随時更新していきますw

マクロの処理速度を調べる 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プロパティを使用します。

サンプルは下記♪

 

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

 

ではでは♪