コメントの追加，削除，表示/非表示をする

Excelのコメントについて
この記事では以下の項目をVBAで実行する方法を見ていきます。

1. コメントを追加する
2. コメントを削除する
3. シートにあるすべてのコメントを削除する
4. シートにあるコメントの位置と内容の一覧を作成する
5. コメント一覧を使用して指定したコメントを削除する
6. コメントの内容を検索して置換する
7. コメントの表示/非表示の切り替える

1. コメントを追加する

コメントを追加するにはAddCommentメソッドを使用します。
コメント内容を改行したい場合は
Text引数に指定する文字列にChr(10)を含めます。

次のコードはA1セルにコメントを追加します。

Sub macro2020527a()
'コメントを追加する
   
    With Range("A1").AddComment
        .Visible = False
        .Text Text:="コメント1行目" _
            & Chr(10) & "コメント2行目"
    End With

End Sub

コメントを追加するセルをCellsで指定したい場合は
上記コードの「Range("A1")」を
「Cells(1,1)」などと書き換えてください。

2. コメントを削除する

コメントを削除するにはClearCommentsメソッドを使います。
次のコードはA1セルのコメントを削除します

Sub macro2020527b()
'コメントを削除する

    Range("A1").ClearComments

End Sub

Rangeで複数範囲を指定するには

Range("A1:B3").ClearComments

のようにします。

3. シートにあるすべてのコメントを削除する

次のコードは
アクティブシートのすべてのコメントを削除します。

Sub macro20200527c()
'アクティブシートにあるすべてのコメントを削除する

    ActiveSheet.Cells.ClearComments

End Sub

4. シートにあるコメントの位置と内容の一覧を作成する

次のコードは
アクティブシートのコメントの位置，作成者，内容を
シートを追加して一覧にします。

Sub macro20200527d()
'アクティブシートのコメント一覧を作成する

    Dim sh As Worksheet
    Dim i As Integer
    Dim c
   
    Set sh = ActiveSheet
    i = 0
   
    Sheets.Add.Name = "コメント一覧-" & sh.Name
    Cells(1, 1) = "コメント一覧-" & sh.Name
    Cells(4, 2) = "行"
    Cells(4, 3) = "列"
    Cells(4, 4) = "作成者"
    Cells(4, 5) = "コメント内容"
   
    For Each c In sh.Comments
        Cells(i + 5, 2) = c.Parent.Row
        Cells(i + 5, 3) = c.Parent.Column
        Cells(i + 5, 4) = c.Author
        Cells(i + 5, 5) = c.Text
        i = i + 1
    Next c
   
    Cells.EntireColumn.AutoFit
    Cells.EntireRow.AutoFit

End Sub

実行結果一例：

5. コメント一覧を使用して指定したコメントを削除する

4項のコードで作成したコメント一覧を使用して
不要なコメントを選択して削除します。
A列に何かしらの値を入力したコメントを削除します。

Sub macro20200527e()
'アクティブシートのコメント一覧を使用する
'A列が空欄でないときコメントを削除
'実行前にコメント一覧のシートを表示させておく

    Dim sh As Worksheet
    Dim sh_name As String
    Dim i As Integer
   
    sh_name = Mid(Cells(1, 1), InStr(Cells(1, 1), "-") + 1, Len(Cells(1, 1)))
    Set sh = Sheets(sh_name)
   
    For i = 5 To Cells(5, 2).End(xlDown).Row
        If Cells(i, 1) <> "" Then
            sh.Cells(Cells(i, 2), Cells(i, 3)).ClearComments
        End If
    Next i

End Sub

6. コメントの内容を検索して置換する

4項のコードで作成したコメント一覧を使用して
コメント内容を置換します。

Excelではコメント内容を検索することはできますが
置換はできません。
4項のコードでコメント一覧を作成することで
コメントをセルの値として使用できるようになったので
Excelの置換機能を使用してE列のコメントの内容を変更してください。
置換後のコメントを再設定することで
コメントの内容を置換します。

Sub macro20200527f()
'アクティブシートのコメント一覧を使用して
'コメントを再設定
'実行前にコメント一覧のシートを表示させておく

    Dim sh As Worksheet
    Dim sh_name As String
    Dim i As Integer
   
    sh_name = Mid(Cells(1, 1), InStr(Cells(1, 1), "-") + 1, Len(Cells(1, 1)))
    Set sh = Sheets(sh_name)
   
    For i = 5 To Cells(4, 2).End(xlDown).Row
        sh.Cells(Cells(i, 2), Cells(i, 3)).ClearComments
        With sh.Cells(Cells(i, 2), Cells(i, 3)).AddComment
            .Visible = False
            .Text Text:=CStr(Cells(i, 5))
        End With
    Next i

End Sub

7. コメントの表示/非表示の切り替える

コメントの表示は次3パターンあります。

① コメントとコメントマークを表示
② コメントマークのみ表示
③ コメントとコメントマークを表示しない

次のコードはコメントの表示を変更します。
①～③のいずれかを適宜使用してください。

Sub macro20200527g()
'コメントの表示/非表示の切り替える

    '①コメントとコメントマークを表示
    Application.DisplayCommentIndicator = xlCommentAndIndicator
   
    '②コメントマークのみ表示
    Application.DisplayCommentIndicator = xlCommentIndicatorOnly

    '③コメントとコメントマークを表示しない
    Application.DisplayCommentIndicator = xlNoIndicator
   
End Sub

使用Ver：Win10, Excel For Office365

続きを読む "コメントの追加，削除，表示/非表示をする"

カテゴリー「Excel VBA」の記事一覧

For文の使い方とテンプレート

VBAをしばらく使っていないと
For文などの基本的な事も結構忘れてしまいます。

そんな時のために
コピぺして使えるテンプレートを作っておきます。 ...>続きを読む

虫食い2

記事『虫食い』、
『値によってセルを色分けする』で使用したプロシ ージャを...>続きを読む

Withを使うか、オブジェクト変数を使うか

Withステートメントを使うと入力を省略できます。
例えば次のようなコードについて説明します。

Sheets("Sheet1").Cells(1,1) = 1
Sheets("Sheet1").Range("A2") = 2

このコードをWithステートメントを使うと
次のコードのように入力できます。 ...>続きを読む

虫食い

今回もプライバシーシートと同様に
乱数を使ってセルの塗りつぶしを設定していきます。

虫食いとは何？ということですが、
いちばん最初にあるセルを選択しているとします。
次に
そこから上、下、右、左のセルのどれかに
移動します。
この４つの方向を決めるのに乱数を使います。
あとはそれの繰り返しです。 ...>続きを読む

プライバシーシート2

数字を使ったプライバシーシートは
たくさんShapeを使ったため重かったです。

もっと実用的に、
ランダムな模様で
プライバシーシートを作ってみます。 ...>続きを読む

プライバシーシート

役所などからの封筒や各種請求書の封筒の内側には
ランダムな模様や文字が印刷されています。
それによって中身が見えないようになっています。 ...>続きを読む

型宣言文字とその使用方法

データ型について型宣言文字についてです。

型宣言文字とは、
変数、定数、関数名の末尾に付ける、データ型を示す文字です。

次の表のようになっています。 ...>続きを読む

Openメソッドを使ってURLが存在するか確かめる

前の記事 『 Webクエリを使ってURLが存在するか確かめる 』で
できなかったこと、
URLが存在するか確かめるを違った方法でやってみます。 ...>続きを読む

メソッド、プロシージャ、関数の呼び出しと引数指定

メソッド、プロシージャ、関数を呼び出すときの引数について、
括弧が要るのか、要らないのか？
複数の引数はどのように指定するか？
名前付引数はどう使うのか？
など整理したいと思います。
以下の順にみていきます。 ...>続きを読む

静的変数/Staticステートメントについて

保持したい値はシートに入れちゃうので
今まで静的変数を使わなかったのですが、
ここいらで使えるところは使えるようになりたいですね。

まずは、静的変数とは？
というところからです。 ...>続きを読む

マクロをショートカットキーに割り当てる

ショートカットキーとは
1つ前の状態に戻るときは Ctrl + z、
コピペは Ctrl + c して Ctrl + v のように
キーボードで特定の操作をできるキーボードのキーです。

頻繁に使うマクロ、
特に選択しているセルの値を変えたり操作したりするタイプのマクロは
ショートカットキーに割り当てると便利です。 ...>続きを読む

「より高速な VBA マクロ」について

下記リンク先のページ
『 Excel 2007 におけるパフォーマンスの改善 』
の「より高速な VBA マクロ」についてのメモ...>続きを読む

脳トレ計算

某有名ゲームの簡単な計算問題を
VBAでやります。

最近、脳みその活動レベルが低下しているなと
自覚している人にお薦めです。
２桁対１桁の計算をしたときに
脳みそがシュワシュワするのを感じました。

まずは準備です。
コードはこちら ...>続きを読む

よくある質問20100803

ある記事内のあるプロシージャを実行してこのようなエラーが起こる場合は
該当記事より以前の記事で書いた
自作のプロシージャ(Sub)や
ファンクションプロシージャ(Function)を利用しています。

これらがないためにこのエラーが起こります。 ...>続きを読む

VBAで何ができるか？具体例１

VBAに限らず、
プログラミングを始める一番の抵抗は
何ができるかわからない事だと思います。

そこでVBAで何ができるか？の具体例です。 ...>続きを読む

ある文字列を含むかで条件分岐

ある文字列が含まれているかを調べるのに
文字列操作系InStr関数を使います。

InStr関数は、
指定した文字列の中に探したい文字列がある場合は、
探したい文字列の先頭の文字が
最初からの何文字目かを返します。

探したい文字列がない場合は0を返します。 ...>続きを読む

MsgBoxの使い方とビジュアル一覧

ただメッセージを示すだけの場合 ...>続きを読む

列を表す英文字を列番号に変換

記事「列番号を 英文字に変換してRangeプロパティに使う 」
では、列番号を英文字に変換しました。

今回は、その逆で
英文字を列番号に変換します。 ...>続きを読む

Excelは見えないところでギザ休む

記事「マルチコアと VBA 」において
タスクマネージャの「パフォーマンス」のところ
「CPU使用率の履歴」について書きました。 ...>続きを読む

マルチコアとVBA

今使っているパソコンが
シングルコアかマルチコアかは
タスク マネージャでわかります。

タスクマネージャは
「ファイル名を指定して実行」で
taskmgr.exe
と入力してOKを押して実行できます。

タスクマネージャの「パフォーマンス」のところ
「CPU使用率の履歴」の枠を見てください。...>続きを読む

VBA サンプル マクロを検索

マイクロソフトサポートオンラインの
サンプル マクロを探すキーワードは

マイクロソフトは提供プログラミング言語の使用方法の一例としてのみ

がいいようです。 ...>続きを読む

続きを読む "カテゴリー「Excel VBA」の記事一覧"

カテゴリー「Excel VBA HTML」の記事一覧

HTMLファイルの目次を作る2

前の記事はこちら
『 HTMLファイルの目次を作る』

今回はファイル名をリンクに表示するのではなく
HTMLファイル内のタイトルをリンクに表示したいと思います。 ...>続きを読む

VBAのコードをHTMLで表示できるようにする

VBAのコードをHTMLでそのままの形式で表示するには
改行や段落などのタグを追加したり、
インデントを正しく表示するために
スペースの特殊フォントを入れたりします。

またVBAのコード内の文字列にHTMLが入っていると
ブラウザはそれをタグとみなしてしまうので
それも特殊フォントに置き換えます。

手順を説明します。 ...>続きを読む

画像一覧のHTMLファイル作成

以前の記事でもHTMLファイル作成したことがあります。

その記事ではあるフォルダ内の
HTMLファイルの目次を作りました。
今回は、リンクをクリックすると
画像ファイルを見られるHTMLファイルを作成します。 ...>続きを読む

HTML形式のファイルが文字化けする場合

記事「 HTMLファイルを生成」で、作成したHTMLファイルを
アップロードしてWeb上で見た時に文字化けしました。 ...>続きを読む

HTMLファイルを生成

記事「HTMLファイルの目次を作る 」では
生成したHTMLのソースをイミディエイトウィンドウに出力して
それをメモ帳にコピペして
HTML形式で保存したのですが
今回はファイルの保存までVBAでします...>続きを読む

HTMLファイルの目次を作る

指定したフォルダ内のHTMLファイルのリンクを一列に並べた
目次のようなHTMLファイルを作ります。

HTMLファイルを作るといっても
タグの生成までで
生成したタグはメモ帳などで
HTML形式で保存してください。 ...>続きを読む

HTMLのTableをVBAで作る 4

記事「HTMLのTableをVBAで作る」では
セルの「塗りつぶし」を
記事「...>続きを読む

HTMLのTableをVBAで作る 3

記事「HTMLのTableをVBAで作る」では
セルの「塗りつぶし」を
記事「...>続きを読む

HTMLのTableをVBAで作る 2

記事「HTMLのTableをVBAで作る」では
セルの「塗りつぶし」だけをTableにしましたが
次は
セルの大きさもTableに反映させたいと思います。 ...>続きを読む

HTMLのTableをVBAで作る

色の表示形式の変換1、2、3で作った
色の表示形式を変換する
Functionプロシージャを利用して
HTMLのTableを作って
遊んでみたいと思います。 ...>続きを読む

続きを読む "カテゴリー「Excel VBA HTML」の記事一覧"

カテゴリー「Excel VBA VBE」の記事一覧

イミディエイトウィンドウ内をクリアする

いろいろとイミディエイトウィンドウに出力していると
見にくくなります。

そのような場合、
手動でイミディエイトウィンドウ内を
すべてを選択して削除していました。

毎回このようなことをしていては
面倒なので
VBAでこれを実行したいと思います。 ...>続きを読む

オブジェクトモジュールではデバッグモードがないのね。

InternetExplorerでイベントを使うために
オブジェクトモジュールで変数を宣言して、
標準モジュールではなく
オブジェクトモジュールでプロシージャを書いて
実行した時にエラーが起こったら
このようなダイアログが表示されました。...>続きを読む

続きを読む "カテゴリー「Excel VBA VBE」の記事一覧"

カテゴリー「Excel VBA 色」の記事一覧

現在のカラーパレットをセルに表示して確認する

現在のカラーパレットの
表示される色とインデックスをセルに表示して確認します。

単純にFor文を使います。

コードはこちら ...>続きを読む

任意の色とその補色

記事「色を反転する」の中で
パソコンソフトのイラストレーターの機能「反転」について
書きました。
「反転」という機能の下に
「補色」という機能が付いています。
...>続きを読む

色を反転する

パソコンソフトのイラストレーターには
「反転」という機能が付いています。...>続きを読む

RGB関数を使ってセルの塗りつぶしの色を指定したとき実際に表示される色

記事「Excelで表 示できる色はいったい何色？」の中でとりあえず、
個人的にExcelで表示できる色は46色ってことにしましたが
（上記記事内プロシージャ「Macro100107c」を実行すると
46色だったり、...>続きを読む

種々のカラーパレット 5

記事「種々のカラーパレット 4」のつづき ...>続きを読む

種々のカラーパレット 4

記事「種々のカラーパレット 3」のつづき ...>続きを読む

種々のカラーパレット 3

記事「種々のカラーパレット 2」のつづき ...>続きを読む

種々のカラーパレット 2

記事「種々のカラーパレット」のつづき

まずは、シアン・マゼンダ・イエロー同士のグラデーションです。 ...>続きを読む

種々のカラーパレット

記事「カラーパレ ットを変更する」において
カラーパレットを変更する方法について書きました。 ...>続きを読む

カラーパレットを変更する

手動でカラーパレットを変更するには
メニュー[ツール] - [オプション]を選びます。
すると、このようなダイアログが表示されます。...>続きを読む

色の表示形式の変換 3

色の表示形式のまとめです。

色の表示形式

長整数型(Long)
RGB
16進数

以上３つをそれぞれ変換してみる。
今回は、16進数と長整数型の変換について

・16進数　=> 長整数型 ...>続きを読む

色の表示形式の変換 2

以上３つをそれぞれ変換してみる。
今回は、RGBと16進数の変換について

・RGB => 16進数

RGB関数の各引数のRed, Green, Blueの値を
16進数に変換する
それにはHex関数を使う。 ...>続きを読む

色の表示形式の変換 １

以上３つをそれぞれ変換してみる。
今回は、RGB と 長整数型(Long)

・RGB => 長整数型(Long) ...>続きを読む

Excelで表示できる色はいったい何色？

さて
VBAにおいてRGB関数で色を指定する
の時に、とりあえず256x256x256通りの色の表示を見ました。
実際には256x256x256色も表示できないことを
見て実感しました。
では一体Ex...>続きを読む

VBAにおいてRGB関数で色を指定する

RGB関数を使うとき

RGB(red, green, blue)のred, green, blueのそれぞれに
0から255までの整数を指定します。

理論的には256*256*256=16777216通り指定できますが
実際にはそれだけの種類で表示されません。...>続きを読む

続きを読む "カテゴリー「Excel VBA 色」の記事一覧"

カテゴリー「Excel VBA ウィンドウ」の記事一覧

ウィンドウの分割とウィンドウ枠の固定

ウィンドウの分割を手動でするには
分割したいラインの下・右のセルを選択した状態で
[表示]-[分割]をクリックします。
上記の操作で次の画像の状態になります。
...>続きを読む

意図したセルを画面の可視範囲に表示

作成途中のプロシージャを試しながら実行しているときに
セルを表示して効果や結果を見ながら順次実行したい時があります。

そのようなとき
Selectメソッドを使えば良いのですが、
他の方法もあります。 ...>続きを読む

続きを読む "カテゴリー「Excel VBA ウィンドウ」の記事一覧"

カテゴリー「Excel VBA エラー」の記事一覧

あるあるエラー：シートをオブジェクト変数に入れる

シートをオブジェクト変数に入れようとしたら
このようなエラーになったことはありませんか？...>続きを読む

あるあるエラー：オブジェクト変数

オブジェクト変数に
シートや範囲のなどオブジェクトを入れようとしたとき
次のようなエラーになったことはありませんか？...>続きを読む

エラーを無視してエラーを使う

エラーに対処する方法として
エラートラップを作るという方法があります。
これは、

On Error GoTo ErrHandle

などとしてエラーが起きたときに、
「ErrHandle」行ラベルに行き
エラーに対処するという方法です。 ...>続きを読む

2度目のエラーは捕えない

エラートラップを作っても
エラーを捕えられない。
このようなことを経験したことはありませんか？

もっと詳細に言うと、
1回目のエラーは捕えることができて
再度、実行しているプロシージャに戻ってから起こる
2回目のエラーはエラートラップに行かずに
デバッグモードになってしまう。

下のコードの次の箇所 ...>続きを読む

あるあるエラー：Webクエリ

WebクエリをVBAで作成するときのことです。 ...>続きを読む

あるあるエラー：Integer型

VBAを使い始めて少したつと経験するであろうエラーです。

最初のころはよく理解できないままやっているので、
整数を使うときは全部Integer型を使っていました。 ...>続きを読む

くだらなすぎる間違い「s」

次のコードのなかにはエラーがあります。...>続きを読む

続きを読む "カテゴリー「Excel VBA エラー」の記事一覧"

カテゴリー「Excel VBA カレンダー」の記事一覧

iCalendar形式のファイルをExcelで生成する2

以前の記事「iCalendar形式のファイルをExcelで生成する1

」での
日時の指定の方法を調べていきます。 ...>続きを読む

iCalendar形式のファイルをExcelで生成する1

iCalendar形式（以下iCal形式とする）のファイルには、
Googleカレンダーなどスケジュールアプリで出力できる
スケジュールのデータが含まれます。

この形式のファイルで
スケジュールアプリ間でスケジュールのやり取りが出来ます。 ...>続きを読む

年間予定表を作成する

一年の計は元旦にあり

ということで
元旦からは少し経ってしまいましたが
年間予定表をVBAで作成したいと思います。

1年間を1枚の紙に表すのに
左から右へ1月から12月までの月を、
上から下へ1日から月末までの日を配置します。 ...>続きを読む

月間カレンダー、祝日あり

記事「祝日を年間カレンダーに追加する 」で作った
年間カレンダーを使って
月間カレンダーを作ります。 ...>続きを読む

祝日を年間カレンダーに追加する

記事「任意の日付から任意の日付までを一列にセル に入れる 」の中の
DateWriterYプロシージャで作る年間カレンダーと、 ...>続きを読む

任意の年の祝日を取得する

大半の市販のカレンダーには
祝日の情報が載ってます。

祝日は、こちらのWebページで分かります。
国立天文台トップページ　＞ 暦計 算室　＞　暦要項 ...>続きを読む

続きを読む "カテゴリー「Excel VBA カレンダー」の記事一覧"

カテゴリー「Excel VBA 関数」の記事一覧

ワークシート関数をVBAで使う

VBAの関数と
セルに入力して使う関数(ワークシート関数)は
同じものではありません。

ワークシート関数にあってVBAの関数にないものは多いです。
ですので、ワークシート関数をVBAで使いたい場合はよくあります。

ワークシート関数をVBAで使う方法を2つ挙げます。 ...>続きを読む

続きを読む "カテゴリー「Excel VBA 関数」の記事一覧"

カテゴリー「Excel VBA クエリ」の記事一覧

WebクエリでGoogleの検索結果を取得する

Webクエリの作成方法については
記事『 Webクエリまとめ 』を参照してください。 ...>続きを読む

Webクエリを使ってURLが存在するか確かめる

前記事でWebクエリについて整理できたので
いろいろ使いたいと思っていたのですが…
とりあえず結論から言いますと、
Webクエリを使ってURLが存在するか確かめることは
できませんでした。

以下できなかった方法です。 ...>続きを読む

Webクエリまとめ

今までの記事の中のWebクエリは
マクロ自動記録で記録したものを使っていました。
必須の引数などがいまいちわからなかったので
そのまま放置していたのですが、
整理します。 ...>続きを読む

Webクエリを秒単位で更新する

Webクエリを自動で更新するには
[外部データ範囲のプロパティ]-[更新の周期]に
チェックをいれて有効にしてから
60分から1分の間で更新の周期を指定します。...>続きを読む

続きを読む "カテゴリー「Excel VBA クエリ」の記事一覧"

カテゴリー「Excel VBA シート」の記事一覧

SheetsとWorksheets

SheetsとWorksheetsの違いについて
はっきりさせるために調べてみました。

正確に表せば、
Sheets コレクションとWorksheets コレクションです。

コレクションとは、
VBAヘルプの「コレクションからオブジェクトを取得する」の頁をみると、
何らかのオブジェクトの集合のようです。 ...>続きを読む

シートを移動して並び替える2

前の記事に続いて
シートの並び替えをします。

今回は「CodeName」で並び替えます。

名前で並び替える方法と
大体は同じですが、
シートを入れ替える箇所で少し変更があります。

シートのCodeNameを配列に入れて、
その配列を並び替えるまでは同じような流れです。 ...>続きを読む

シートを移動して並び替える1

シートの位置を指定しないで
シートの挿入をVBAで実行すると
選択されているシートによって
挿入する位置が変わってしまいます。

このようにあまり計画せず
シートの挿入を繰り返して
シートが多くなってくると
見にくくなります。

そこでシートを移動して並び替えをします。 ...>続きを読む

シートの指定方法のまとめ

シートをする方法を3通り挙げます。

1.名前で指定する。
2.Indexで指定する。
3.CodeNameで指定する。

1.名前で指定する。 ...>続きを読む

シートの並び替えをする

以前の記事「 配列をソートする 」の続きで
2次元配列をソートする方法を考えていたのですが、
その前にシートの「並び替え」についてVBAで実行する方法についてのまとめ。

...>続きを読む

シート名を検索、条件を満たしたシートを削除

記事「シート挿入あれこれ 3 」では、
macro103016a、macro103016a2、macro103016a3、macro103016a4、…
と番号を付けてシートを挿入していきました。 ...>続きを読む

シート挿入あれこれ 3

記事「シート挿入あれこれ 2 」のつづき… ...>続きを読む

シート挿入あれこれ 2

記事「シート挿入あれこれ 」のつづき…

上記記事のなかで
シートの挿入方法について
次の4通りを考えました。 ...>続きを読む

シート挿入あれこれ

シートを名前を付けて挿入するには

Sheets.Add.Name = "シートの名前"

とします。 ...>続きを読む

指定したシートがあるか確認してからそのシートを削除する

VBAでシートを指定して削除しようとしたときに
そのシートがなかったら
このようなエラーになります。...>続きを読む

VBAで警告なしにシートを削除する

VBAでシートを削除するには、

Sheets("シートの名前").Delete

とします。
しかし、これには問題があります。
試しに、下のコードを実行してみます。

シートを挿入して、名前をつける。
そして、それを削除する。
っといった
元の木阿弥状態なコードです。 ...>続きを読む

続きを読む "カテゴリー「Excel VBA シート」の記事一覧"

カテゴリー「Excel VBA 書籍」の記事一覧

『Excel VBAスタンダード　VBAエキスパート』

田中亨(著)：『Excel VBAスタンダード　VBAエキスパート』, オデッセイ，2009

スタンダードは、 Excel VBAの基礎からの始まって幅広く終わる。

基本というのは、
変数/配列、プロシージャ、ステートメント等についてです。 ...>続きを読む

『Excel VBAベーシック　VBAエキスパート』

田中亨(著)：『Excel VBAベーシック　VBAエキスパート』, オデッセイ，2009

Excel VBAの基礎中の基礎からの始まって基礎中の基礎で終わる。
ベーシックなだけある。 ...>続きを読む

『Excel VBAマスターブック 2003&2002対応 WindowsXP版』

きたみ あきこ (著)：『Excel VBAマスターブック 2003&2002対応 WindowsXP版 』，毎日コミュニケーションズ ，2006/06 ...>続きを読む

『Excel VBA WEB連携術』

土屋 和人 (著)：Excel VBA WEB連携術―2007/2003対応 ，ソシム ，2009/5

ExcelでWebサービスを利用する方法が書かれた本。
ExcelでWebから情報を取得して加工やストックしておきたい人に
役立つと思います。

WEBから情報を取得する方法として ...>続きを読む

『Microsoft Excel 2000 Power Programming with VBA』

アマゾンで評価が良かったので買ってしまいました。
イミダス・現代用語並みの大きさと分厚さです。
目次を見た限りでは、
基本から発展までカバーしてそうです。

というのも、まだ目次しか読んでません… ...>続きを読む

井領 邦弘 (著)：株解析チャートから自動発注ロボットまで!Excel VBAで極

１．株価情報を
インターネットのサイトから
Excelに取り込む。

２．その情報を
テクニカル分析する。

３．分析した2つの指標を比較して
売買サインを出す。

４．出されたサインに基づいて
Internet Explorerを操作して
発注しよう。 ...>続きを読む

田沼晴彦：『Excelで遊ぶ手作り数学シュミレーション』

...>続きを読む

続きを読む "カテゴリー「Excel VBA 書籍」の記事一覧"

カテゴリー「Excel VBA セル」の記事一覧

数式かどうか判定する

セルに数値が直接入力されているか，
数式の計算結果の値なのか，
セルに表示されている値のみで判断できません。...>続きを読む

セルが空白か判別して斜線を設定/塗りをグレイにする

表を作成していると
内容を記入する必要がない空白のセルが存在することもあります。

空白のままだと表全体が見難いので
斜線をつけたり，セルの塗りをグレイにしたります。

これをVBAで実行したいと思います。

空白かどうかの判定は単純に
セルの値が""に等しいかの比較でします。...>続きを読む

RangeオブジェクトにCellsプロパティを使う

あるセルを参照するのに
RangeプロパティとCellsプロパティの両方が使えます。

アクティブシートのセルA1の値を1にするのは
次の2通りでできます。
ActiveSheet.Range("A1") = 1
ActiveSheet.Cells(1, 1) = 1 ...>続きを読む

結合したセルの高さを自動調整する

Excelで行と行の間をダブルクリックすると
セルの高さが自動調整されます。

しかし、結合したセルでは
高さの自動調整ができません。 ...>続きを読む

セルに入力した文字をはみ出して表示させる

Excelでセルに長い文字列を入力した時
右側のセルに何も入力されていなければ
下の画像のA1セルのように
文字が右側にはみ出して表示されます。...>続きを読む

セルの内容を90度回転、-90度回転、180度回転

Excelで座席表やオフィスの配置図などを作ることありますよね。

どちらを上にして作ればいいのか迷ってとりあえず作ったけど
やっぱりこっちの向きが良かった
とか
自分の座っている席から見てこの向きの座席表だと見難い
など ...>続きを読む

セルの高さの自動変更をなくす

手動でもVBAでも
セルに複数行の文字列を入力すると
セルの高さが自動で変更されます。

何十行になる文字列を入力すると
1つのセルで画面の縦一杯になってしまって
とても見にくく扱いづらくなることがあります。

Excel本来の使用目的は
そのようなことではないのでしょうがないですが。 ...>続きを読む

Range オブジェクトのOffset プロパティの使い方

個人的に、
以前の記事『 虫食い 』で初めて使ったプロパティです。

存在は知っていたものの使いどころがわからず
使っていませんでした。 ...>続きを読む

秒の小数部分の表示形式

1秒の小数点第二位までExcelのセルで表示できるようです。

表示形式が「h:mm:ss.00」だと
12:28:34.78などと表示されます。
1/100秒単位で表示ということになります。
ストップウォッチと同じです。 ...>続きを読む

Format関数を使ったのに違う表示形式が自動で適用される件

この自動で表示形式が適用される機能で
イライラすることが間々あります。

何も入力したことのないセルA1に、
次のコードを実行します。 ...>続きを読む

数式を値に変換する2

以前の記事「 数式を値に変換する 」で
コピペを使って
数式を値に変換する方法を紹介しました。

今回はコピペを使わずに
数式を値に変換してみます。 ...>続きを読む

Copy、Pasteメソッドを使わないコピペ

Copy, Pasteメソッドを使わないなら
コピペとは言わないとは思いますが
とりあえずわかりやすいと考え、こう表現しました。

まずは、
なぜワザワザCopy, Pasteを使わないのか？ですが
Copy, Pasteを使うプロシージャを実行しながら
他の作業をしているとき面倒が増えることがあります。 ...>続きを読む

数式を値に変換する

数式が入っているセルは
その数式で参照しているセルの値を変更すると
数式が入っているセルの値まで変わります。

このとき数式が入っているセルの値を変化させたくない場合、
数式を値に変換します。

「変換」といっても
要するにコピペです。 ...>続きを読む

[書式設定]をクリアするときの不便を解消

日付けが入っているセルを、
メニューバーの[編集]-[クリア]-[書式設定]で
書式設定をクリアすると
フォントの色だけをなくしたいだけでも
「表示形式」までクリアされて数字になってしまいます。

そこで、また表示形式を設定しなおす…
なんてことをしたことありませんか？ ...>続きを読む

別のシートのセルと同期する

同じシート内であるセルとあるセルを同期するには
セルを参照します。
例えば、A1セルをB1セルと同期させるには
A1セルに次のように入力します。

=B1

別のシートのB1セルを参照するには

='シート名'!B1 ...>続きを読む

コピーしてコピーしたセルを挿入する

コピーしたセルをペーストするとき
既存のデータの後（下）に追加する場合は
そのままペーストすればいいのですが
既存のデータの前にコピーしたセルを入れたい時
セルを挿入してペーストします。 ...>続きを読む

列番号を英文字に変換してRangeプロパティに使う

2つの変数、例えば i と ｊ などで
For文を使ってセルを指定したいとき
Rangeプロパティのみ対応していて
Cells( i, j )ではできないプロパティがあるので
少し手間が必要です。

なぜなら、
Ragneプロパティは、A1形式にしか対応してないからです。
2つの変数 i、ｊ を使ってA1形式にする必要があります。 ...>続きを読む

VBAでセルの高さと幅を自動調節する

手動で、セルの高さと幅を自動調節するには
セルとセルの境界線にマウスのカーソルを移動させ
下の画像のカーソルになったらダブルクリックします。 ...>続きを読む

範囲指定のまとめ

注意点は、Rangeの中でCellsを使うとき

Range(Cells(2, 3))

とできない。セル(2, 3)だけ指定したくても...>続きを読む

削除とクリア

手動で「削除」や「クリア」をするときは
メニューの[編集]を選んで、
それぞれ[削除]や[クリア]を選びます。

「クリア」は以下4つの方法があります。

すべて
書式
数値と値
コメント

セルの値を消すとき
よくキーボードのDeleteキーを使います。 ...>続きを読む

ピクセル単位でセルの高さと幅を指定する

記事「セルの縦と 横の比 」
記事「セルの高さ と幅の設定 」
でセ...>続きを読む

Cellsの解剖

記事「HTMLのTableをVBAで作る 4」で
ウォッチウィンドウについてチラッと書いたので
そのことについて。

まずはウォッチウィンドウの表示の仕方：
VBEのメニューで ...>続きを読む

セルの高さと幅の設定

セルの縦と横の比 の記事のときに
セルの大きさを思い通りに決めることは
案外難しかったのですが
今回はその続き… ...>続きを読む

セルの縦と横の比

意外に難しいのが
セルの高さと幅の変更

私はExcelでセルを使って描画する目的で
セルの高さと幅を１：１にしたいと思ったのです。
VBAヘルプでそれらしい

ColumnWidth
RowHeight

を見つけたので
次のようなコードで試す。...>続きを読む

R1C1形式をA1形式に変換する

たびたび、R1C1形式をA1形式に変換することがあったので
R1C1形式をA1形式に変換するFuctionプロシージャを作ってみた。
作ったという程のものでもないんですが…

詳しくは、
VBAのヘルプで「ConvertFormula」で検索してみてください。　

まずは、下記のコードを標準モジュールにコピペしてください。...>続きを読む

手動でよくやるコピペ

Excelで手動でコピペするとき、どうやってますか？

コピーしたい範囲を選択してCtrl + Cでコピー。
次にコピーしたい範囲の左上のセルを選んでペーストする。
こんな感じでしょうか。

これをマクロ自動記録すると…

...>続きを読む

セルの結合を解除して結合されていた全セルに同じ値を入力する

セルの結合をしていると
並び替えができません。

並び替えをするために
セルの結合を解除すると
左上のセルのみに値が設定され
その他のセルは空白になってしまいます。

セルの内容によって
並び替えをしたい場合は
結合解除で空白になると適切に並び替えができません。...>続きを読む

セルの枠線を非表示にする

たまたまVBAのヘルプで見つけました。

以前セルの枠線を非表示にしたくて
ヘルプを検索したとき見つからなかったので、
セルの塗りつぶしを白にして
枠線を見えないようにしていました。

VBAヘルプからの引用コードはこちら ...>続きを読む

値によってセルを色分けする

数字を可視化する方法として
セルに値の大小によって異なった色を付けて
グラフ化したいと思います。 ...>続きを読む

フォント名一覧とその見本を作成

書式設定のツールバーで
使えるフォントがわかります。

ということは、
このツールバーを探っていけば
使えるフォントがわかるということですね。

マクロ自動記録を開始して
書式設定のツールバーのフォント名を変えたりします。
すると記録されたコードで
このツールバーのオブジェクトが ...>続きを読む

Patternの見本

Patternの見本をつくります。

コードはこちら ...>続きを読む

LineStyleの見本

セルの枠線、LineStyleとWeightの表をつくります。

コードはこちら ...>続きを読む

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

VBAでハイパーリンクを設定するには
Hyperlinks.Addメソッドを使用します。
Addメソッドには次の5つの引数を指定します。...>続きを読む

複数のキーワードを含むセルを検索する

Excelの検索機能では
Googleで検索するときのように
複数のキーワードを空白で区切って入力して
検索することはできません。

VBAで
複数のキーワードをすべて含むセルを
検索できるコードを作っていきます。

以下コードの流れを説明します。...>続きを読む

続きを読む "カテゴリー「Excel VBA セル」の記事一覧"

カテゴリー「Excel VBA 数学」の記事一覧

2011年センター試験、数学プログラム問題

2011年センター試験、数学プログラム問題が
新聞に載っていたので
それについてです。
問題全文は1月17日の新聞を参照してください。

問題を最初から解いている場面を想定したレポートです。

ん～今年はどんな問題かなー
え～っと ...>続きを読む

センター試験数学のプログラム問題をVBAで

センター試験…
今はもう遠いむかしですね～

高校生の時
数学の教師にセンター入試のプログラムのことを
聞いたことがあったのですが
全く教えてもらえませんでした。

こんなもんも教えられないで
よくあの人たちは数学教師を
やっていられたなーと思います。
世の中　結構　甘いですね。 ...>続きを読む

ピタゴラス数

ピタゴラス数とは
2乗するとひとつが他の2つの2乗の和になるような
3つの自然数の組のことです。

式で表すと

a^2 + b^2 = c^2

です。

このピタゴラス数をVBAで求めたいと思います。

プログラム的に求めようと思います。 ...>続きを読む

最大と最小

VBAを使い始めて間もなく困ることは
VBAで最大と最小をどうやって求めるか？
だと思います。

数式に使うMAXとMINのような関数が
当然、VBAの関数にもあると思っていました。

しかし実際にはありません。
最大と最小をVBAで求めるには、
ワークシート関数を使います。 ...>続きを読む

0以下は0に、その他はそのままにする

まずは、ワークシート関数でやってみます。

IF関数を使います。
たとえば、
A1-B1の計算をして
その答えを0以下は0に、その他はそのままにするには

=IF(A1-B1<0,0,A1-B1)

とセルに入力します。 ...>続きを読む

友愛数

先日テレビで放送されていた映画
『博士が愛した数式』を見ていたら
友愛数というものが出てきました。

この友愛数をVBAで求めてみました。

映画自体は良かったです。 ...>続きを読む

素数を調べる

素数とは
1とその数自身でしか割り切れない数です。

まずは2から a までの正数に含まれる素数を調べます。
下のコードでは、a = 1000で
2から1000までの正数に含まれる素数を調べています。

大まかな考え方は次のようになっています。 ...>続きを読む

商と余り

基本的なことですが
しっかり調べてみるといろいろと知らないことがあるものです。

いままで紹介してきたプロシージャでも
商と余りは利用してきました。
最近の記事ではこのように使いました。

商： Int( i / j )
余り： i - Int( i / j ) * j ...>続きを読む

正多角形の内角の組合せ

さて、今回は厳密でない数学的テーマ。

１つの頂点に集まる正多面体の内角の総和が360度以外なら
立体的（平面以外）になります。

今回はその中でも
内角の総和が360度より小さくなる組合せのみを調べます。 ...>続きを読む

任意の整数から任意の整数までの乱数

乱数を発生させるにはRnd関数を使います。
Rnd関数は0 以上、1 未満の範囲の値を返す。
試しに表示させてみる。...>続きを読む

続きを読む "カテゴリー「Excel VBA 数学」の記事一覧"

カテゴリー「Excel VBA データ型」の記事一覧

オブジェクトがNothingかどうか調べる

定義したオブジェクト型の変数に何も代入していない状態では
ウォッチで調べると変数の値はNothingになります。

変数objがNothingかどうかを判別するために
If文を使って条件判断します。

そこで次のコードを実行してみます。...>続きを読む

続きを読む "カテゴリー「Excel VBA データ型」の記事一覧"

カテゴリー「Excel VBA データ操作」の記事一覧

重複行を削除する

同じ列において重複している値を削除します。 ...>続きを読む

列と列を入れ替える

前の記事は列の「並び替え」でしたが
並び替えよりも
入れ替えをしたいなっと思うことの方が
個人的には多いです。

1列丸ごと入れ替えます。
ですので列の途中まで同士を入れ替えたい場合は
他の方法でお願いします。

セルの値を配列に格納する方法を知ってからは
かなりこの方法を重宝しています。 ...>続きを読む

列の「並び替え」

列の「並び替え」
ってどんな場合に使うのかわからないけれども
とりあえず
1っ回くらいやっておこう。 ...>続きを読む

ランダムに並び替える

行の並び替えや
配列のソートについての記事が続いていますが
その過程で
並び替えの効果を見やすくするために
行や配列をランダムで並び替えた方がいいかなと
思うこともありました。

ある名簿で
ランダムで並び替えて順番を決める。

一定量の英文法の問題があり
それを毎回ランダムな順番で出題したい。 ...>続きを読む

行と列を入れ替える

記事「数式を値に変換する 」では
[編集]メニューの[形式を選択して貼り付け]を使いました。

行と列を入れ替えるのにも[形式を選択して貼り付け]が使えそうです。 ...>続きを読む

続きを読む "カテゴリー「Excel VBA データ操作」の記事一覧"

カテゴリー「Excel VBA 配列」の記事一覧

2次元配列の行列を入れ替える

以前の記事で
セルの範囲の行列の入れ替えを
コピぺで実行する方法を紹介しました。

今回は
2次元配列の行列を入れ替えます。

これには
ワークシート関数のTransposeを使用します。 ...>続きを読む

2次元配列をランダムに並び換える

以前の記事「ランダムに並び替える 」では
セルの内容を乱数を使ってランダムに並び換えました。

今回は配列をランダムに並び換えたいと思います。 ...>続きを読む

2次元配列をソートする

以前の記事「 配列をソートする 」では
1次元の配列をソートする方法でしたが
今回は2次元配列をソートします。 ...>続きを読む

配列をソートする

参照URL:
『 How to Use a Visual Basic Macro to Sort Arrays in Excel 』

上記ページによると
VBAで配列を直接ソートする方法はないようです。
代わりに2つの方法 ...>続きを読む

セルの値を配列に格納して使う

Excelはセルの値を直接使えば、
わざわざ配列を使わなくても済むということを
以前の記事で書きました。

しかし、配列に入れて使ったほうが
処理が速い場合もあるようです。

最初に
配列の添え字の最小値を1にするために
使用するモジュールの一番上に ...>続きを読む

2次元配列の使い方

記事「 配列の使い方を整理」で説明したように
以下の順番でみていきます。

...>続きを読む

配列の使い方を整理

配列の使い方を何回も調べてしまう人は
一度、頭の中で使い方を整理するといいかもしれません。

配列の使い方がはっきりしない理由は、
配列の使い方が大きく分けて２種類あることだと思います。
この２つの一部分と一部分が混ざって
間違った配列の使い方をして
結局、調べなおすということを
何回もしました。 ...>続きを読む

特定の文字で区切られた文字列を分割して配列にいれる

もう少し詳しく言い換えると、
特定の文字（例えば空白やコンマなど）で区切られた文字列を
その特定の文字で分割して一次元配列にいれる。

これにはSplit関数を使います。
使い方の具体例を挙げます。

Split("1,2,3,4,5", ",")

これは、 ...>続きを読む

続きを読む "カテゴリー「Excel VBA 配列」の記事一覧"

カテゴリー「Excel VBA ファイル」の記事一覧

フォルダ内のファイル情報のリスト化

指定したフォルダに含まれるファイルの情報を得るには
FileSystemObjectオブジェクトを使用します。
FileSystemObjectオブジェクトは次のコードで作成します。

Set fs = CreateObject("Scripting.FileSystemObject")...>続きを読む

VBAでファイルの存在を確認してから開く

VBAでブックを開くときは

Workbooks.Open "ファイルまでのパス"

とします。

例えば、下のコードで
指定したファイルが存在しない場合
エラーになります。 ...>続きを読む

ファイルの名前を変更する

VBAでファイルの名前を変更するには
Nameステートメントを使います。
使い方は、

Name OldName As NewName

のOldNameに名前を変更したいファイルのフルパスを入れ、
NewNameに名前を変更した後のフルパスを入れます。 ...>続きを読む

VBAでファイルを削除する

記事「ワイルドカードで検索してファイル名を取得 」
のなかのコードを改造してコピーしたファイルを削除します。 ...>続きを読む

ワイルドカードで検索してファイル名を取得

VBAでファイルを検索するときは
FileSearchオブジェクトを使います。

下のコードは
マイドキュメントのフォルダ内の拡張子.xls
つまりExcelのファイルを検索して
検索結果をシートに書き出します。

変数MyPathの文字列内の
「あなたのユーザー名」のところを適宜変更してください。 ...>続きを読む

続きを読む "カテゴリー「Excel VBA ファイル」の記事一覧"

カテゴリー「Excel VBA フォームコントロール」の記事一覧

VBAでコマンドボタンを作成

手動でのボタンの作成については、
記事『ボタンを使ってマクロを実行する』を
参照してください。 ...>続きを読む

ボタンに画像を使用する

ボタンの作成については、
記事『ボタンを使ってマクロを実行する』を
参照してください。 ...>続きを読む

昇順/降順の並び替えをするボタンを作成

ボタンを作成して、
クリックでマクロを実行、
ボタンに表示されている文字を変更することについては、
記事『ボタンを使ってマクロを実行する』を参照してください。 ...>続きを読む

ボタンを使ってマクロを実行する

ボタンを使ってマクロを実行するには、
まずボタンを作成します。

「コントロールツールボックス」のツールバーが
表示されていない場合は、
次の手順で表示してください。

[表示]-[ルーツバー]-[コントロールツールボックス] ...>続きを読む

続きを読む "カテゴリー「Excel VBA フォームコントロール」の記事一覧"

カテゴリー「Excel VBA メール」の記事一覧

確実性を増した簡易メール送信

”簡易メール送信”関連の今までの記事中の
プロシージャを使われた方はわかると思いますが、
SendKeysを使っているので失敗することがあります。

これはSendKeysが実行されるときに
キーを送りたいウィンドウが
最前面にない(出現してない)ことがあるためです。 ...>続きを読む

簡易メール送信 ２の補足

記事「 簡易メール送信 ２」では、
シートに作ったメールのリンクを
件名を指定してからクリックして、
メールのウィンドウが立ち上がったら
SendKeysで本文を入力して送信しました。 ...>続きを読む

簡易メール送信 ２

記事「 簡易メール送信 」内で、
メールの件名だけの簡易メールを送信する方法を紹介しました。 ...>続きを読む

簡易メール送信

VBAでメールを送信するには
Outlookを操作してって…
めんどくさいです。

別に長文を送りたいわけではなく、
ただ単に外為をモニタしてるパソコンから
タイミングを知らせるだけのメールでいい場合は
こんなんでいかがでしょうか？ ...>続きを読む

続きを読む "カテゴリー「Excel VBA メール」の記事一覧"

カテゴリー「Excel VBA ワークブック」の記事一覧

ワークブックのパスを取得する

パスとは、
ブックが保存されているフォルダの場所のことです。

例えば、

C:\Users\Username\Documents\

のようなものです。

アクティブブックのパスは
次のコードで取得できます。

ActiveWorkbook.Path...>続きを読む

ワークブックをアクティブにする

ワークブックをアクティブにするとは
手動操作でいうと
Excelのウィンドウ内をクリックして
編集できる状態にすることです。

VBAでワークブックをアクティブにするには
次のようにします。...>続きを読む

既存xls形式ブックをxlsx、xlsm形式で保存し直す

Excel2003までの保存形式はxls形式でした。
Excel2007以降はマクロが使えるブックはxlsm形式、
マクロが使えないブックはxlsx形式になりました。

xls形式のブックとは言い換えると
拡張子が.xlsのブックのことです。
同じくxlsx形式の拡張子は.xlsx、
xlsm形式の拡張子は.xlsmです。...>続きを読む

ワークブックを新規作成、名前をつけて保存、閉じる

ワークブックを新規作成するには
Workbooks.Addメソッドを使用します。
今回は変数wbに新規作成したワークブックを入れておきます。

新しく作成したワークブックに名前をつけるには
"名前を付けて保存"をします。
保存をしないで名前を付けることはできません。...>続きを読む

使用中のファイルを開く、開かない

手動で使用中のブックを開こうとすると
次のようなダイアログが表示されます。...>続きを読む

ブックが開いているか確認してから開く

開いているブックに変更を加えたあと
同じブックを開こうとすると次のような警告が出ます。...>続きを読む

バックアップいろいろ

いろいろなタイミングでのバックアップについて。
ここではバックアップというのは
ファイルの複製をコピーする
ということを指します。 ...>続きを読む

ファイル保存時に自動でコピーを作って保存

手動でエクセルのブックを保存する時に
自動でコピーを作って保存します。

何のためにコピーを作るかというと
バックアップの為です。

VBAを使っていると予期せぬ時に
Excelが強制終了してしまうことがしばしばあります。
しかも、そのファイルを開こうとすると
すぐに強制終了して開けなくなることもあります。 ...>続きを読む

続きを読む "カテゴリー「Excel VBA ワークブック」の記事一覧"

カテゴリー「Excel VBA 為替・株」の記事一覧

為替の時系列データの取り込み(データ更新)

記事「為替の時系列データの取り込み」で
日ごとの為替データ(USD/JPY,EUR/JPY,AUD/JPY)を取得しました。

今回は上記記事で作成したクエリを更新して
最新データを取得していきます。...>続きを読む

為替の時系列データの取り込み

データの取得先は
YAHOO!ファイナンスです。

このサイトの「株式」のページで
以下の通貨の時系列データが見れます。...>続きを読む

ゆる～いリアルタイム為替レートのリソース

Excelでインターネットの情報を取り込むには
Webクエリが簡単ですが、
最近のWebページは
FlashとかJavaとJavaScriptとか使ってあって
WebクエリではExcelに取り込めなくなりつつあるように感じます。 ...>続きを読む

続きを読む "カテゴリー「Excel VBA 為替・株」の記事一覧"

カテゴリー「Excel VBA 印刷」の記事一覧

印刷範囲の自動設定

シートの印刷範囲を指定していない状態で
印刷プレビューをすると
使用している範囲を自動でExcelが認識してくれます。

これを利用して
印刷範囲を自動設定したいと思います。...>続きを読む

改ページの追加・削除・位置取得

改ページとは
印刷する時のページの区切りのことです。
水平，垂直の2種類の改ページがあります。
設定された用紙サイズに合わせて自動設定されます。
また，自分で任意の位置に改行を追加することができます。
...>続きを読む

ページ設定をコピーする

あるシートのページ設定を
他のシートにも設定したいときの方法について見ていきます。

セルの書式設定のように
コピーやペーストのメソッドは見つからなかったので，
コピー元のページ設定に関するプロパティの値を
コピー先のプロパティに指定することで
ページ設定をコピーしていきます。 ...>続きを読む

先頭ページ，奇数・偶数ページのヘッダー・フッターの設定

ヘッダー・フッターは先頭ページのみ異なる内容に設定できます。
さらに，奇数・偶数ページで異なる内容にも設定できます。

手動で設定する場合,
[奇数/偶数ページを別指定]と
[先頭ページのみ別指定]にチェックを入れて
[ヘッダーの編集]か[フッターの編集]をクリックします。...>続きを読む

複数ページにまたがる表を印刷する時に，表タイトルを各ページに表示させる

表が大きくて1ページに収まらないとき
2ページ目以降に
表のタイトル行・列が表示されずに見にくくなってしまいます。

そのような場合に
表のタイトル行・列を各ページに印刷できる設定があります。

手動で設定する場合は
[ページレイアウト]タブ-[印刷タイトル]をクリックすると
下の画像のウィンドウが表示されます。...>続きを読む

ヘッダー・フッターに画像を表示させる

ヘッダー・フッターに
社外秘マークやロゴマークなど
画像を使用したい場合があります。

手動で画像を設定するには次のようにします。
[ファイル]-[印刷]-[ページ設定]-[ヘッダー/フッター]タブをクリックすると
下の画像のウィンドウが表示されます。...>続きを読む

ヘッダー・フッターにセルの値を使用する

手動では
ヘッダー・フッターにセルの値を指定することはできません。

VBAで
セルの値を使用する方法を見ていきます。

次のコードは
アクティブシートのセル(1,1)の値を
ヘッダー右側に指定します。...>続きを読む

ヘッダー・フッターのフォント設定

手動でヘッダー・フッターのフォント設定をするには
次のような手順で行います。
[印刷]-[ページ設定]-[ヘッダー/フッター]タブで
次のウィンドウが開きます。
...>続きを読む

ヘッダー・フッターの設定

ヘッダーは印刷プレビューや印刷された物の
ページ上部に表示される共通部分のことです。
フッターは下部に表示される共通部分のことです。...>続きを読む

ページ設定の自動化

ページ設定は
[ファイル]-[印刷]-[ページ設定]をクリックする、もしくは
[ページレイアウト]タブのページ設定区分、右下の矢印をクリックすると
現れるウィンドウで設定できます。
(以下このウィンドウを「ページ設定」とする)
...>続きを読む

続きを読む "カテゴリー「Excel VBA 印刷」の記事一覧"

カテゴリー「Excel VBA 図形」の記事一覧

セルに移動するリンクを付けた図形を作成する

例えば，
Excelに貼り付けた画像の部分をクリックすると
特定のセルに移動させたい場合，
画像上に図形を配置して
図形にハイパーリンクを付けることで実現できます。

このようにすると画像の部分と
セルを関連付けることができます。...>続きを読む

セル内容をもとにPasteメソッドで文字列の図形を作成する

図形で文字列を作成する場合に
1つ１つ文字を入力していくのは手間です。

ここでは，セルに入力された内容で
複数の文字列を図形で作成していきます。

手動でペーストする場合に
右クリックメニューで
[形式を選択して貼り付け]-[図]の手順で作成される図は
セルの大きさや文字の設定など反映した文字列の図形になります。...>続きを読む

図形のビジュアル一覧を作成する

シェイプを追加するには
AddShape メソッドを使用します。
次のコードは位置が左から10pt,上から10pt
幅100pt,高さ100ptの四角形を追加します。...>続きを読む

ShapeをTypeごとに扱う

Shape の Typeプロパティについては
次のページを参照してください。
参: MsoShapeType 列挙型 ...>続きを読む

Shapeを指定して削除する

すべてのShapeを選択する方法を
以前の記事『 ActiveSheetのすべてのShapeを選択 』で書きましたが、
この方法を使わなくても
すべてのShapeを選択できることがわかりました。 ...>続きを読む

テキストボックスを作成する2

記事『 テキストボックスを作成する 』のつづき、
テキストボックスの設定を詳しく見ていきます。

まずはテキストボックスには
どんな設定ができるのかを調べます。 ...>続きを読む

テキストボックスを作成する

セルに関係なく文字を表示したいときは
ワードアートを使うしかないと思っていましたが、
テキストボックスというものがあるのですね。
下の画像は[図形描画]ルーツボックスですが、
テキストボックスは矢印のマークです。...>続きを読む

ActiveSheetのすべてのShapeを選択

ActiveSheetのすべてのShapeを指定することは
以外に難しいです。

前記事を作っている途中のことですが、
作成したたくさんのShapeを削除したいとき
1つ1つ手で選択して削除するのも面倒なので、
シートごと削除してました。 ...>続きを読む

PresetTextEffectのビジュアル一覧

ワードアート オブジェクトを作成します。
テキストが押し出されて立体的になっていたりするヤツのことです。

どのようなものがあるか、
PresetTextEffectのビジュアル一覧を作成します。
ワードアートを作成するには、 AddTextEffectメソッドを使います。 ...>続きを読む

続きを読む "カテゴリー「Excel VBA 図形」の記事一覧"

カテゴリー「Excel VBA 文字列」の記事一覧

大文字/小文字変換，全角/半角変換，ひらがな/カタカナ変換

StrConv関数を使うと
アルファベットの大文字/小文字変換,
全角/半角変換，
ひらがな/カタカナ変換ができます。

使い方は
1つ目の引数に"文字列"，
2つ目の引数に"変換の種類"を指定します。

"変換の種類"の定数は以下になります。...>続きを読む

複数個の文字列を一括で置換する

Excelで置換をするには
ショートカットキーCtrl+Fで表示されるウィンドウで
検索する文字列と置換後の文字列を指定し，
[置換]をクリックします。...>続きを読む

文字列を任意の文字数以下になるようコンマで改行する

以下の2記事でセル内の文字列を改行する方法を書きました。
「セル内の文字列を改行する」...>続きを読む

セル内の文字列をコンマで改行する

1つ前の記事「ExcelVBAでセル内での改行」では
5文字ごとに改行するコードを紹介しました。
今回は、コンマで改行する方法を考えてみます。 ...>続きを読む

セル内の文字列を改行する

セル内に長い文字列が入力されているとき
改行しないとセルの横幅が長くなります。
横幅が長すぎると見難いので
セル内で改行して横幅を小さくします。

手入力でのセル内の改行は
「Alt」+「Enter」で出来ます。 ...>続きを読む

文字列を逆転させるStrRevers関数

以前の記事『 回文判定マクロ 』で
文字列を反対から並べるという操作を
For文を使ってやりました。

その時は探しもしなかったのですが、
それ用の関数がありました。 ...>続きを読む

ReplaceメソッドとReplace関数

Replaceメソッドは以前の記事
『 セル内の改行を削除する 』において使用しました。 ...>続きを読む

回文判定マクロ

回文とは、
前から読んでも後ろから読んでも同じ文のことです。

Wikipediaによると日本語の回文では
濁音、半濁音、促音、拗音は
清音と同一として考えることが多いようですが
まずはこれらを別物とした回文判定をしてみます。 ...>続きを読む

セル内の改行を削除する

セル内の改行を削除するには、
記事「 ダブルクォーテーションをシングルクォーテーションに変換する 」
内で、
ダブルクォーテーションをシングルクォーテーションに変換したときにも使った
Replace関数を使いま...>続きを読む

文字コードを調べる

Chr関数などで使う文字コードは

VBAのヘルプの[質問]タブを開いて
「ASCII」で検索すると、検索結果に

ASCII 文字セット (0 - 127)
ASCII 文字セット (128 - 255)

の頁があるのでこの頁で調べます。 ...>続きを読む

セル内で改行、マクロ自動記録では…

Excelでやっているいろいろな手動での操作を
VBAで実行したいとき、
マクロの自動記録をして調べたりします。

他ごとでマクロ自動記録をしていたら
コメント内で改行したとき
改行がChr(10)で記録されていました。 ...>続きを読む

ダブルクォーテーションをシングルクォーテーションに置換する

ダブルクォーテーションを
シングルクォーテーションに置換したい場面は
どういう時でしょうか？

例えば…
HTMLでリンクを作るにはこういうタグを使います。 ...>続きを読む

Excel VBAでセル内での改行

手動でセルを選択、アクティブにして
セルに入力する時に、
セル内で改行したいと思って
｛Enter｝を押す。
すると、セル内で改行できずに
下のセルに移動してしまいます。

手動の入力でセル内で改行する時は

Alt + {Enter}

で改行します。 ...>続きを読む

Chr関数に使いそうなASCIIコードの表

使い方は、
Chr関数の方を文字列に＆で加える。
例えば、

"今から改行します。" & Chr(10) & "改行しました。"

...>続きを読む

改行って改行と言わないのねVBAでは

VBAヘルプ「Chr関数」頁より

ASCII コード 0 ～ 31 の範囲の文字は表示できません。この中には次に示す制御文字が含まれています。これらを利用すると MsgBox 関数や InputBox 関数などを使ってメッセージを表示するときに、文字列の中にタブや改行を含めることができます。

Chr(9)タブ...>続きを読む

”（ダブルクォーテーション）を文字列に入れる

Excel VBAでHTMLソースコードを生成しようとしたところ
”（ダブルクォーテーション）を文字列に入れる必要が発生。

VBAでは、文字列はダブルクォーテーションで挟みます。...>続きを読む

続きを読む "カテゴリー「Excel VBA 文字列」の記事一覧"

カテゴリー「Excel VBA 日時」の記事一覧

今日という日付の入力方法いろいろ

Excelを使っていて
入力した日付を記録する機会は多いと思います。
いちいち手入力するのは手間ですね。

以下に、
今日の日付を入力する方法を挙げていきます。 ...>続きを読む

1/10秒や1/100秒の時間計算

1/10秒や1/100秒単位の時間の計算をしようとしたら
案外難しいという話です。

セルに直接入力する方法では、 ...>続きを読む

一定時間だけ実行する

開始から10分だけ実行したい場合など
一定時間だけ実行したいとき、
Do Loop ステートメントや
IfステートメントとGotoステートメントの組み合わせを使います。 ...>続きを読む

時差の計算

時差の計算なんて言うと
中学の地理の授業を思い出します。

調べてみると日本では、
以前はGMT(グリニッジ標準時刻)が採用されていて、
現在はUTC(協定世界時)が採用されているということです。 ...>続きを読む

OnTimeでの設定を解除する

繰り返し実行するのには便利なOnTimeメソッドですが、
いろいろと不明な部分もあります。

OnTimeで繰り返しを始めたかを忘れてしまって
2回目の繰り返しを始めてしまうことがあります。
結果パラレルワールドです。 ...>続きを読む

日付か時刻かの判別2

前の記事はこちら
『 日付か時刻かの判別』

さて前回の日付か時刻かの判別から少し進んで
24:00以上の時刻も加えて判別します。 ...>続きを読む

日付か時刻かの判別

Excelでは日付でも時刻でもDate型で扱われ、
型だけでは日付か時刻かの判別がつきません。

そこで日付の長整数型の値を使って
日付か時刻かの判別をしてみます。

というのも

あるWebサイトから
情報をExcelで取り込み加工したいときに
そのサイトでは日付と時刻が別々に表示されていました。 ...>続きを読む

MicroTimerを使って処理時間を計測する

ずいぶん前の記事で
MicroTimerについて触れました。

今回はこれを使って
処理時間を計測してみたいと思います。 ...>続きを読む

1時間の内で15分ごとに実行する

プロシージャを繰り返し実行する為に
OnTimeメソッドを使った方法を
記事「 一定時間ごとに繰り返し実行する 」で取り上げました。 ...>続きを読む

Timer 関数を使って処理時間を計測する

Timer 関数を使って
プロシージャや一連の処理にかかる実行時間を計測したいと思います。

Timer 関数は正午からの経過時間を秒で返します。
Windows では小数点以下も返しますが、
Mac では返しません。
ですので Windows のみミリ秒単位での計測ができます。 ...>続きを読む

24時間以上を表す文字列をDate型に変換する

24時間以上を表す文字列とは、
"28:00"とか"32:12"などの文字列のことです。

これをDate型に変換するために
CDate関数を使って

CDate("28:00")

とするとエラーになります。
(実行時エラー'13':型が一致しません。) ...>続きを読む

時間帯で条件分岐

現在の時刻は

Time

で取得できます。

条件分岐にはIfステートメントを使います。

例えば、７時以降なら実行したい処理がある場合、

If "7:00" < Time Then
…
End If ...>続きを読む

一定時間ごとに繰り返し実行する

一定時間ごとに繰り返し実行する方法は
いろいろあると思います。

OnTimeメソッドを使った方法を考えてみます。
OnTimeメソッドの使い方は

OnTime "実行したい時刻", "実行したいプロシージャの名前" ...>続きを読む

マクロ実行中に一定時間ごとにブックを保存

時間の掛かるマクロを実行中は
ブックを定期的に
キリのいいところで保存していきたいです。

途中で強制終了して
また初めからなんてことがあります。

されど、ブックの保存は時間が掛かるし、
経験的にブックの開閉や保存のときには
強制終了が起こる確率が高いので
あまり頻繁にはブックを保存したくありません。 ...>続きを読む

特定の曜日を判定してセルに色を付ける

記事「任意の日付から任意の日付までを一列にセル に入れる 」で、
日付を上から順に一列に入れました。 ...>続きを読む

任意の日付から任意の日付までを一列にセルに入れる

２つの任意の日を早い順からそれぞれ
DAY1、DAY2とします。

DAY2 - DAY1 + 1

で2つの日付の間の日数がわかります。
これをSPANとします。
つまり、

SPAN = DAY2 - DAY1 + 1 ...>続きを読む

セルの書式設定で日本語の曜日を表示させる

セルの書式設定で表示形式を変更するには
変更したい範囲を選んで
メニューの[書式]-[セル]で
セルの書式設定のダイアログを表示させます。...>続きを読む

日時と時間

ｘ月ｘ日ｘｘ時ｘｘ分から
o月o日oo時oo分までの時間の「量」を計算したい時について ...>続きを読む

続きを読む "カテゴリー「Excel VBA 日時」の記事一覧"

カテゴリー「Excel」の記事一覧

Excelが保存時にエラーや強制終了した場合の対処法

ブックの保存時にエクセルが強制終了したとき、
保存しようとしたブックが正常に保存されていないことがあります。

そのようなときの対処法をみていきます。
記憶を頼りに書いていますので
あいまいな部分があります、ご了承を。 ...>続きを読む

指数形式 E の意味

指数形式 E を知らないと、
セルの値にEの文字が出てくると
値がどんな大きさなのかさっぱりわかりません。

E の意味ですが、

小数点の移動桁数に対応する数値が E 記号の右側に表示される ...>続きを読む

Excel2000をオフラインでアップデート

いまだにExcel2000を使っている人が
どれだけいるのかわかりませんが…

Excel2000をオフラインでアップデートする方法
Excel2000をハードディスクからアップデートする方法
Excel2000をダウンロードしてアップデートする方法

いろいろ表現できますが（何が正しいかわかりません）
こんな感じのことです。...>続きを読む

続きを読む "カテゴリー「Excel」の記事一覧"

[読み取り専用を推奨する]設定

デフォルトの設定ではブックは編集可能な状態で開かれます。
ブックの内容を見たいだけの場合，
誤って上書き保存しないようないに
ブックを開く際に読み取り専用で開くか選ぶことができる
[読み取り専用を推奨する]という設定があります。

手動でブックを[読み取り専用を推奨する]で保存するには
次のようにします。
[ファイル]-[名前を付けて保存]-[参照]をクリックすると
次のウィンドウが開きます。

[ツール]-[全般オプション]をクリックし，
[読み取り専用を推奨する]にチェックを入れます。

あとは保存先・ファイル名を指定して[保存]をクリックします。

VBAで[読み取り専用を推奨する]設定で保存するには
SaveAsメソッドの引数ReadOnlyRecommendedをTrueに指定します。

次のコードはブックを新規作成して
[読み取り専用を推奨する]設定で保存して閉じます。
保存先は変数path，ファイル名は変数f_nameで指定します。

Sub macro20200521a()
'[読み取り専用を推奨する]で保存

    Dim f_path As String
    Dim f_name As String
    f_path = "C:\Users\username\Documents\yamuemuVBA"
    f_name = "Book.xlsx"
   
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=f_path & "\" & f_name, _
        FileFormat:=xlOpenXMLWorkbook, ReadOnlyRecommended:=True
    ActiveWorkbook.Close

End Sub

上のコードで作成されたブックを開くと
次のようなアラートが表示されます。

[はい]をクリックすると読み取り専用で開きます。
[いいえ]で編集できる状態で開きます。
[キャンセル]でブックを開くことを中止します。

VBAで
[読み取り専用を推奨する]で保存したブックを
開く方法を見ていきます。
次のコードを実行するとブックは開きますが
アラートが表示されコードが中断されます。

Sub macro20200521b()
'[読み取り専用を推奨する]で保存したブックを開く
'アラートが表示されてコードが中断される

    Dim f_path As String
    Dim f_name As String
    f_path = "C:\Users\username\Documents\yamuemuVBA"
    f_name = "Book.xlsx"
   
    Workbooks.Open Filename:=f_path & "\" & f_name

End Sub

読み取り専用で開く場合に
アラートが出ないようにするには
Openメソッドの引数ReadOnlyをTrueにします。
この指定をすると
[読み取り専用を推奨する]設定ではないブックも
読み取り専用で開くことができます。

Sub macro20200521c()
'[読み取り専用を推奨する]で保存したブックを開く
'読み取り専用で開く

    Dim f_path As String
    Dim f_name As String
    f_path = "C:\Users\username\Documents\yamuemuVBA"
    f_name = "Book.xlsx"
   
    Workbooks.Open Filename:=f_path & "\" & f_name, _
        ReadOnly:=True

End Sub

上記コードで開かれたブックは
ウィンドウのタイトルの部分に[読み取り専用]と表示されます。

[読み取り専用を推奨する]で保存したブックを
編集可能な状態で開くには次のようにします。

Sub macro20200521d()
'[読み取り専用を推奨する]で保存したブックを開く
'編集可能状態で開く
   
    Dim f_path As String
    Dim f_name As String
    f_path = "C:\Users\username\Documents\yamuemuVBA"
    f_name = "Book.xlsx"
   
    Workbooks.Open Filename:=f_path & "\" & f_name, _
        ignorereadonlyrecommended:=True

End Sub

ちなみにアラートを表示させないようにして
[読み取り専用を推奨する]で保存したブックを開くと
読み取り専用で開かれます。

Sub macro20200521e()
'[読み取り専用を推奨する]で保存したブックを開く
'読み取り専用で開く

    Dim f_path As String
    Dim f_name As String
    f_path = "C:\Users\username\Documents\yamuemuVBA"
    f_name = "Book.xlsx"
   
    Application.DisplayAlerts = False
    Workbooks.Open Filename:=f_path & "\" & f_name
    Application.DisplayAlerts = True

End Sub

使用Ver：Win10, Excel For Office365

続きを読む "[読み取り専用を推奨する]設定"

セルの結合を解除して結合されていた全セルに同じ値を入力する

セルの結合をしていると
並び替えができません。

並び替えをするために
セルの結合を解除すると
左上のセルのみに値が設定され
その他のセルは空白になってしまいます。

セルの内容によって
並び替えをしたい場合は
結合解除で空白になると適切に並び替えができません。

VBAで
結合を解除したときに
結合されていた全セルに結合時の値を設定できるようにします。

次のコードは選択範囲内の結合を解除して
結合していた全セルに同じ値を入力します。

Sub macro20200517a()
'セルの結合を解除したときに
'すべてのセルに同じ値を入れる

    Dim Str As String
    Dim c As Range
    Dim rng As Range
   
    For Each c In Selection
        If c.MergeCells = True Then
            Str = c.FormulaR1C1
            Set rng = c.MergeArea
            c.UnMerge
            rng.Cells = Str
        End If
    Next c

End Sub

次のコードは
上記のマクロで結合を解除したセルを再結合します。

Sub macro20200517b()
'隣り合う同じ値のセルを結合する

    Dim Str As String
    Dim c As Range
    Dim rng As Range
    Dim i As Integer, j As Integer
   
    For Each c In Selection
        i = 0
        j = 0
        If c <> "" Then
            Do While c = c.Offset(i + 1, 0)
                i = i + 1
            Loop
            Do While c = c.Offset(0, j + 1)
                j = j + 1
            Loop
            Application.DisplayAlerts = False
            Range(c, c.Offset(i, j)).Merge
            Application.DisplayAlerts = True
        End If
    Next c

End Sub

使用Ver：Win10, Excel For Office365

続きを読む "セルの結合を解除して結合されていた全セルに同じ値を入力する"

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

VBAでハイパーリンクを設定するには
Hyperlinks.Addメソッドを使用します。
Addメソッドには次の5つの引数を指定します。

引数	説明
Anchor	リンクを付けたいセルを指定
Address	他のブック，ファイル，URLを指定
SubAddress	同じブック内のシート，セル位置を指定
TextToDisplay	リンクを付けたセルに表示される文字列を指定
ScreenTip	リンクの上にマウスがのったときに 表示される文字列を指定

VBAで
セルにリンクを設定する方法について
リンク先の種類別に以下の7パターンを見ていきます。

1. 同じシート内のセル
2. 同じブック内の別のシートのセル
3. 別のブック
4. 別のブックの特定セル
5. URL
6. メールアドレス
7. メールアドレス(件名付き)

次のコードは上記7パターンのリンクを作成します。

Sub macro20200516a()
'セルにハイパーリンクを設定する
   
    Sheets.Add
   
    '1.同じシート内のセルへのリンク作成
    ActiveSheet.Hyperlinks.Add _
        Anchor:=Cells(1, 1), _
        Address:="", _
        SubAddress:="A100", _
        TextToDisplay:="A100", _
        ScreenTip:="同じシート内のセルへのリンク"
       
    '2.同じブック内のセルへのリンク作成
    ActiveSheet.Hyperlinks.Add _
        Anchor:=Cells(2, 1), _
        Address:="", _
        SubAddress:="Sheet4!A1", _
        TextToDisplay:="Sheet4!A1", _
        ScreenTip:="同じブックの別シート内のセルへのリンク"
   
    '3.別のブックへのリンク作成
    ActiveSheet.Hyperlinks.Add _
        Anchor:=Cells(3, 1), _
        Address:="C:\Users\username\Documents\Book1.xlsx", _
        TextToDisplay:="C:\Users\username\Documents\Book1.xlsx", _
        ScreenTip:="別のブックへのリンク"
   
    '4.別のブックの特定セルへのリンク作成
    ActiveSheet.Hyperlinks.Add _
        Anchor:=Cells(4, 1), _
        Address:="C:\Users\username\Documents\Book1.xlsx", _
        SubAddress:="Sheet1!A100", _
        TextToDisplay:="C:\Users\username\Documents\Book1.xlsx!Sheet1!A100", _
        ScreenTip:="別のブックの特定セルへのリンク"
   
    '5.URLへのリンク作成
    ActiveSheet.Hyperlinks.Add _
        Anchor:=Cells(5, 1), _
        Address:="https://www.google.co.jp", _
        TextToDisplay:="google", _
        ScreenTip:="URLへのリンク"
   
    '6.メールアドレスのリンク作成
    ActiveSheet.Hyperlinks.Add _
        Anchor:=Cells(6, 1), _
        Address:="mailto:aaa@aaa.co.jp", _
        TextToDisplay:="mailto:aaa@aaa.co.jp", _
        ScreenTip:="メールアドレスのリンク"
   
    '7.メールアドレス(件名付き)のリンク作成
    ActiveSheet.Hyperlinks.Add _
        Anchor:=Cells(7, 1), _
        Address:="mailto:aaa@aaa.co.jp?subject=件名", _
        TextToDisplay:="mailto:aaa@aaa.co.jp?subject=件名", _
        ScreenTip:="メールアドレス(件名付き)のリンク"

End Sub

実行結果：

使用Ver：Win10, Excel For Office365

続きを読む "セルにハイパーリンクを設定する"

セルに移動するリンクを付けた図形を作成する

例えば，
Excelに貼り付けた画像の部分をクリックすると
特定のセルに移動させたい場合，
画像上に図形を配置して
図形にハイパーリンクを付けることで実現できます。

このようにすると画像の部分と
セルを関連付けることができます。

図形の数が数個であれば
手動で図形を作成することも簡単ですが
数十個となれば作成するのは手間です。
VBAであれば一瞬でできます。

VBAで
セルに移動するリンクを貼った図形を
作成する方法を見ていきます。

1～47のナンバーが入力されている
次の画像のシートがあるとします。

A列の各セルの内容を文字として表示して
セルに移動するリンクを追加した図形を作成します。
図形の種類は円です。
作成した図形を日本地図の画像上に配置して
地図からセルへ移動できるようにします。

Sub macro20200514a()
'セルに移動するリンクを付けた図形を作成する

    Dim i As Integer
   
    For i = 1 To Cells(1, 1).End(xlDown).Row
        ActiveSheet.Shapes.AddShape(msoShapeOval, _
            200 + 14 * (i Mod 10), 10 + 14 * Int(i / 10), _
            14, 14).Select
       
        'テキストの設定
        Selection.ShapeRange(1).TextFrame2.TextRange. _
            Characters.Text = Cells(i, 1)
       
        '図形の塗りの設定
        With Selection.ShapeRange.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 255, 255)
            .Solid
        End With
       
        '図形の線の設定
        With Selection.ShapeRange.Line
            .Visible = msoTrue
            .Weight = 0.75
            .ForeColor.RGB = RGB(0, 0, 0)
        End With
       
        '文字の塗りの設定
        With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
            .Visible = msoTrue
            .Solid
            .ForeColor.RGB = RGB(0, 0, 0)
        End With
       
        '余白，文字揃えの設定
        With Selection.ShapeRange.TextFrame2
            .MarginLeft = 0
            .MarginRight = 0
            .MarginTop = 0
            .MarginBottom = 0
            .VerticalAnchor = msoAnchorMiddle
            .HorizontalAnchor = msoAnchorCenter
            .WordWrap = msoFalse
        End With
       
        'フォントの設定
        With Selection.ShapeRange.TextFrame2.TextRange.Font
            .Name = "ＭＳ Ｐゴシック"
        End With
       
        'リンクの追加
        ActiveSheet.Hyperlinks.Add _
            Anchor:=Selection.ShapeRange.Item(1), _
            Address:="", _
            SubAddress:=ActiveSheet.Name & "!" & Cells(i, 1).Address
    Next i

End Sub

実行結果：

リンクを貼り付けた図形が作成されたので
これを日本地図上に配置します。

リンクを貼った図形は選択しようとすると
リンク先へ移動してしまうので
[ホーム]-[検索と選択]-[オブジェクトの選択]をクリックして
オブジェクトのみ選択できるようにしてから
図形を移動させます。

図形を日本地図上に配置すると次のようになります。

数字をクリックするとセルに移動します。

使用Ver：Win10, Excel For Office365

続きを読む "セルに移動するリンクを付けた図形を作成する"

数式かどうか判定する

セルに数値が直接入力されているか，
数式の計算結果の値なのか，
セルに表示されている値のみで判断できません。

他のセルを参照するべきところに
直接 数値が入力されている場合，
参照元セルが変更されても
変更が反映されず問題になることがあります。
このような問題が起こらないために
セルに数式が入力されているか確認したい場合があります。

VBAで
数式かどうか判定するには
HasFormulaプロパティを使います。

次のコードは
選択範囲の各セルの内容が
数式であれば太字にして
見た目で区別できるようにしていきます。

Sub macro20200513a()
'数式かどうか判定する
'範囲：選択しているセル
    Dim c As Range
   
    For Each c In Selection
        If c.HasFormula = True Then
            c.Font.Bold = True
        End If
    Next c

End Sub

実行結果：

＝で始まる数式が入力されているセルが太字になりました。

使用Ver：Win10, Excel For Office365

続きを読む "数式かどうか判定する"

セルが空白か判別して斜線を設定/塗りをグレイにする

表を作成していると
内容を記入する必要がない空白のセルが存在することもあります。

空白のままだと表全体が見難いので
斜線をつけたり，セルの塗りをグレイにしたります。

これをVBAで実行したいと思います。

空白かどうかの判定は単純に
セルの値が""に等しいかの比較でします。

次のコードは選択範囲内のセルが空白の場合に
セルの罫線に斜線を追加します。

コード実行前に範囲を選択しておいてください。

Sub macro20200512a()
'空白の場合，斜線を設定する
'範囲は選択しているセル

    Dim c As Range
   
    For Each c In Selection
        If c.Value = "" Then
            With c.Borders(xlDiagonalUp)
                .LineStyle = xlContinuous
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
                .Weight = xlThin
            End With
        End If
    Next c

End Sub

実行結果(一例)：

右下がりの斜線にしたい場合は，
「xlDiagonalUp」部分を「xlDiagonalDown」に変更してください。

次のコードは選択範囲のセルが空白の場合に
セルの塗りつぶしの色を灰色にします。

Sub macro20200512b()
'空白の場合，塗りを灰色にする
'範囲は選択しているセル

    Dim c As Range
   
    For Each c In Selection
        If c.Value = "" Then
            With c.Interior
                .Pattern = xlSolid
                .Color = RGB(150, 150, 150)
            End With
        End If
    Next c

End Sub

実行結果(一例)：

グレイの濃さを変えたい場合は
RGB関数の引数の値を変更します。

グレイの場合は3つの引数に同じ値を指定します。
上限は250です。

上記マクロでは150を指定しています。
薄くしたい場合は引数150の値をより大きく，
濃くしたい場合は引数150の値をより小さくしてください。

使用Ver：Win10, Excel For Office365

続きを読む "セルが空白か判別して斜線を設定/塗りをグレイにする"

為替の時系列データの取り込み(データ更新)

記事「為替の時系列データの取り込み」で
日ごとの為替データ(USD/JPY,EUR/JPY,AUD/JPY)を取得しました。

今回は上記記事で作成したクエリを更新して
最新データを取得していきます。

本記事のマクロを実行する前に
記事「為替の時系列データの取り込み」の内容を
上から順に実行しておいてください。

今回のコードの内容は基本的には
上記記事と同じです。

異なる点を説明します。
前回は最新から一番古いデータまでを
クエリを更新しながら順番に取得していきました。

今回は最新から保存されている一番新しい日付の前までの
データを取得します。
そのために
保存されている一番新しい日付を変数l_dateに格納します。

クエリを更新して
クエリのデータの日付と変数l_dateを比較して
l_dateより大きい日付のデータのみ
各通貨のシートにコピーしていきます。

コピーする際に
前回はデータを一番下にコピーしましたが
今回はデータの一番上の4行目に挿入します。

次のコードは
「通貨ペア」のシートに入力された通貨ペアの
時系列データを更新します。
マクロmacro20200511bの方を実行してください。

Sub macro20200511b()
'「通貨ペア」のシートに入力された通貨ペアの
'為替時系列データ取り込み(更新)

    Dim i As Integer
    Dim end_row As Integer
    Dim cur_str As String
    Dim sh As Object
   
    Set sh = Sheets("通貨ペア")
    end_row = sh.Cells(1, 1).End(xlDown).Row
   
    For i = 2 To end_row
        cur_str = sh.Cells(i, 1)
        Call macro20200511a(cur_str)
    Next i

End Sub
Sub macro20200511a(cur_str As String)
'為替時系列データ取り込み(更新)

    Dim i As Integer, j As Integer
    Dim end_row As Integer
    Dim end_row_q As Integer
    Dim cur_url1 As String, cur_url2 As String
    Dim sh1 As Object
    Dim rng_c As Range, rng_p As Range
    Dim de
    Dim l_date As Date
   
    Set sh1 = Sheets(cur_str)
    l_date = sh1.Cells(4, 1)
   
    cur_url1 = "https://info.finance.yahoo.co.jp/history/?code="
    cur_url2 = "%3DX&sy=1983&sm=1&sd=1&ey=" & Year(Date) & _
        "&em=" & Month(Date) & _
        "&ed=" & Day(Date) & "&tm=d"
   
    For i = 1 To 700
        Debug.Print i & "ページ目取り込み中"
       
        'Webクエリを更新
        With Sheets("為替クエリ").QueryTables("為替")
            .Connection = "URL;" & cur_url1 & cur_str & cur_url2 & "&p=" & i
           
            .BackgroundQuery = False
            .Refresh
        End With
       
        'Webクエリ更新の終了判断
        If Sheets("為替クエリ").Cells(4, 1) = "" Then
            Exit For
        End If
       
        'データをコピー
        end_row_q = 0
        For j = 4 To 23
            If IsDate(Sheets("為替クエリ").Cells(j, 1)) Then
                If Sheets("為替クエリ").Cells(j, 1) > l_date Then
                    end_row_q = j
                Else
                    Exit For
                End If
            Else
                Exit For
            End If
        Next j
       
        If end_row_q = 0 Then
            Debug.Print cur_str & ":最新データに更新完了"
            Exit Sub
        End If
       
        Set rng_c = Sheets("為替クエリ").Range("A4:E" & end_row_q)
        Set rng_p = sh1.Range(sh1.Cells(4, 1), sh1.Cells(end_row_q, 4))
       
        rng_c.Copy
        rng_p.Insert (xlShiftDown)
        sh1.Cells.EntireColumn.AutoFit
       
        'Escキーで中断できるようにDoEventsを挟む
        If i Mod 5 = 0 Then
            de = DoEvents
        End If

    Next i
   
End Sub

実行結果：

前回は2020年5月5日まで取得していたので
赤枠内の5月6日から8日までのデータが追加で取得できました。

使用Ver：Win10, Excel For Office365

続きを読む "為替の時系列データの取り込み(データ更新)"

フォルダ内のファイル情報のリスト化

指定したフォルダに含まれるファイルの情報を得るには
FileSystemObjectオブジェクトを使用します。
FileSystemObjectオブジェクトは次のコードで作成します。

Set fs = CreateObject("Scripting.FileSystemObject")

変数fsに対してGetFolderメソッドを使用して
フォルダオブジェクトを変数fs_fldに格納します。

Set fs_fld = fs.GetFolder("フォルダの場所を指定")

変数fs_fldにはフォルダ内のすべてのファイルが含まれます。

次のコードでフォルダ内のすべてのファイルオブジェクトを
変数fs_fに格納します。

Set fs_f = fs_fld.Files

複数のファイルが格納された変数fs_fに対して
For Such文を使用して
各ファイルの情報を取得していきます。

取得できるファイルの情報については
次のMicrosoftのページのプロパティを参照してください。
File オブジェクト

次のコードは
変数f_pathで指定されたフォルダ内の
ファイル情報をリスト化します。

Sub macro20200510a()
'指定フォルダのファイル情報のリスト化する

    Dim fs, fs_fld, fs_f, f
    Dim f_path As String
    Dim i As Integer
    f_path = "C:\Users\username\Documents\"
   
    f_path = f_path & "\"
    i = 1
   
    Sheets.Add
    Cells(1, 1) = "フォルダ内ファイルのリスト化"
    Cells(2, 1) = "パス：" & f_path
   
    Cells(4, 1) = "Attributes"
    Cells(4, 2) = "DateCreated"
    Cells(4, 3) = "DateLastAccessed"
    Cells(4, 4) = "DateLastModified"
    Cells(4, 5) = "Drive"
    Cells(4, 6) = "Name"
    Cells(4, 7) = "ParentFolder"
    Cells(4, 8) = "Path"
    Cells(4, 9) = "ShortName"
    Cells(4, 10) = "ShortPath"
    Cells(4, 11) = "Size"
    Cells(4, 12) = "Type"
   
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set fs_fld = fs.GetFolder(f_path)
    Set fs_f = fs_fld.Files
    For Each f In fs_f
        Cells(i + 4, 1) = f.Attributes
        Cells(i + 4, 2) = f.DateCreated
        Cells(i + 4, 3) = f.DateLastAccessed
        Cells(i + 4, 4) = f.DateLastModified
        Cells(i + 4, 5) = f.Drive
        Cells(i + 4, 6) = f.Name
        Cells(i + 4, 7) = f.ParentFolder
        Cells(i + 4, 8) = f.Path
        Cells(i + 4, 9) = f.ShortName
        Cells(i + 4, 10) = f.ShortPath
        Cells(i + 4, 11) = f.Size
        Cells(i + 4, 12) = f.Type
       
        i = i + 1
    Next f
    Cells.EntireColumn.AutoFit
End Sub   

使用Ver：Win10, Excel For Office365

続きを読む "フォルダ内のファイル情報のリスト化"

記事一覧

カテゴリー選択
Excel
Excel VBA
Excel VBA HTML
Excel VBA VBE
Excel VBA 色
Excel VBA ウィンドウ
Excel VBA エラー
Excel VBA カレンダー
Excel VBA 関数
Excel VBA クエリ
Excel VBA シート
Excel VBA 書籍
Excel VBA セル
Excel VBA セル 結合
Excel VBA セル 装飾
Excel VBA センター試験
Excel VBA データ型
Excel VBA データ操作
Excel VBA ハイパーリンク
Excel VBA 配列
Excel VBA ファイル
Excel VBA フォームコントロール
Excel VBA メール
Excel VBA ワークブック
Excel VBA 為替・株
Excel VBA 印刷
Excel VBA 検索
Excel VBA 図形
Excel VBA 数
Excel VBA 日時
Excel VBA 文字列

●Excel

Excelが保存時にエラーや強制終了した場合の対処法
指数形式 E の意味
Excel2000をオフラインでアップデート

●Excel VBA

For文の使い方とテンプレート
虫食い2
Withを使うか、オブジェクト変数を使うか
虫食い
プライバシーシート2
プライバシーシート
型宣言文字とその使用方法
Openメソッドを使ってURLが存在するか確かめる
メソッド、プロシージャ、関数の呼び出しと引数指定
静的変数/Staticステートメントについて
マクロをショートカットキーに割り当てる
「より高速な VBA マクロ」について
脳トレ計算
よくある質問20100803
VBAで何ができるか？具体例１
ある文字列を含むかで条件分岐
MsgBoxの使い方とビジュアル一覧
列を表す英文字を列番号に変換
Excelは見えないところでギザ休む
マルチコアとVBA
VBA サンプル マクロを検索

●Excel VBA HTML

HTMLファイルの目次を作る2
VBAのコードをHTMLで表示できるようにする
画像一覧のHTMLファイル作成
HTML形式のファイルが文字化けする場合
HTMLファイルを生成
HTMLファイルの目次を作る
HTMLのTableをVBAで作る 4
HTMLのTableをVBAで作る 3
HTMLのTableをVBAで作る 2
HTMLのTableをVBAで作る

●Excel VBA VBE

イミディエイトウィンドウ内をクリアする
オブジェクトモジュールではデバッグモードがないのね。

●Excel VBA 色

現在のカラーパレットをセルに表示して確認する
任意の色とその補色
色を反転する
RGB関数を使ってセルの塗りつぶしの色を指定したとき実際に表示される色
種々のカラーパレット 5
種々のカラーパレット 4
種々のカラーパレット 3
種々のカラーパレット 2
種々のカラーパレット
カラーパレットを変更する
色の表示形式の変換 3
色の表示形式の変換 2
色の表示形式の変換 １
Excelで表示できる色はいったい何色？
VBAにおいてRGB関数で色を指定する

●Excel VBA ウィンドウ

ウィンドウの分割とウィンドウ枠の固定
意図したセルを画面の可視範囲に表示

●Excel VBA エラー

あるあるエラー：シートをオブジェクト変数に入れる
あるあるエラー：オブジェクト変数
エラーを無視してエラーを使う
2度目のエラーは捕えない
あるあるエラー：Webクエリ
あるあるエラー：Integer型
くだらなすぎる間違い「s」

●Excel VBA カレンダー

iCalendar形式のファイルをExcelで生成する2
iCalendar形式のファイルをExcelで生成する1
年間予定表を作成する
月間カレンダー、祝日あり
祝日を年間カレンダーに追加する
任意の年の祝日を取得する

●Excel VBA 関数

ワークシート関数をVBAで使う

●Excel VBA クエリ

WebクエリでGoogleの検索結果を取得する
Webクエリを使ってURLが存在するか確かめる
Webクエリまとめ
Webクエリを秒単位で更新する

●Excel VBA シート

SheetsとWorksheets
シートを移動して並び替える2
シートを移動して並び替える1
シートの指定方法のまとめ
シートの並び替えをする
シート名を検索、条件を満たしたシートを削除
シート挿入あれこれ 3
シート挿入あれこれ 2
シート挿入あれこれ
指定したシートがあるか確認してからそのシートを削除する
VBAで警告なしにシートを削除する

●Excel VBA 書籍

『Excel VBAスタンダード　VBAエキスパート』
『Excel VBAベーシック　VBAエキスパート』
『Excel VBAマスターブック 2003&2002対応 WindowsXP版』
『Excel VBA WEB連携術』
『Microsoft Excel 2000 Power Programming with VBA』
井領 邦弘 (著)：株解析チャートから自動発注ロボットまで!Excel VBAで極
田沼晴彦：『Excelで遊ぶ手作り数学シュミレーション』

●Excel VBA セル

数式かどうか判定する
セルが空白か判別して斜線を設定/塗りをグレイにする
RangeオブジェクトにCellsプロパティを使う
結合したセルの高さを自動調整する
セルに入力した文字をはみ出して表示させる
セルの内容を90度回転、-90度回転、180度回転
セルの高さの自動変更をなくす
Range オブジェクトのOffset プロパティの使い方
秒の小数部分の表示形式
Format関数を使ったのに違う表示形式が自動で適用される件
数式を値に変換する2
Copy、Pasteメソッドを使わないコピペ
数式を値に変換する
[書式設定]をクリアするときの不便を解消
別のシートのセルと同期する
コピーしてコピーしたセルを挿入する
列番号を英文字に変換してRangeプロパティに使う
VBAでセルの高さと幅を自動調節する
範囲指定のまとめ
削除とクリア
ピクセル単位でセルの高さと幅を指定する
Cellsの解剖
セルの高さと幅の設定
セルの縦と横の比
R1C1形式をA1形式に変換する
手動でよくやるコピペ

●Excel VBA セル 結合

セルの結合を解除して結合されていた全セルに同じ値を入力する

●Excel VBA セル 装飾

セルの枠線を非表示にする
値によってセルを色分けする
フォント名一覧とその見本を作成
Patternの見本
LineStyleの見本

●Excel VBA センター試験

2011年センター試験、数学プログラム問題
センター試験数学のプログラム問題をVBAで

●Excel VBA データ型

オブジェクトがNothingかどうか調べる

●Excel VBA データ操作

重複行を削除する
列と列を入れ替える
列の「並び替え」
ランダムに並び替える
行と列を入れ替える

●Excel VBA ハイパーリンク

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

●Excel VBA 配列

2次元配列の行列を入れ替える
2次元配列をランダムに並び換える
2次元配列をソートする
配列をソートする
セルの値を配列に格納して使う
2次元配列の使い方
配列の使い方を整理
特定の文字で区切られた文字列を分割して配列にいれる

●Excel VBA ファイル

フォルダ内のファイル情報のリスト化
VBAでファイルの存在を確認してから開く
ファイルの名前を変更する
VBAでファイルを削除する
ワイルドカードで検索してファイル名を取得

●Excel VBA フォームコントロール

VBAでコマンドボタンを作成
ボタンに画像を使用する
昇順/降順の並び替えをするボタンを作成
ボタンを使ってマクロを実行する

●Excel VBA メール

確実性を増した簡易メール送信
簡易メール送信 ２の補足
簡易メール送信 ２
簡易メール送信

●Excel VBA ワークブック

ワークブックのパスを取得する
ワークブックをアクティブにする
既存xls形式ブックをxlsx、xlsm形式で保存し直す
ワークブックを新規作成、名前をつけて保存、閉じる
使用中のファイルを開く、開かない
ブックが開いているか確認してから開く
バックアップいろいろ
ファイル保存時に自動でコピーを作って保存

●Excel VBA 為替・株

為替の時系列データの取り込み(データ更新)
為替の時系列データの取り込み
ゆる～いリアルタイム為替レートのリソース

●Excel VBA 印刷

印刷範囲の自動設定
改ページの追加・削除・位置取得
ページ設定をコピーする
先頭ページ，奇数・偶数ページのヘッダー・フッターの設定
複数ページにまたがる表を印刷する時に，表タイトルを各ページに表示させる
ヘッダー・フッターに画像を表示させる
ヘッダー・フッターにセルの値を使用する
ヘッダー・フッターのフォント設定
ヘッダー・フッターの設定
ページ設定の自動化

●Excel VBA 検索

複数のキーワードを含むセルを検索する

●Excel VBA 図形

セルに移動するリンクを付けた図形を作成する
セル内容をもとにPasteメソッドで文字列の図形を作成する
図形のビジュアル一覧を作成する
ShapeをTypeごとに扱う
Shapeを指定して削除する
テキストボックスを作成する2
テキストボックスを作成する
ActiveSheetのすべてのShapeを選択
PresetTextEffectのビジュアル一覧

●Excel VBA 数

ピタゴラス数
最大と最小
0以下は0に、その他はそのままにする
友愛数
素数を調べる
商と余り
正多角形の内角の組合せ
任意の整数から任意の整数までの乱数

●Excel VBA 日時

今日という日付の入力方法いろいろ
1/10秒や1/100秒の時間計算
一定時間だけ実行する
時差の計算
OnTimeでの設定を解除する
日付か時刻かの判別2
日付か時刻かの判別
MicroTimerを使って処理時間を計測する
1時間の内で15分ごとに実行する
Timer 関数を使って処理時間を計測する
24時間以上を表す文字列をDate型に変換する
時間帯で条件分岐
一定時間ごとに繰り返し実行する
マクロ実行中に一定時間ごとにブックを保存
特定の曜日を判定してセルに色を付ける
任意の日付から任意の日付までを一列にセルに入れる
セルの書式設定で日本語の曜日を表示させる
日時と時間

●Excel VBA 文字列

大文字/小文字変換，全角/半角変換，ひらがな/カタカナ変換
複数個の文字列を一括で置換する
文字列を任意の文字数以下になるようコンマで改行する
セル内の文字列をコンマで改行する
セル内の文字列を改行する
文字列を逆転させるStrRevers関数
ReplaceメソッドとReplace関数
回文判定マクロ
セル内の改行を削除する
文字コードを調べる
セル内で改行、マクロ自動記録では…
ダブルクォーテーションをシングルクォーテーションに置換する
Excel VBAでセル内での改行
Chr関数に使いそうなASCIIコードの表
改行って改行と言わないのねVBAでは
”（ダブルクォーテーション）を文字列に入れる

続きを読む "記事一覧"

為替の時系列データの取り込み

為替の時系列データの取り込みを
Webクエリを使って行います。

データの取得先は
YAHOO!ファイナンスです。

このサイトの「株式」のページで
以下の通貨の時系列データが見れます。

USDJPY
EURJPY
AUDJPY

上記3つの通貨ペアの時系列データを取り込んでいきます。
Webクエリの基本的な使い方は次の記事を参照ください。
記事「Webクエリまとめ」

まずは，USDJPYのみのデータを取り込む方法を見ていきます。
USDJPYのデータのURLは
"https://stocks.finance.yahoo.co.jp/stocks/history/?code=USDJPY=X"です。

次のコードは
上記URLのデータを取り込むWebクエリを作成します。

Sub macro20200506a()
'為替時系列データ用Webクエリ作成
    Dim cur_url As String

    cur_url = "https://stocks.finance.yahoo.co.jp/stocks/history/?code=USDJPY=X"
   
    Sheets.Add.Name = "為替クエリ"
    With ActiveSheet.QueryTables.Add( _
        Connection:="URL;" & cur_url, _
        Destination:=Range("A1"))
       
        .Name = "為替"
        .WebFormatting = xlWebFormattingNone '書式なし
        .Refresh
    End With

End Sub

実行結果：

「為替クエリ」シートが挿入されて
Webクエリが作成されました。

次は
過去にさかのぼってデータを取得していきます。

先ほどのサイトの少し下にスクロールすると
過去データの日付の範囲を指定できます。

とりあえず一番古い日付と最新の日付を指定して
表示をクリックします。

表示されたページのURLを見てください。
1ページ目のURLは，
https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=1983&sm=1&sd=1&ey=2020&em=5&ed=6&tm=d
2ページ目のURLは，
https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=1983&sm=1&sd=1&ey=2020&em=5&ed=6&tm=d&p=2
3ページ目のURLは，
https://info.finance.yahoo.co.jp/history/?code=USDJPY%3DX&sy=1983&sm=1&sd=1&ey=2020&em=5&ed=6&tm=d&p=3

ページごとにURLで異なる部分は
最後の&p=2,&p=3,...の部分です。

先ほど作成したWebクエリのURLを
1ページ目のURL，2ページ目のURL，3ページ目のURL，...と
順に変更して
Webクエリを更新していけば過去データも取得できます。

Webクエリを更新すると更新前のデータが消えるで
データを更新したら
その時点のWebクエリのデータを別のシートにコピペします。

ページ数がわからないので
とりあえず700ページまで取り込む設定にして
データがなくなったら終了するようにします。

Sub macro20200506b()
'為替時系列データ用Webクエリ作成

    Dim i As Integer, j As Integer
    Dim end_row As Integer
    Dim end_row_q As Integer
    Dim cur_str As String
    Dim cur_url1 As String, cur_url2 As String
    Dim sh1 As Object
    Dim rng_c As Range, rng_p As Range
    Dim de
   
    cur_str = "USDJPY"
    Sheets.Add.Name = cur_str
    Set sh1 = Sheets(cur_str)
   
    With sh1
        .Cells(1, 1) = cur_str
        .Cells(3, 1) = "日付"
        .Cells(3, 2) = "始値"
        .Cells(3, 3) = "高値"
        .Cells(3, 4) = "安値"
        .Cells(3, 5) = "終値"
    End With

    cur_url1 = "https://info.finance.yahoo.co.jp/history/?code="
    cur_url2 = "%3DX&sy=1983&sm=1&sd=1&ey=" & Year(Date) & _
        "&em=" & Month(Date) & _
        "&ed=" & Day(Date) & "&tm=d"
   
    For i = 1 To 700
        Debug.Print i & "ページ目取り込み中"
       
        'Webクエリを更新
        With Sheets("為替クエリ").QueryTables("為替")
            .Connection = "URL;" & cur_url1 & cur_str & cur_url2 & "&p=" & i
           
            .BackgroundQuery = False
            .Refresh
        End With
       
        'Webクエリ更新の終了判断
        If Sheets("為替クエリ").Cells(4, 1) = "" Then
            Exit For
        End If
       
        'データをコピー
        If sh1.Cells(4, 1) = "" Then
            end_row = 4
        Else
            end_row = sh1.Cells(3, 1).End(xlDown).Row + 1
        End If

        For j = 4 To 23
            If IsDate(Sheets("為替クエリ").Cells(j, 1)) Then
                end_row_q = j
            Else
                Exit For
            End If
        Next j
       
        Set rng_c = Sheets("為替クエリ").Range("A4:E" & end_row_q)
        Set rng_p = sh1.Range(sh1.Cells(end_row, 1), sh1.Cells(end_row + 19, 4))
       
        rng_c.Copy Destination:=rng_p
        sh1.Cells.EntireColumn.AutoFit
       
        'Escキーで中断できるようにDoEventsを挟む
        If i Mod 5 = 0 Then
            de = DoEvents
        End If

    Next i
   
End Sub

実行結果：

「USDJPY」シートが挿入されて
時系列データが取り込めました。

次はUSDJPY以外の通貨ペアのデータも取り込んでいきます。
マクロmacro20200506bで定義した変数cur_strを
別の通貨ペアに変更して
実行すればデータを取り込めます。

その際に，マクロmacro20200506bを改良してmacro20200506cにとし
変数cur_strを
他のマクロmacro20200506dから引数としてもらう形式にします。

準備として
取り込む通貨ペアのリストを入力するシートを作成します。
名前「通貨ペア」のシートを挿入してください。
内容を次の画像のように入力してください。

次のコードは
「通貨ペア」のシートに入力された通貨ペアの
時系列データを取得します。
マクロmacro20200506dの方を実行してください。

Sub macro20200506d()
'「通貨ペア」のシートに入力された通貨ペアの
'為替時系列データ取り込み

    Dim i As Integer
    Dim end_row As Integer
    Dim cur_str As String
    Dim sh As Object
   
    Set sh = Sheets("通貨ペア")
    end_row = sh.Cells(1, 1).End(xlDown).Row
   
    For i = 2 To end_row
        cur_str = sh.Cells(i, 1)
        Call macro20200506c(cur_str)
    Next i

End Sub
Sub macro20200506c(cur_str As String)
'為替時系列データ取り込み

    Dim i As Integer, j As Integer
    Dim end_row As Integer
    Dim end_row_q As Integer
    Dim cur_url1 As String, cur_url2 As String
    Dim sh1 As Object
    Dim rng_c As Range, rng_p As Range
    Dim de
   
    Sheets.Add.Name = cur_str
    Set sh1 = Sheets(cur_str)
   
    With sh1
        .Cells(1, 1) = cur_str
        .Cells(3, 1) = "日付"
        .Cells(3, 2) = "始値"
        .Cells(3, 3) = "高値"
        .Cells(3, 4) = "安値"
        .Cells(3, 5) = "終値"
    End With

    cur_url1 = "https://info.finance.yahoo.co.jp/history/?code="
    cur_url2 = "%3DX&sy=1983&sm=1&sd=1&ey=" & Year(Date) & _
        "&em=" & Month(Date) & _
        "&ed=" & Day(Date) & "&tm=d"
   
    For i = 1 To 700
        Debug.Print i & "ページ目取り込み中"
       
        'Webクエリを更新
        With Sheets("為替クエリ").QueryTables("為替")
            .Connection = "URL;" & cur_url1 & cur_str & cur_url2 & "&p=" & i
           
            .BackgroundQuery = False
            .Refresh
        End With
       
        'Webクエリ更新の終了判断
        If Sheets("為替クエリ").Cells(4, 1) = "" Then
            Exit For
        End If
       
        'データをコピー
        If sh1.Cells(4, 1) = "" Then
            end_row = 4
        Else
            end_row = sh1.Cells(3, 1).End(xlDown).Row + 1
        End If

        For j = 4 To 23
            If IsDate(Sheets("為替クエリ").Cells(j, 1)) Then
                end_row_q = j
            Else
                Exit For
            End If
        Next j
       
        Set rng_c = Sheets("為替クエリ").Range("A4:E" & end_row_q)
        Set rng_p = sh1.Range(sh1.Cells(end_row, 1), sh1.Cells(end_row + 19, 4))
       
        rng_c.Copy Destination:=rng_p
        sh1.Cells.EntireColumn.AutoFit
       
        'Escキーで中断できるようにDoEventsを挟む
        If i Mod 5 = 0 Then
            de = DoEvents
        End If

    Next i
   
End Sub

データの取得には時間がかかりますので
出かける前や就寝前に実行するのを勧めます。

使用Ver：Win10, Excel For Office365

続きを読む "為替の時系列データの取り込み"

大文字/小文字変換，全角/半角変換，ひらがな/カタカナ変換

StrConv関数を使うと
アルファベットの大文字/小文字変換,
全角/半角変換，
ひらがな/カタカナ変換ができます。

使い方は
1つ目の引数に"文字列"，
2つ目の引数に"変換の種類"を指定します。

"変換の種類"の定数は以下になります。

定数	変換の種類
vbUpperCase	大文字に変換
vbLowerCase	小文字に変換
vbProperCase	単語の最初の文字を大文字に変換
vbWide	半角文字を全角文字に変換
vbNarrow	全角文字を半角文字に変換
vbKatakana	文字列に含まれるひらがなをカタカナに変換
vbHiragana	文字列のカタカナ文字をひらがなに変換

例えばアルファベットを大文字に変換したい場合は

StrConv("abc",vbUpperCase)

と指定します。
上記は"ABC"という文字列を返します。

次のコードはStrConv関数を使用して
各種変換をします。

アルファベットの半角小文字，半角大文字，
アルファベットの全角小文字，半角大文字，
ひらがな，全角カタカナ，半角カタカナが入った次の文字列
"abc ABC ａｂｃ ＡＢＣ あいう アイウ ｱｲｳ"
を変換します。

変換後の文字をセルに書き出すので
どのように変換されるか参考にしてください。

Sub macro20200505a()
'大文字/小文字変換，全角/半角変換，
' ひらがな/カタカナ変換するコード
   
    Dim i As Integer
    Dim str As String
    i = 1
    str = "abc ABC ａｂｃ ＡＢＣ あいう アイウ ｱｲｳ"
   
    Sheets.Add
    Cells(1, 1) = "変換前の文字列"
    Cells(1, 2) = str
   
    '大文字に変換
    Cells(2, 1) = "大文字に変換"
    Cells(2, 2) = StrConv(str, vbUpperCase)
    '小文字に変換
    Cells(3, 1) = "小文字に変換"
    Cells(3, 2) = StrConv(str, vbLowerCase)
    '単語の最初の文字を大文字に変換
    Cells(4, 1) = "単語の最初の文字を大文字に変換"
    Cells(4, 2) = StrConv(str, vbProperCase)
    '全角文字に変換
    Cells(5, 1) = "全角文字に変換"
    Cells(5, 2) = StrConv(str, vbWide)
    '半角文字に変換
    Cells(6, 1) = "半角文字に変換"
    Cells(6, 2) = StrConv(str, vbNarrow)
    'カタカナに変換
    Cells(7, 1) = "カタカナに変換"
    Cells(7, 2) = StrConv(str, vbKatakana)
    'ひらがなに変換
    Cells(8, 1) = "ひらがなに変換"
    Cells(8, 2) = StrConv(str, vbHiragana)
    '半角+カタカナに変換
    Cells(8, 1) = "半角+カタカナに変換"
    Cells(8, 2) = StrConv(str, vbNarrow + vbKatakana)
   
    Cells.EntireColumn.AutoFit
   
 End Sub

実行結果：

使用Ver：Win10, Excel For Office365

続きを読む "大文字/小文字変換，全角/半角変換，ひらがな/カタカナ変換"

複数個の文字列を一括で置換する

Excelで置換をするには
ショートカットキーCtrl+Fで表示されるウィンドウで
検索する文字列と置換後の文字列を指定し，
[置換]をクリックします。

複数個の文字列を置換したい場合は
1つずつ文字列を入力して置換していくことになります。

VBAで複数個の文字列を一括で置換してみます。
大まかな手順は次の通りです。
名前が"複数置換"のシートを作成して
検索する文字列と置換後の文字列を手入力する。
入力された文字列に対して
VBAで1つずつ検索・置換をしていく。

まず，検索・置換する文字列を入力するシートを作成します。
シートを追加し名前を"複数置換"にします。
このシートのA5セル以降に検索する文字列を，
B5セル以降に置換後の文字列を入力していく仕様とします。
次の画像を参考にしてください。

次のコードは
シート名"複数置換"の5行から15行に入力された文字列を
アクティブシートで検索・置換をします。

Sub macro20200503a()
'複数個の文字列を一括置換する
'検索範囲はアクティブシート
   
    Dim i As Integer
    Dim msg As Integer
    Dim ad As String
    Dim sh1 As Worksheet
    Dim c As Range
    Dim s_word As String, r_word As String
   
    '目的のシートか確認する
    msg = MsgBox("現在のシートで検索・置換を行いますか？", vbOKCancel)
    If msg <> 1 Then
        Exit Sub
    End If
   
    Set sh1 = Sheets("複数置換")
   
    '文字列を1個ずつ検索・置換する
    For i = 5 To 15
        s_word = sh1.Cells(i, 1) '検索する文字列
        r_word = sh1.Cells(i, 2) '置換する文字列
       
        '文字列が空の場合,次の文字列へ
        If s_word = "" Or r_word = "" Then
            GoTo Line100
        End If
       
        'A1セルからアクティブシートの検索開始
        '変数cに検索結果の範囲を格納
        Set c = Cells.Find(What:=s_word, After:=Range("A1"), _
            LookIn:=xlValues, LookAt:=xlPart, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, MatchByte:=False, _
            SearchFormat:=False)
       
        '一番最初に見つかったセルを変数adに格納する
        If Not (c Is Nothing) Then
            ad = c.Address
        End If
       
        '検索結果がNothingにならない限り検索・置換を続ける
        Do While Not (c Is Nothing)
            c.Activate
            c.Interior.Color = RGB(200, 255, 255)
            c.Replace What:=s_word, Replacement:=r_word, _
                LookAt:=xlPart, SearchOrder:=xlByRows, _
                MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
           
            Set c = Cells.FindNext(After:=c)
           
            '検索結果が一巡したらcをNothingにしてLoopを終了
            If Not (c Is Nothing) Then
                If c.Address = ad Then
                    Set c = Nothing
                End If
            End If
        Loop
       
Line100:
    Next i
   
    MsgBox "検索終了"
   
End Sub

コードの説明をしていきます。

検索・置換する対象はアクティブシートです。
VBAで置換したらものは元に戻せないので，
コード実行前に
目的のシートが選択されているか確認するための
MsgBoxを表示させます。
[キャンセル]がクリックされると検索をしないでコードを終了します。

検索する文字列は変数s_word，
置換する文字列は変数r_wordに入れます。
For文を使って
5行から15行まで文字列を入れ替えていきます。
ある行の文字列が空の場合，検索をしないで次の行に行きます。

検索はFindメソッドを使用します。
What引数に検索したい文字列を指定します。
After引数に検索を開始するセルを指定します。
その他の引数は下記リンク先を参照ください。
Range.Find メソッド (Excel)

Findメソッドで見つかったセルは変数cに格納します。
検索結果がない場合cはNothingになります。
cがNothingでなければ，
検索した文字列が見つかったことがわかります。

最初に見つかったセルのアドレスを
変数adに入れておき，検索の終了の目標とします。

置換はReplaceメソッドを使用します。
詳しくは下記リンク先を参照ください。
Range.Replace メソッド (Excel)

[次を検索]は，FindNextメソッドを使用します。

[置換]と[次を検索]を
Do...While文を使って
変数cがNothingにならない限り続けます。

[置換]と[次を検索]の繰り返しの終了は
検索結果が一番最初のセルに戻った時，
また変数cがNothingになった時です。
この状態になったら
次の行の文字列を検索・置換していきます。
最終行までこの繰り返しです。

使用Ver：Win10, Excel For Office365

続きを読む "複数個の文字列を一括で置換する"

ウィンドウの分割とウィンドウ枠の固定

ウィンドウの分割を手動でするには
分割したいラインの下・右のセルを選択した状態で
[表示]-[分割]をクリックします。
上記の操作で次の画像の状態になります。

グレーの線の上側，下側，左側，右側は
それぞれスクロールできます。

ウィンドウ枠の固定をするには，手動では
分割したいラインの下・右のセルを選択した状態で
[表示]-[ウィンドウ枠の固定]-[ウィンドウ枠の固定]をクリックします。
上記の操作で次の画像の状態になります。

細い線で分割された左側・上側は固定されて
スクロールできない状態になります。

ウィンドウの分割とウィンドウ枠の固定は
同時に使用することはできません。

分割を使用している状態でウィンドウ枠の固定をすると
分割箇所でウィンドウ枠の固定がされます。
逆に，ウィンドウ枠の固定を使用している状態で分割をすると
ウィンドウ枠の固定位置で分割されます。

ウィンドウの分割とウィンドウ枠の固定を
VBAで行う方法を見ていきます。

次のコードはウィンドウの分割をします。

Sub macro20200502a()
'ウィンドウの分割

    With ActiveWindow
        'ウィンドウ枠の固定を解除
        .FreezePanes = False
        'ウィンドウの分割を解除
        .Split = False
    End With
   
    'ウィンドウの分割の設定
    With ActiveWindow
        .SplitRow = 2
        .SplitColumn = 1
    End With

End Sub

コードの流れを説明します。
まず，ウィンドウの分割を設定する前に
既存の設定がある場合の対策として設定を解除します。
.FreezePanes = Falseでウィンドウ枠の固定を解除，
.Split = Falseでウィンドウの分割を解除します。

ウィンドウの分割の設定は
SplitRowプロパティに分割の上側に表示したい行数，
SplitColumnプロパティに分割の左側に表示したい列数を指定します。
下のコードでは上側に2行，左側に1列を表示するよう設定します。

SplitRow，SplitColumnプロパティに0以外の値を設定すると
Splitプロパティは自動でTrueになります。

行のみ分割したい場合は
上のコードのSplitColumn=0にしてください。
同じく列のみ分割したい場合は
上のコードのSplitRow=0にしてください。

ウィンドウ枠の固定をするには上記のマクロの
.SplitColumn = 1の後に
.FreezePanes = Trueを追加して
ウィンドウ枠の固定を有効にします。

Sub macro20200502b()
'ウィンドウ枠の固定

    With ActiveWindow
        'ウィンドウ枠の固定を解除
        .FreezePanes = False
        'ウィンドウの分割を解除
        .Split = False
    End With
   
    'ウィンドウの分割の設定
    With ActiveWindow
        .SplitRow = 2
        .SplitColumn = 1
        .FreezePanes = True
    End With

End Sub

使用Ver：Excel For Office365

続きを読む "ウィンドウの分割とウィンドウ枠の固定"