VBAの勉強を始めてみた

色々試しています。

非プログラマーがプログラミングの基礎を学んだのちに、進むべき方向は?(雑記-4)

今回は、プログラミングを本職としていない非プログラマーな私が非プログラマーを代表(?)してプログラミングとの付き合い方を考えてみたいと思います。

 

テクニックではなくアイデアで勝負する

まあ・・・非プログラマーは趣味でプログラミングをやっているだけなので、一日の大半をプログラミングに費やしているプロの方には、知識・ノウハウ・テクニックで敵うわけがありません。
しかし、非プログラマーにはプログラミング以外の自分の本職がある(無い場合は、すいません)はずですから、「プログラミングによって本職を効率化する」など目的を見出しやすいという強みがあると思います。
ある程度、基礎を学んで簡単なプログラムを書けるようになってきたら、テクニックを追求するよりも「(プログラム的には)簡単だけどアイデアは斬新」みたいな方向へ舵を切っていけたらいいんじゃないかな・・・と最近思います。
もちろん、知識・ノウハウ・テクニックがあれば、アイデアを形にしやすいとは間違いありませんが・・・。

 

プログラマーがプログラミングを活用する場面とは

私たちがプログラミングを活用する場面はいくつかあると思います。
・仕事に役立てる(仕事を効率化するなど)
・完全に趣味(プラモデルなんかを組み立てるのと同じ)
フリーソフト、フリゲーなどを開発する
・その他?
私はもともとフリゲーを製作するために、プログラミングを学び始めました。しかしフリゲー製作は時間的コスパが悪く、あとで振り返って「楽しかった」思い出ぐらいしか残りません。(それはそれでいいじゃないか、という意見もあるかもしれませんががが・・・)
フリゲで学んだこと(プログラミング)を無駄にせず、かつ時間的コスパを考えて、辿り着いたのが「仕事の効率化」および「VBA」でした。

 

プログラミングする→仕事を効率化できる→時間的余裕が生まれる→プログラミングする→仕事を効率化できる・・・

 

プログラミングを趣味とし、プログラミングのネタは本職のほうで拾ってくる・・・という永久機関のようなサイクルを回せるのではないか、と思った訳です。

 

プログラミングに時間はかけない

とはいうものの、プログラムを少し書けるようになったら「もっと難しいのを書いてみよう」「もっと凝ったことをしてみよう」っていう欲が出てきるものです。結果として、プログラミングに時間をかけるようになり、仕事よりもプログラミングに時間をかけるといった本末転倒なことをおっぱじめる始末です。

自分がやりたいのはそういうのではなく「冷蔵庫にあるあり合わせの材料で酒のつまみをサッと作る」みたいなプログラミングなんです!(無駄に力説)
プロの方から「プログラミングなめんな」という声が聞こえてきそうですが・・・。


まとめ
仕事は「食材」、プログラミングは「調理道具」。しかし、非プログラマーが作るのは「家庭料理」なのでプロ用の道具は要らないということです。

分かりにくい・・・?

ではまた(`_´)ゞ

f:id:kouten0430:20171109221843p:plain

コサイン90度は0にならない?

先日、仕事で三角関数を使う場面があったのですが、エクセルでcosの計算をしたところ、90°のところで結果が「0」にならず、一瞬ぽかーんとなってしまいました。
「あるぇ~、確かcos90°って0になるはずだよね?もしかして今まで勘違いしてたんだろうか(゜ρ゜)」
いや、そんなハズはない・・・でも、エクセル先生がお示しになるのは「6.12574E-17」っていう0に近いけれど、0じゃない数値なんだな・・・(゜ρ゜)
はて・・・?

ということで、疑問を解消するために少し調べたので、整理してみます。

 

まず、角度の単位についておさらい

デグリー:角度を度で表す
ラジアン:角度をπで表す
グラード:角度をg(1g=0.9度)で表す 

Deg Rad Grad
0 0 0
30 π/6 33.3・・・
45 π/4 50
60 π/3 66.6・・・
90 π/2 100
180 π 200
270 1.5π 300
360 400

ちなみに、電卓で「Deg」「Rad」「Grad」のモードがあると思いますが、これは角度の計算をどの単位で行うかを切り替えるものです。(今まで生きててGradは一度も使ったことがありませんが・・・(;^_^A)

 

んで、エクセルではどの単位で計算するのかというと「ラジアン」のほうで計算することになっています。

COS(90) ←×
COS(π/2) ←○
COS(100) ←×

πは関数PI()で取得できるので、数式にCOS(PI()/2)と入力するか、デグリーをラジアンに変換するRADIANS関数を入れ子にして、COS(RADIANS(90))と入力することになります。しかし、ここでコンピューターの限界が生じてしまいます。πは皆さんご存知のとおり、小数以下が循環せずに続く無理数です。

 

π=3.1415926535897932384626433832795028841971693993751058209749445923078164062862089986280348253421170679821480865132823066470938446095505822317253594081284811174502・・・

 

エクセルで扱うことができる有効桁数は15桁まで

ここからが本題です。エクセルで扱うことができる有効桁数は15桁までなので、関数PI()の中に入っている円周率は、3.14159265358979の15桁までであり,これを計算に使うと僅かな誤差がでてしまいます。

理論的にcos90°は0ですが、エクセル先生はそんなことを知らずにクソまじめに15桁に打ち切られた数値を使って計算をし、結果、僅かな誤差を伴った答えを導きだすのです。
cos270°、sin180°、tan180°も同様です。

これを浮動小数点演算誤差の打切り誤差とゆーそうです。コンピューターには他にも多くの誤差があるそうで・・・機会があれば勉強してみます・・・ゴフッ(吐血)

 

ちょっと補足
sin0°やtan0°は引数にπを使っていないので、結果はちょうど0になります。
sin30°は引数にπを使うのでちょうど0.5にならないような気がしますが、有効桁数15桁超の誤差が打ち切られてちょうど0.5になっているように見えます(たぶん)
sin90°も引数にπを使うのでちょうど、1にならないような気がしますが、有効桁数15桁超が切り捨てられてちょうど1になっているように見えます(たぶん)
その他は、誤差があってもわからないレベルの世界。

 

まとめ!

有効桁数15桁で打ち切られた円周率(もどき)を使って計算するため、僅かな誤差が生じる。誤差は非常に僅かなので、ほとんど分からないが・・・0が0でなくなってしまった場合には誤差が大きいと感じてします。は違うものなので、コンピューターの中で誤差が生じていることを理解しておきましょう!

即席でちょっとだけ便利なマクロを書いてみるよ

今回は無くても困らないけど・・・あればちょっとだけ便利かもしれないマクロを書いてみます。もしかしたらエクセルの標準機能でまかなえることをやっているかもしれませんが、そこはご愛嬌。わざわざ1記事にするまでもないので纏めて詰め込みます。

 

選択範囲のセル内改行を削除する

 ご存知のようにエクセルでは、Alt+Enterでセル内改行をすることができます。  

f:id:kouten0430:20171103132934j:plain

このような、セル内改行が不要になった場合にまとめて削除するマクロです。
セル内改行は文字コード「LF」であるため、Replace関数を使って「LF」を「長さ0の文字列」にまとめて置換します。

---------------------- 

Sub 選択範囲のセル内改行を削除する()
    Dim myRange As Range

    For Each myRange In Selection.SpecialCells(xlCellTypeVisible)   '可視セルのみに処理を行う
        If myRange.Address = myRange.MergeArea(1).Address Then   '結合セルの場合は左上の値のみ処理する
            myRange.Value = Replace(myRange.Value, vbLf, "")    'セル内改行(改行コード:LF)を削除
        End If
    Next myRange

End Sub

----------------------

 

数値をパーセントスタイル(文字列)に変換する 

下記の画像では数値がパーセントで表示されています。・・・が、これは書式設定によって表示がパーセントスタイルになっているだけで、実際のデータは0.01、0.02・・・となっています。

f:id:kouten0430:20171103133340j:plain


これを1%、2%・・・のような文字列だと思い込んでコピペ(値)したり、関数やマクロで処理しようとすると予定外の結果になってしまいます。
このマクロでは、数値に100を掛け末尾に"%"を連結し文字列として返します。数値をパーセントスタイルの「文字列」として取り扱いたい場合にどうぞ・・・。

 ---------------------- 

Sub パーセント表記に変換する()
    '数値をパーセント表記(文字列)に変換する
    Dim myRange As Range

    For Each myRange In Selection
        If myRange.Value <> "" And TypeName(myRange.Value) <> "String" _
        And TypeName(myRange.Value) <> "Date" Then
        'セルの値が空白,文字列,日付のいづれかの場合は処理をしない
            myRange.Value = "'" & myRange.Value * 100 & "%"
        End If
    Next myRange
   
End Sub

 ----------------------

 

単一セルの文字列をクリップボードに出力 

例えば、下記の画像のように文字列の入ったセルをコピーすると 

f:id:kouten0430:20171103133617j:plain

 f:id:kouten0430:20171103133642j:plain

クリップボードには、カーソルの位置からも分かるように、文字列の末尾に改行が入った状態で格納されます。
この余分な改行が気に入らないために、文字列をコピーする場合は、わざわざ数式バーから文字列のみを選択してコピーしたりしていました。
・・・が、このマクロでは単一セル(結合セル含む)から余分な改行やTabがない状態でクリップボードに文字列のみを吸い取ることができます。
ちなみにマクロはクイックアクセスツールバーに登録しましょう。マクロの一覧からいちいち実行するなら、数式バーから文字列のみをコピーする従来方式のほうが速いからです(笑)
同じ動作を何十回と繰返す場面があれば、クリック回数を少なくかつコンマ数秒でも速くできたほうが、ストレスもないですし、仕事が捗ります。

----------------------

Sub 単一セルの文字列をクリップボードに出力()
    Dim V As String
    Dim myLib As Object
    Set myLib = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
   
    V = ActiveCell.Value

    myLib.SetText V  '変数の値をDataObjectに格納する
    myLib.PutInClipboard 'DataObjectのデータをクリップボードに格納する
   
End Sub

----------------------

 


エクセルの使い方は人により千差万別なので、必ずしも「ピタッ」とくるとは限りませんが・・・。必要な場面に出くわした際に、思い出してもらえれば嬉しいです。
では失礼します。

選択中のセルの文字列に右側nセル分の文字列を連結する 其の弐

前回の「選択中のセルの文字列に右側nセル分の文字列を連結する」を発展させてみました。

前回の記事 

kouten0430.hatenablog.com

 

非表示の列を処理対象外になるように改修。(下の画像ではB、D、Fが非表示)

f:id:kouten0430:20171029143713j:plain

 

連結対象となるセル数を入力します。(可視セルのみでカウント)

f:id:kouten0430:20171029143844j:plain

 

文字列と文字列の連結間に入れる文字を指定できます。改行とするので、キャンセルします。

f:id:kouten0430:20171029144004j:plain

 

選択中のセルの文字列の末尾に(改行を挟んで)3セル分連結されました。

f:id:kouten0430:20171029144202j:plain

 

非表示セルを再表示してみます。非表示列にあった「★」が連結されていないことが分かります。

f:id:kouten0430:20171029144353j:plain

 

こんな感じで、連結させたくない列を非表示にしておけば便利に使えます。(たぶん)

 

----------------------

Sub 右隣の文字を連結()
    '選択したセルの文字列に右隣nセルの文字列を連結する
    '非表示の列の文字列は連結しません(連結させたくない列を非表示にしておくと便利です)
    Dim Y As Integer
    Dim X As Integer
    Dim XR As Variant
    Dim XC As Variant
    Dim i As Integer
    Dim myRange As Range
   
    XR = Application.InputBox(Prompt:="選択中のセルに右側何セル分の文字を連結しますか?", Type:=1)
        If TypeName(XR) = "Boolean" Then
            Exit Sub
        End If

    XC = Application.InputBox(Prompt:="連結間に挿入する文字を入力して下さい。(ブランクでも可)" _
    & vbCrLf & "改行にする場合はキャンセルして下さい。", Type:=2)
        If TypeName(XC) = "Boolean" Then
            XC = vbLf
        End If

    For Each myRange In Selection.SpecialCells(xlCellTypeVisible)
        Y = myRange.Row
        X = myRange.Column + 1
        i = 1
        Do While i <= XR
            If Columns(X).Hidden = False Then '非表示の列は処理を行わない
                myRange.Value = myRange.Value & XC & Cells(Y, X).Value
                X = X + 1
                i = i + 1
            Else
                X = X + 1
            End If
        Loop
    Next myRange
   
End Sub

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

選択中のセルの文字列に右側nセル分の文字列を連結する

タイトルが長いです。
なんのこっちゃ。という感じですが、タイトルのとおりです。

 

数値の入ったセルと単位の入ったセルが隣同士に並んでいて(普通によくある状況ですね)、とある事情で一つのセルに数値と単位を一緒に入れる必要があり、かつデータが多かったので

\_ヘヘ(∀`*)カタカタ。oO(楽して早く終わらせたい・・・)

という、怠け者スキルが自動的に発動し、ほとばしるパトスを抑えきれなくなったのでしょうもないマクロを作ってみました(`_´)ゞ 今回の事例に限らず、汎用的に使えるように少し工夫してみました。

関数でもよかったんですけど、VBAの利点は関数のために別セルを用意しなくてもいいところにあります。

 

例えば、選択中のセルに右側3セル分の文字列を連結して格納します。(例では連結する文字と文字の間に改行を入れています)

f:id:kouten0430:20171028154715j:plain

f:id:kouten0430:20171028154741j:plain

注:仕事で絵文字は使いません。

 

文字と文字の連結間に入れる文字は改行以外にも、通常の文字列を指定できます。スペースでもいいですし、ブランク(長さ0の文字列)でもOKです。

ブランクの場合はこんな感じ

f:id:kouten0430:20171028155135j:plain


EXCELを使っていて、こんな状況がなかったら洋ナシなので、このページは見なかったことにして素通りして下さい。

 

文字列連結演算子を使った、かんたんなプログラムです。

----------------------

Sub 右隣の文字を連結()
    '選択したセルの文字列に右隣nセルの文字列を連結する
    Dim Y As Integer
    Dim X As Integer
    Dim XR As Variant
    Dim XC As Variant
    Dim i As Integer
    Dim myRange As Range
   
    XR = Application.InputBox(Prompt:="選択中のセルに右側何セル分の文字を連結しますか?", Type:=1)
        If TypeName(XR) = "Boolean" Then
            Exit Sub
        End If

    XC = Application.InputBox(Prompt:="連結間に挿入する文字を入力して下さい。(ブランクでも可)" _
    & vbCrLf & "改行にする場合はキャンセルして下さい。", Type:=2)
        If TypeName(XC) = "Boolean" Then
            XC = vbLf
        End If

    For Each myRange In Selection.SpecialCells(xlCellTypeVisible)
        Y = myRange.Row
        X = myRange.Column
        For i = 1 To XR
            myRange.Value = myRange.Value & XC & Cells(Y, X + i).Value
        Next i
    Next myRange
   
End Sub

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

GUIDを調べる

注:今回の記事は少々推定が含まれます

 

GUIDはプログラム(など)を識別するための世界で唯一の識別子(32桁の16進表記〔128bit〕で表す)です。世界で唯一というくらいですから、世界のどっかで集中管理していると思われがちですが、そうではなく、世界中のプログラマーが専用のソフトでランダムに好き勝手に生成しています。しかしランダムに生成したところで、その組み合わせは128bitという天文学的な数字であり、重複する可能性は限りなく0に近いという性質を利用しています。
こうやってできた、世界で唯一のGUIDがプログラム(など)に埋め込まれており、プログラムが他のプログラム(など)を識別する際に重複することなく識別することができるようになっている訳です。

 

VBAで外部ライブラリのGUIDを取得する。

VBProjectが現在参照している外部ライブラリのGUIDを取得するマクロを実行してみましょう。
----------------------
Sub 外部ライブラリのGUIDを調べる()
'Microsoft Visual Basic for Applications Extensibilityを参照設定して下さい
'「VBAプロジェクトオブジェクトモデルへのアクセスを信頼する」にチェックを入れて下さい
'アクティブシートのA~D列に参照中のライブラリの名称、GUID、メジャーバージョン、マイナーバージョンを出力します
'個人用マクロブック以外のアクティブブックを対象にします
Dim myRef As Variant
Dim i As Integer
    i = 1
    Cells(i, 1).Value = "Name"
    Cells(i, 2).Value = "GUID"
    Cells(i, 3).Value = "Major"
    Cells(i, 4).Value = "Minor"
For Each myRef In ActiveWorkbook.VBProject.References
    i = i + 1
    Cells(i, 1).Value = myRef.Name
    Cells(i, 2).Value = myRef.GUID
    Cells(i, 3).Value = myRef.Major
    Cells(i, 4).Value = myRef.Minor
Next
End Sub
----------------------

マクロを実行してSheet1に出力したものが下記です。(4つ出力してみた)

  Name GUID Major Minor
Microsoft Forms 2.0 Object Library MSForms {0D452EE1-E08F-101A-852E-02608C4D0BB4} 2 0
Microsoft ActiveX Data Objects 6.1 Library ADODB {B691E011-1797-432E-907A-4D8C69339129} 6 1
Microsoft Scripting Runtime Scripting {420B2830-E718-11CF-893D-00A0C9054228} 1 0
Microsoft VBScript Regular Expressions 5.5 VBScript_RegExp_55 {3F4DACA7-160D-11D2-A8E9-00104B365C9F} 5 5


前回の外部ライブラリの参照設定を自動で行うマクロは、このGUIDを使用しました。

 

CreateObjectについて(前々回の補足)

CreateObjectの引数には、プログラムID(Name.Object)を指定します。

例えば、こんな感じ
CreateObject("ADODB.Connection")
CreateObject("Scripting.FileSystemObject")
CreateObject("VBScript.RegExp")
CreateObject("MSForms.DataObject") ←これは×

 

MSForms.DataObjectは何ゆえバツなのか・・・?MSForms.DataObjectはレジストリにプログラムIDとして登録されない(登録が必須ではないため、開発者が登録しなかった?)のでプログラムIDで呼び出すことができず、代わりにGUIDで呼び出しするしかありません。
こんな感じ
CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
※GUIDの頭にnew:をつけてね

 

プログラムIDまたはGUIDで呼び出すイメージ

f:id:kouten0430:20171022133710j:plain

注:あくまでイメージですよ・・・。

 

MSForms.DataObjectのGUIDはネットで調べたものです。・・・ひとつ疑問が生まれます。外部ライブラリのGUIDは調べることができますが、外部ライブラリに内包される各オブジェクトのGUIDは自分で調べることができるのでしょうか?
・・・ここから先はEXCELを便利に使って仕事を楽したい(だけ)という当初の趣旨から脱線するので、止めときます。次回からベクトルの向きを少し戻しますヾ(;´・з・`)/

 

こちらを参考にさせていただきました。

thom.hateblo.jp

参照設定を自動で行う

前回、参照設定の手間なくマクロを使う方法として、参照設定なしでインスタンスを生成する(CreateObject)方法について熱く語りましたが、今回はもう一つの方法である「参照設定を自動で行う」についても漏れなく勉強しておきたいと思います。

参照設定をマクロで追加する方法は2通りあります。例として、以下の外部オブジェクトをそれぞれの方法で参照設定してみましょう。
Microsoft Forms 2.0 Object Library
Microsoft ActiveX Data Objects 6.1 Library
Microsoft Scripting Runtime
Microsoft VBScript Regular Expressions 5.5

 

1.外部オブジェクトのファイル名(フルパス)で指定する方法です。 

References.AddFromFile("ファイルのフルパス") 

 

私のPC環境では次のようになります。
----------------------
Sub 参照設定をフルパスで追加()
'「VBAプロジェクトオブジェクトモデルへのアクセスを信頼する」にチェックを入れて下さい
'個人用マクロブック以外のアクティブブックを対象にします
    Dim myRef As Variant
    Set myRef = ActiveWorkbook.VBProject.References.AddFromFile("C:\Windows\SysWOW64\FM20.DLL")
    Set myRef = ActiveWorkbook.VBProject.References.AddFromFile("C:\Program Files (x86)\Common Files\System\ado\msado15.dll")
    Set myRef = ActiveWorkbook.VBProject.References.AddFromFile("C:\Windows\SysWOW64\scrrun.dll")
    Set myRef = ActiveWorkbook.VBProject.References.AddFromFile("C:\Windows\SysWOW64\vbscript.dll\3")
End Sub
----------------------

 

2.外部オブジェクトのGUIDで指定する方法です。

References.AddFromGuid("GUID",メジャーバージョン番号,マイナーバージョン番号)

 

PC環境に依存せず次のようになります。
----------------------
Sub 参照設定をGUIDで追加()
'「VBAプロジェクトオブジェクトモデルへのアクセスを信頼する」にチェックを入れて下さい
'個人用マクロブック以外のアクティブブックを対象にします
    Dim myRef As Variant
    Set myRef = ActiveWorkbook.VBProject.References.AddFromGuid("{0D452EE1-E08F-101A-852E-02608C4D0BB4}", 2, 0)
    Set myRef = ActiveWorkbook.VBProject.References.AddFromGuid("{B691E011-1797-432E-907A-4D8C69339129}", 6, 1)
    Set myRef = ActiveWorkbook.VBProject.References.AddFromGuid("{420B2830-E718-11CF-893D-00A0C9054228}", 1, 0)
    Set myRef = ActiveWorkbook.VBProject.References.AddFromGuid("{3F4DACA7-160D-11D2-A8E9-00104B365C9F}", 5, 5)
End Sub
----------------------

 

どちらの方法でも、このように参照設定が自動で追加されていることが確認できます。(既に参照設定済みであればエラーになります)

f:id:kouten0430:20171021145717j:plain


注意点として、参照設定をマクロで操作する場合は、事前にマクロのセキュリティから「VBAプロジェクトオブジェクトモデルへのアクセスを信頼する」にチェックをつけておく必要があります。

f:id:kouten0430:20171021145805j:plain

f:id:kouten0430:20171021145829j:plain

 

最後に・・・フルパスで指定する場合はPC環境に依存しますが、GUIDで指定する場合はPC環境に依存しないので、配布用として作成する場合はGUIDのほうが確実な方法ではないかと思います。