EXCEL表をTableタグ(HTML)に変換する

HTMLで表（Tableタグ）を記述するのは、結構骨の折れる作業です。はてなブログでは視覚的に作表する機能がありませんので、地道にHTMLで記述する（または、はてな記法で記述する）しかありません。
そもそもHTMLエディターにしたって、表作成には特化していないのですから、結構面倒だと思います。（各種計算やオートフィル等の機能がないため）
なので、HTML作成において表（Table）作成の部分だけでもEXCELでやってしまおうというのが今回の記事の趣旨です。

まず、下記のマクロをコピペして標準モジュール等に貼り付けして下さい。あと、クリップボードとデータのやり取りを行うのでVBEでMicrosoft Forms 2.0 Object Libraryを参照設定しておいて下さい。（詳細なやり方は記事末尾のリンクからどうぞ）

－－－－－－－－－－－－－－－－－－－－－－

Sub 選択範囲をTableタグに変換しクリップボードに出力()
    'Microsoft Forms 2.0 Object Libraryを参照設定して下さい
    '正方形または長方形のような連続した選択範囲とする
    Dim i As Long
    Dim j As Long
    Dim V As String
    Dim rh As Integer
    Dim ch As Integer
    Dim myLib As New DataObject
   
    rh = MsgBox("選択範囲の上端の行を" & vbCrLf & "見出しにしますか？", vbYesNo)
    ch = MsgBox("選択範囲の左端の列を" & vbCrLf & "見出しにしますか？", vbYesNo)
   
    V = "<table>" & vbCrLf

    For i = Selection.Row To Selection.Rows(Selection.Rows.Count).Row
        For j = Selection.Column To Selection.Columns(Selection.Columns.Count).Column
            If i = Selection.Row And rh = 6 Then '見出し行の処理。データを<th></th>で囲む
                If j = Selection.Column Then    '選択範囲の左端であれば冒頭に<tr>を追加
                    V = V & "<tr>" & vbCrLf & "<th>" & Cells(i, j).Value & "</th>"
                ElseIf j = Selection.Columns(Selection.Columns.Count).Column Then   '選択範囲の右端であれば末尾に</tr>を追加
                    V = V & "<th>" & Cells(i, j).Value & "</th>" & vbCrLf & "</tr>" & vbCrLf
                Else    '左端と右端以外の処理
                    V = V & "<th>" & Cells(i, j).Value & "</th>"
                End If
            Else '見出し行以外の処理。データを<td></td>で囲む
                If j = Selection.Column Then    '選択範囲の左端であれば冒頭に<tr>を追加
                    If ch = 6 Then  '見出し列の処理。データを<th></th>で囲む
                        V = V & "<tr>" & vbCrLf & "<th>" & Cells(i, j).Value & "</th>"
                    Else    '見出し列以外の処理。データを<td></td>で囲む
                        V = V & "<tr>" & vbCrLf & "<td>" & Cells(i, j).Value & "</td>"
                    End If
                ElseIf j = Selection.Columns(Selection.Columns.Count).Column Then   '選択範囲の右端であれば末尾に</tr>を追加
                    V = V & "<td>" & Cells(i, j).Value & "</td>" & vbCrLf & "</tr>" & vbCrLf
                Else    '左端と右端以外の処理
                    V = V & "<td>" & Cells(i, j).Value & "</td>"
                End If
            End If
        Next j
    Next i
   
    V = V & "</table>"
   
    myLib.SetText V  '変数の値をDataObjectに格納する
    myLib.PutInClipboard 'DataObjectのデータをクリップボードに格納する
   
    MsgBox "Tableタグ(HTML)をクリップボードに" & vbCrLf & "出力しました！" & vbCrLf & vbCrLf & _
    "ブログなどでお好みの位置にペースト" & vbCrLf & "して下さい。"
   
End Sub

－－－－－－－－－－－－－－－－－－－－－－

マクロを使用する準備が終わったら、Tableタグに変換したい表を選択してマクロを実行します。

こんな感じでクリップボードにHTMLが出力されるので、試しにはてなブログに貼り付けてみましょう。

「例えばここにTableを貼り付ける↓」という文字の下に、Tableタグを貼り付けてみます。HTML編集モードに切り替えて、マクロから出力したTableタグをペーストします。

見たまま編集モードに戻すと、このように表が挿入されていることが確認できます。

見たまま編集モードでは表の大きさを変更（表の四隅をドラッグ）したり、文字色・文字の大きさ等を変更できます。

表の中に画像を挿入することもできます。

ちなみにマクロから出力したTableには罫線などの装飾が入っていませんが、ブログ側のスタイルの定義によって罫線等が自動的に入ります。

マクロから出力したTableタグをメモ帳などに貼り付け、拡張子を.htmlで保存したものをブラウザで開くと、プレーンなTableが表示されるのみです。（言い換えればプレーンなTableを提供するマクロだと思って下さいませ）

最小限の機能として、見出しの有無のみを選択できるようにはしました。

上端の行と左端の列を見出しにした場合はこんな感じ

Test	Test	Test	Test
Test	Test	Test	Test
Test	Test	Test	Test
Test	Test	Test	Test

上端の行のみ見出しにした場合はこんな感じ

Test	Test	Test	Test
Test	Test	Test	Test
Test	Test	Test	Test
Test	Test	Test	Test

左端の列のみ見出しにした場合はこんな感じ

Test	Test	Test	Test
Test	Test	Test	Test
Test	Test	Test	Test
Test	Test	Test	Test

 マクロの標準モジュールへの貼り付け方法はこちら

kouten0430.hatenablog.com

参照設定の方法はこちら 

kouten0430.hatenablog.com

CSV形式でクリップボードにコピーする

今回は、選択した範囲のデータをCSV形式にしてクリップボードへコピーするマクロを作ってみました。
こんな感じです。

CSV形式でコピーしたい範囲を選択し、マクロを実行します。

こんな感じでCSV形式でコピーされます。

メモ帳などに貼り付けて、拡張子を.csvとして保存すればCSVファイルとして保存することもできます。

しかし･･････、それだけでは面白みがないので結合セルのデータに対しても同様にCSV形式でコピーできるようにしてみました。

このように余分な空白、改行の無い状態でクリップボードへコピーできます。

単一セルと結合セルが混在した範囲ではデータがいびつになるので、単一セルのみ、または結合セルのみの範囲で使用していただければ幸いです。

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊

Sub CSV形式でクリップボードに格納()
    'Microsoft Forms 2.0 Object Libraryを参照設定して下さい
    '正方形または長方形のような連続した選択範囲とする
    '結合セルも単一セル同様のCSV形式にします（ただし、結合セルと単一セルを混在すると歪になります）
    Dim i As Long
    Dim j As Long

    For i = Selection.Row To Selection.Rows(Selection.Rows.Count).Row
        For j = Selection.Column To Selection.Columns(Selection.Columns.Count).Column
            If Cells(i, j).Address = Cells(i, j).MergeArea(1).Address And _
            Rows(i).Hidden = False And Columns(j).Hidden = False Then '結合セルの場合は左上の値のみ取り出す。非表示セルは処理しない
                If Cells(i, j).MergeArea(1).Address = Cells(i, Selection. _
                Columns(Selection.Columns.Count).Column).MergeArea(1).Address Then
                '選択範囲の最終列（最終列を含む結合セル）であれば末尾に改行を追加
                    V = V & Replace(Cells(i, j).Value, vbLf, "") & vbCrLf
                Else
                '選択範囲の最終列（最終列を含む結合セル）以外は末尾にカンマを追加
                    V = V & Replace(Cells(i, j).Value, vbLf, "") & ","
                End If
            End If
        Next j
    Next i
   
    V = Left(V, Len(V) - 2) '最終行の改行区切りを取り除く（CrLfは2文字）
   
    With New MSForms.DataObject
        .SetText V  '変数の値をDataObjectに格納する
        .PutInClipboard 'DataObjectのデータをクリップボードに格納する
    End With
   
End Sub

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
※SubからEnd Subまでをコピーし、標準モジュール等に貼り付けて使用して下さい。なお、マクロで実行した処理は「元に戻す」ことができません。実行前に一旦保存しやり直しのできる状態にしておいて下さい。標準モジュールにコードを貼り付けてマクロを使用する方法はこちら。

※上記のマクロは外部ライブラリを使用します。VBEでMicrosoft Forms 2.0 Object Libraryを参照設定して下さい（ツール→参照設定→参照からWindows\System32\FM20.DLLを選択）。参照設定の方法はこちら。

インスタンスの生成について

前回は外部ライブラリを参照設定する方法を説明しました。今回は外部ライブラリのオブジェクトを使ってコーディングする方法を書いてみたいと思います。

まず、外部ライブラリのオブジェクトは参照設定するだけでは使用できないので、NEWキーワードを使ってインスタンスを生成する必要があります。インスタンスの生成というと初心者の方には分かりにくいかもしれませんが、つまり外部ライブラリにある○○オブジェクトをVBAProjectのプロシージャ側の変数（オブジェクト型）に代入する行為を指します。

少し変な例えかもしれませんが･･････、あの世（外部ライブラリ）のものをこの世（VBA）で使うには、この世で姿かたちのある変数に入れてインスタンス化（実体化）する必要があるのです。
変数に入れてしまえば、あとは変数名.プロパティや変数名.メソッドのようにVBAの基本構文どおりに外部ライブラリのオブジェクトを使用することができます。

で、インスタンスを生成する方法ですが、実際には大きく分けて3通りほどあるようです。先述したNEWキーワードを使用する点に関しては共通ですが。
以下に、クリップボードのデータを変数Vに格納するまでの処理を3通りのインスタンス生成方法で紹介します。

一つ目は、一番スタンダードだと思われる方法です。
－－－－－－－－－－－－－－－－－－－－－－
Sub インスタンスの生成1()
    Dim myLib As Object
    Set myLib = New DataObject
    myLib.GetFromClipboard
    V = myLib.GetText
End Sub
－－－－－－－－－－－－－－－－－－－－－－
Set myLib = New DataObjectの行で、myLibというオブジェクト型の変数にDataObjectを代入しています（インスタンスの生成）。通常の代入と違うのはNewがついているところです。

二つ目は、変数の宣言時にNewをつけてしまう方法です。
－－－－－－－－－－－－－－－－－－－－－－
Sub インスタンスの生成2()
    Dim myLib As New DataObject
    myLib.GetFromClipboard
    V = myLib.GetText
End Sub
－－－－－－－－－－－－－－－－－－－－－－
Set～の代入式がなくなる分、コードが短くて済みます。ただし、インスタンスが生成されるタイミングは変数宣言時ではなく、コードの中で変数が使用されるタイミング（上の例ではmyLib.GetFromClipboardの行）になります。余談ですが、インスタンスが生成された後でSet myLib = Nothingと一旦変数を空にしても、次に変数を使用するタイミングで再びインスタンスが自動生成されます。なんだか、不死身のゾンビみたいです。

三つ目は、WithステートメントにNewをつける方法です。
－－－－－－－－－－－－－－－－－－－－－－
Sub インスタンスの生成3()
    With New DataObject
        .GetFromClipboard
        V = .GetText
    End With
End Sub
－－－－－－－－－－－－－－－－－－－－－－
Withについて補足すると、With ○○はSet 変数 = ○○と読み替えることができます。後のピリオドの前には常に変数があると思っていいでしょう。○○の頭にNewがあるとインスタンスの生成になります。余談ですが、Withで作られた仮の変数は内部的なものなので、ローカルウィンドウやウォッチウィンドウで見ることができません。End Withで仮の変数は空になるので、インスタンスが使用できるのはWith～End Withの間だけです。

以上の3つは、実行速度で微小な違いはあると思いますが、インスタンスが生成されるタイミング、インスタンス開放後の違い（再度、自動生成されるか否か）、インスタンスを使用できる範囲などに注意すれば、あとはお好みでいいと個人的には思います。

外部ライブラリの参照設定について

当ブログでちょくちょく外部ライブラリ（Microsoft Forms 2.0 Object Libraryなど）を使用したコードを掲載していますが、今回は外部ライブラリの参照設定について説明したいと思います。

まず、外部ライブラリとは何ぞや？ということですが、ざっくり言うと「VBAには無い機能（オブジェクト）」を収めたファイル（拡張子が.dllや.old.やtlbのようなファイル）ということなります。

VBAがそのような外部ライブラリを参照しますよ。ということをProjectごとに設定しなければいけない訳です。

外部ライブラリにはどのようなものがあるか例を挙げると、以下のようなものがあります。※カッコ内は実際のファイル名。xはバージョンが入ります。

・ファイルやフォルダー、ドライブを操作するためのFSOオブジェクト
Microsoft Scripting Runtime(scrrun.dll)

・外部データベースを操作するためのADOオブジェクト
Microsoft ActiveX Data Objects x.x Library(msado xx.dll)

・正規表現を使用するためのRegExpオブジェクト
Microsoft VBScript Regular Expressions x.x(vbscript.dll)

・クリップボードとテキストデータのやり取りを行うDataObjectオブジェクト
Microsoft Forms 2.0 Object Library(FM20.DLL)

その他、たくさんのライブラリがあるようですが、私のような素人にはすべてを把握することはできません。

で、本題の参照設定の方法ですが･･･参照設定するProjectを選択した状態で「ツール」→「参照設定」を選択します。

参照設定したいライブラリ名にチェックを入れ、OKを押します。

参照設定したいライブラリ名が一覧にない場合は、「参照」をクリックし実際のファイル名を入力（ファイル名の一部を入力すると候補が出るのでそこから選択してもいい）し、「開く」を押します。

ネットに転がっている参照設定ありきのVBAコードをコピペして使用するぶんには、ここまでやってもらえば使用できるようになります。

次回は、自分でコードを書く場合の外部ライブラリの使用方法について記載したいと思います。 

kouten0430.hatenablog.com

マクロを高速化する（画面表示の更新をオフにする）

今更、私が書く必要もないかもしれませんが、マクロを高速化するために最も効果があると謳われる、

Application.ScreenUpdating　プロパティ

の設定について紹介しておく必要があろうかと思います。

プロパティの設定はTrueかFalseの2通りです。
Application.ScreenUpdating=False　･･････画面表示の更新をオフにする
Application.ScreenUpdating=True　･･････画面表示の更新をオンにする（デフォルト）

例えば以下のようなコードがあったとします。

For i = 1 To 1000000
Range("A1").Value = Range("A1").Value + 1
Next i

これはセルA1に「1」を加算する処理を、For～Nextで百万回繰り返しています。
1が加算されるごとにリアルタイムで画面表示が更新され、デジタル時計の少数以下を見るような速さで数値がどんどん増えていきます。ですが、これは「1を加算する」という処理と同時に「画面表示を更新する」という処理を同時に行っているため、処理に時間がかかってしまいます。

では、処理中の「画面表示の更新」をやめてしまったらどうでしょうか？画面表示の処理にかかる負荷が軽減され、処理時間が短縮できるのではないでしょうか。

ということで、上記の処理を「画面表示更新あり」と「画面表示更新なし」の2バージョンでテストして時間を計測してみましょう。時間計測は処理前のTime関数の値と、処理後のTime関数の値を引き算して表示させます。

では、「画面表示更新あり」からやってみましょう。デフォルトで画面表示の更新はオンなので、何も設定する必要はありません。

－－－－－－－－－－－－－－－－－－－－－－

Sub 処理時間計測その1()
    Dim i As Long
   
    StartTime = Time
   
    For i = 1 To 1000000
        Range("A1").Value = Range("A1").Value + 1
    Next i
   
    StopTime = Time
    MsgBox "処理にかかった時間は" & vbCrLf & Minute(StopTime - StartTime) _
    & "分" & Second(StopTime - StartTime) & "秒"

End Sub

－－－－－－－－－－－－－－－－－－－－－－

「画面表示更新あり」では3分38秒かかりました。

次に、「画面表示更新なし」でやってみましょう。処理の前にApplication.ScreenUpdating=Falseを追記します。コードの中で再度Trueに設定するか、もしくはプロシージャを抜けるまで画面表示の更新は行われなくなります。

－－－－－－－－－－－－－－－－－－－－－－

Sub 処理時間計測その2()
    Dim i As Long
   
    StartTime = Time
   
    Application.ScreenUpdating = False  '画面表示の更新をオフにする
   
    For i = 1 To 1000000
        Range("A1").Value = Range("A1").Value + 1
    Next i
   
    StopTime = Time
    MsgBox "処理にかかった時間は" & vbCrLf & Minute(StopTime - StartTime) _
    & "分" & Second(StopTime - StartTime) & "秒"

End Sub

－－－－－－－－－－－－－－－－－－－－－－

「画面表示更新なし」では1分18秒でした。約3分の1程度、処理時間を短縮できていることが分かります。

上記2バージョンのテストを何回かやってみましたが、ほぼ同じ結果になりました。画面表示更新なしバンザイ！

おまけ
Application.ScreenUpdatingについて、疑問点が3つほどあったので自分なりに調べてみました。

1.Application.ScreenUpdatingをTrueに戻さないままプロシージャを終了した場合どうなるのか？
ウォッチ式を使ってApplication.ScreenUpdatingの値を見てみましょう。

ここでFalseになる。

再度、プロシージャを開始するとTrueから始まる。

プロシージャの終了後に画面表示の更新が復活することからも、Application.ScreenUpdating=Falseはプロシージャ実行中のみ適用されていることが分かります。（End SubでTrueに戻る）

2.プロシージャの中で別のプロシージャを呼び出した場合はどうなるのか？

呼び出し元の状態が適用される（Trueに戻るわけではない）

3.ステップイン実行中は画面表示はどうなる？

ステップイン実行中は、Application.ScreenUpdating=Falseであっても画面表示は更新されるようです。ステップインによる動作確認中に「あれ？」と思う人が多いのではないでしょうか。
ちなみにステップイン実行中、ウォッチ式の値ではFalseになっていますが、コード中のApplication.ScreenUpdatingにカーソルを合わせるとTrueになっています。

VBAは仕事の役にたっているか？（雑記-1）

VBAの勉強を始めてはや5ヶ月近く･･････。ちょっとだけ便利なマクロを細々と作成してきました。

実際のところ仕事に役立っているかどうか？

答えは半々といったところでしょうか。何故なら仕事はEXCELオンリーではないからです。体を動かす肉体作業もあれば、お堅い決裁書を作ることもある･･････。

PCに関しては社内独自システムを使ったり、市販のソフトを使ったり、いろいろなものを使います。EXCELはその中の一つで、市販ソフトの中ではかなり使用頻度は高いけれど、ちょっとした仕事なら標準機能で事足りることも･･････。

まだ「1日かかるハズの仕事が数秒で終わった！」というような、快感は味わっていません（￣ｑ￣;）

がしかし！VBAを使い始めたことで、こっちにはVBAがあるぜ～ルーチンワークでもハローワークでも何でも来い。既存のマクロで対処できなければ、その都度新しいマクロを作ってやる･･････という心の余裕が生まれました。ヽ(・∀・)ノ

そして私は悟りを開き、職場が新たなマクロを生み出すキッカケの宝庫に見えてきたのです･･････(‐人‐)ﾅﾑﾅﾑ。アイデアはきっと無限にある筈。

マクロにパスワードを設定するには

第三者にマクロの内容を覗かれたり、変更されたりしないようにパスワードを設定することができます。
例えば、マクロを改変されたら業務に支障をきたす、マクロをパクられたくない、マクロの内容を見られるのが恥ずかしい･･････等々、人によって理由は様々だと思います。
パスワードを設定する方法は簡単です。まず、VBE（Visual Basic Edtor）を起動し、パスワードを設定したいVBAProjectを選択します。

次に「ツール」から「VBAProjectのプロパティ」を選択します。

保護タブの中にある、「プロジェクトを表示用にロックする」にチェックを入れ、パスワードを入力しＯＫを押します。

これでBookを閉じ、次にBookを開いた時からパスワードが有効になります。

パスワードを設定したVBAProjectを開こうとすると、このようにパスワードの入力を求められます。