参照設定なしでインスタンスを生成する
よくよく考えれば、マクロを使う前に参照設定をして下さい･･･と言うのは、家に来られたお客様に「お茶はセルフサービスです」といっているようなものです。これからは参照設定しなくても使えるコードを掲載していきます。
お客様が参照設定しなくても（参照設定について考えなくても）マクロを使用できるようにするには以下が考えられます。
・参照設定を自動で行うコードを追記する
・参照設定なしでインスタンスを生成する（CreateObject）
まず最初に思い浮かんだのが、一つ目の「参照設定を自動で行うコードを追記する」ですが、これをやるためには、事前にマクロのセキュリティから、「VBAプロジェクトオブジェクトモデルへのアクセスを信頼する」にチェックをつけておく必要があります。せっかく、手間無くマクロを使えるようにしようとしているのに、新たな手間が発生してしまいますね･･･。「お茶は入れてあげるけど、茶葉は来る途中で買ってきて☆」と言っているのと同じようなもんです。
で、残されるのは２つめです。
通常は、
Dim myLib As XX
Set myLib = New XX
のようにインスタンスの生成を行いますが
代わりに、
Dim myLib As Object
Set myLib = CreateObject("XX")
という記述をします。XXにはオブジェクトの「ｱﾌﾟﾘｹｰｼｮﾝ名.ｵﾌﾞｼﾞｪｸﾄﾀｲﾌﾟ」が入ります（例えば「Scripting.FileSystemObject」のように）。CreateObjectで「ｱﾌﾟﾘｹｰｼｮﾝ名.ｵﾌﾞｼﾞｪｸﾄﾀｲﾌﾟ」が使えないものは代わりに「GUID」を指定します。（GUIDについては別途記載しようと思います）
この方法で参照設定せずに外部ライブラリのオブジェクトをインスタンス化することができます。
前者は事前バインディング、後者は遅延バインディング（実行時バインディング）と呼ばれるそうです。じゃあぜんぶ、参照設定の要らない遅延バインディングでいいんじゃね？と思うかもしれません。･･･が、マイクロソフトのヘルプによると
「可能な場合は、事前バインディングされたオブジェクトを使用してください。これによって、コンパイラは、アプリケーションをより効率的にする重要な最適化を実行できます。事前バインディングされたオブジェクトは遅延バインディングされたオブジェクトよりも処理が高速です。また、使用されているオブジェクトの種類が明確になるため、コードがより読みやすくなり、保守も簡単になります。事前バインディングのその他の利点として、自動コード補完やダイナミックヘルプなどの便利な機能が有効になります。」
なのだそうです。
しかし･･･お客様にマクロを手間無く使ってもらいたいので、処理時間としてどれくらい違うのか、事前バインディングと遅延バインディングでそれぞれ時間計測してみようと思います。そのうえで処理時間に違いがないようであれば、ブログに掲載するコードについてはすべて遅延バインディングにしたいと思います。
クリップボードのデータを結合セルへ貼り付け
を1万データを対象に処理時間を計ってみます。（コードの始まりと終わりにTime関数を入れて引き算します）
事前バインディングにした場合
遅延バインディングにした場合
画像の使いまわしではありません。何回やっても、どちらも0～1秒程度であります(｀_´)ゞ。実運用上問題ないと判断･･･。
色んなプログラミング言語をやりたい･･････やりたいよぅ（雑記-3）
こんにちは、永遠の黒歴史 好天です。今回は雑記です。
☆JavaScriptやりたい、C#もやりたい･･･
VBAだけではなく色んな言語をやってみたいのが本音です。･･･が、そこそこの年齢になってくると、悲しいかな何かを始める前に費用（時間）対効果があるのかないのか考えてしまいます。
具体的にはどういうことかというと、今やっている仕事はExcelをそこそこ使うのでルーチンワークをVBAで自動化することに時間対効果があります。しかしJavaScriptやC#を活用する場面はあまりありません。ソフトを自作するという手もありますが、まぁ正直、既存のフリーソフトなんかをダウンロードしたほうがよっぽど高性能で効率的です。社内システムに関しては、別途、専門の部署がありますし！それだったら、もっと別の勉強（プログラミング以外）でもしよーっと。
☆若いうちに色んなことをマスターしとけ。ただし･･････
プログラミングに限ったことではありませんが、若いうちは時間がたっぷりあるので、色んなことを納得いくまでやっておくといいと思います。ただし、「時間をたっぷり使って納得いくまでやる」という体質は、ある時期から少しずつ変えていったほうがいいでしょう。（ある時期･･･は自分で見極める必要がありますが･･･）
年齢とともに責任のある仕事を任されるようになり、状況の変化に応じて臨機応変に対応することが求められるようになります。そこまでやってきたことを破棄して、方向転換を余儀なくされることもしばしば･･･。
そんな時に、時間をたっぷり使って納得のいくまで物事をやり遂げ、やり遂げなければ次のステップに行けない･･･という習慣が体に染み付いていると状況の変化に対応できません。習慣というのはそう簡単に矯正できるものではありません＿|￣|○
☆寿命が１５０年くらいあるならやってもいい
寿命が１５０年くらいあって「老人」と言われはじめるのが１２０歳くらいからなら、あらゆるプログラミング言語を納得いくまでやってみたい！（笑）でも、標準的に１５０年の寿命であるなら、たぶん、「まだ先は長いから、もうちょっとあとでやろう(´_ゝ｀)」と言うのは目に見えている･･･。
☆まとめ
人生は長いようで短い。費用（時間）対効果を考えるクセを身につけるのは大事です。
かける費用（時間）よりも効果が下回るのであれば、やめるべきです。
ではまた。
EXCEL表をTableタグ(HTML)に変換する 其の弐
前回、エクセルの表をＨＴＭＬ（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 | 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 |
－－－－－－－－－－－－－－－－－－－－－－
Sub 選択範囲をTableタグに変換しクリップボードに出力其の弐()
'正方形または長方形のような連続した選択範囲とする
Dim i As Long
Dim j As Long
Dim V As String
Dim rh As Integer
Dim ch As Integer
Dim mi As Integer
Dim Alg As String
Dim myLib As Object
Set myLib = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") '参照設定なしでDataObjectのインスタンスを生成する
rh = MsgBox("選択範囲の上端の行を" & vbCrLf & "見出しにしますか？", vbYesNo)
ch = MsgBox("選択範囲の左端の列を" & vbCrLf & "見出しにしますか？", vbYesNo)
mi = Application.InputBox(Prompt:="見出し以外の文字の水平位置を指定できます。" & vbCrLf & _
"1:中央揃え" & vbCrLf & "2:右寄せ" & vbCrLf & "3:両端揃え" & vbCrLf & "ｷｬﾝｾﾙ:左寄せ(初期値)", Type:=1)
If TypeName(mi) = "Boolean" Then
Alg = ""
ElseIf mi = 1 Then
Alg = " align=""center"""
ElseIf mi = 2 Then
Alg = " align=""right"""
ElseIf mi = 3 Then
Alg = " align=""justify"""
End If
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>"
If j = Selection.Columns(Selection.Columns.Count).Column Then '左端かつ右端である場合の処置
V = V & vbCrLf & "</tr>" & vbCrLf
End If
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 '見出し行以外の処理
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" & Alg & ">" & Cells(i, j).Value & "</td>"
End If
If j = Selection.Columns(Selection.Columns.Count).Column Then '左端かつ右端である場合の処置
V = V & vbCrLf & "</tr>" & vbCrLf
End If
ElseIf j = Selection.Columns(Selection.Columns.Count).Column Then '選択範囲の右端であれば末尾に</tr>を追加
V = V & "<td" & Alg & ">" & Cells(i, j).Value & "</td>" & vbCrLf & "</tr>" & vbCrLf
Else '左端と右端以外の処理
V = V & "<td" & Alg & ">" & Cells(i, j).Value & "</td>"
End If
End If
Next j
Next i
V = V & "</table>"
myLib.SetText V '変数の値をDataObjectに格納する
myLib.PutInClipboard 'DataObjectのデータをクリップボードに格納する
MsgBox "ＨＴＭＬをクリップボードに" & vbCrLf & "出力しました！" & vbCrLf & vbCrLf & _
"ブログなどでお好みの位置にペースト" & vbCrLf & "して下さい。"
End Sub
－－－－－－－－－－－－－－－－－－－－－－
※SubからEnd Subまでをコピーし、標準モジュール等に貼り付けて使用して下さい。なお、マクロで実行した処理は「元に戻す」ことができません。実行前に一旦保存しやり直しのできる状態にしておいて下さい。標準モジュールにコードを貼り付けてマクロを使用する方法はこちら。
(2017.11.2 若干のバグ修正）
Round関数の戻り値が違う？（ワークシート関数とVBA関数の違い）
Round関数が返す値はVBA関数とワークシート関数（俗に言うEXCEL関数）で違います。EXCELでのRound関数はご存知のとおり、引数で指定した桁数で四捨五入を行うものですが、VBAでは偶数丸め（ISO丸め、銀行丸めなどとも呼ばれる）という処理を行います。
具体的にはどういうことかというと、例えば「2.5」という数値を小数第1位以下で四捨五入すると「3」になります。これが、EXCELでのRound関数です。
しかしVBAで同じ処理をRound関数を使って行うと「2.5」は「2」になります。「1.5」は「2」になります。これは端数がちょうど0.5の場合に、繰り上げて偶数となる場合は繰り上げ、繰り上げて奇数となる場合は繰り上げしない（切り捨て）といった処理をしているからです。
0.1～0.4は切り捨て、0.6～0.9は切り上げることに関してはEXCEL関数と変わりません。イメージを掴みやすいように、下の表をご覧下さい。
Round関数の戻り値の違い
| 元の値 | EXCEL | VBA |
|---|---|---|
| 0.1 | 0 | 0 |
| 0.2 | 0 | 0 |
| 0.3 | 0 | 0 |
| 0.4 | 0 | 0 |
| 0.5 | 1 | 0 |
| 0.6 | 1 | 1 |
| 0.7 | 1 | 1 |
| 0.8 | 1 | 1 |
| 0.9 | 1 | 1 |
| 1 | 1 | 1 |
| 1.1 | 1 | 1 |
| 1.2 | 1 | 1 |
| 1.3 | 1 | 1 |
| 1.4 | 1 | 1 |
| 1.5 | 2 | 2 |
| 1.6 | 2 | 2 |
| 1.7 | 2 | 2 |
| 1.8 | 2 | 2 |
| 1.9 | 2 | 2 |
| 2 | 2 | 2 |
| 2.1 | 2 | 2 |
| 2.2 | 2 | 2 |
| 2.3 | 2 | 2 |
| 2.4 | 2 | 2 |
| 2.5 | 3 | 2 |
| 2.6 | 3 | 3 |
| 2.7 | 3 | 3 |
| 2.8 | 3 | 3 |
| 2.9 | 3 | 3 |
ではなぜ、EXCELとVBAでRound関数の仕様が違うのか？･･･は、実は分かりません（知りません）。が、偶数丸めにすると何か違いがあるのでしょうか･･･少し紐解いてみたいと思います。
まず、通常の四捨五入では端数0.5の繰り上がりによって、繰り上がり側に偏りが発生します。なので、データが多いほど繰り上がりによるプラス誤差が増えていきます。
偶数丸めは「銀行丸め」とも言われるくらいですから、誤差を小さく抑えることを目的としています。･･･どういうことかというと奇数と偶数で0.5の繰り上がりを相殺しているので、データが多くなっても誤差を小さく抑えることができるのです。
試しに、1.0～9.9の乱数を5,000個用意して、それぞれEXCELとVBAのRound関数で処理したものを合計して、その誤差を見てみます。
次に乱数を10,000個まで増やして同じことをやってみます。四捨五入に関しては着実に誤差が増えていくのに対し、偶数丸めのほうは誤差が小さくおさまっています。
注意したいのは、Round関数で違いが生じるのは端数がちょうど「5」になる場合だけです。例えば「2.50000000000001」のような数値を小数第1位以下で四捨五入または偶数丸めを行っても結果は「3」になります。
1.00000000000000～9.99999999999999の乱数を5,000個用意して、小数第1位以下をRound関数で処理した結果の違いを見てみます。
合計はEXCELとVBAで同じになります。なぜなら、5,000個程度の乱数では「2.50000000000000」のような偶数丸めの対象になるデータが皆無だからです。
でわまた（＇ω＇）ノ
エクセルでどんなことがしたい？（雑記-2）
エクセル素人研究室室長の好天です。（＇ω＇）ノ今回はちょっとした雑談です。
・エクセルでやりたいことは千差万別
エクセルで何をどうしたいかは、人によって、さらに状況によって千差万別だと思うので、当ブログに記載しているようなガベージなマクロは使う場面がなければ無用の長物です。
そういう場面に出くわした時に、思い出していただくか、グーグルなどから飛んで来ていただければ、室長はよろこびます。
・プログラマーでなくとも最低限の知識はもつべき
小学校でもプログラミングが必修科目となるようですが、VBAなどは比較的難易度が低く（たぶん）、プログラムをちょっと覚えてみようかなぁと思っている人にはうってつけのような気がします。
エクセルでやりたいことに合わせて、サーチエンジンなどの検索から、適当なマクロを拾ってくるのもひとつの手ですが、一番確実なのは自分のやりたいことに合わせて、その都度自分でコーディング（もしくはカスタマイズ）するのがてっとり早いと思います。とはいえ、効率化がマクロの主目的なのに、その都度目的に合わせてコーディングすることに時間がかかるようではナンセンスな気もします。
・汎用的なマクロを目指す
そういう意味で、多目的に使える汎用的なマクロに仕上げていくことに時間をかけることには意味があるのかなぁ･･･と思ったりします。
あとは、どこまでを趣味と割り切れるかのセンスも重要かな･･･と最近思うのです。あくまでアマチュアの場合の話です。
でわまた（＇ω＇）ノ
同じ文字を下方向へnセル分貼り付ける
例えばこんな文字がクリップボードにあります。
これをアクティブセルから下方向にnセル分貼り付けるマクロを作ってみました。
アクティブセルから下方向に123セル分貼り付けてみます。マクロを実行したあとに表示されるInputBoxに「123」を入力しＯＫ。
このようにアクティブセルがA1ならA123までクリップボードの文字が貼り付けられました。
結合セルにも貼り付けできます。（結合セルを1セルとみなす）
非表示セルは飛ばして貼り付けします。（可視セルのみ貼り付ける）
コードを少し変えれば右方向、左方向、上方向になりますが、今のところ使う場面がないのでやめときます。
－－－－－－－－－－－－－－－－－－－－－－
Sub 同じ文字を下方向へnセル分貼り付ける()
'Microsoft Forms 2.0 Object Libraryを参照設定して下さい
'クリップボードのデータを丸ごと1セルに貼り付ける処理をn回繰り返します
'結合セルは1セルとしてカウントします
'非表示セルは1セルとしてカウントしません（つまり可視セルのみに貼り付け）
Dim Dobj As DataObject
Dim V As Variant
Dim i As Integer
Dim Y As Integer
Dim X As Integer
Dim YE As Variant
YE = Application.InputBox(Prompt:="下方向へ何セル分貼り付けますか？", Type:=1)
If TypeName(YE) = "Boolean" Then
Exit Sub
End If
Set Dobj = New DataObject
With Dobj
.GetFromClipboard
On Error Resume Next
V = .GetText
On Error GoTo 0
End With
If Not IsEmpty(V) Then
i = 1
Y = ActiveCell.Row
X = ActiveCell.Column
Do While i <= YE
If Cells(Y, X).Address = Cells(Y, X).MergeArea(1).Address _
And Rows(Y).Hidden = False Then
Cells(Y, X).Value = V
Y = Y + 1
i = i + 1
Else
Y = Y + 1
End If
Loop
Else
MsgBox "クリップボードにデータがありません！"
End If
End Sub
－－－－－－－－－－－－－－－－－－－－－－
※SubからEnd Subまでをコピーし、標準モジュール等に貼り付けて使用して下さい。なお、マクロで実行した処理は「元に戻す」ことができません。実行前に一旦保存しやり直しのできる状態にしておいて下さい。標準モジュールにコードを貼り付けてマクロを使用する方法はこちら。
※上記のマクロは外部ライブラリを使用します。VBEでMicrosoft Forms 2.0 Object Libraryを参照設定して下さい（ツール→参照設定→参照からWindows\System32\FM20.DLLを選択）。参照設定の方法はこちら。
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 |
マクロの標準モジュールへの貼り付け方法はこちら
参照設定の方法はこちら
