【VBA】Excelで簡易の電子署名システムを作ってみた！なりすまし防止機能付き

◇前置き

昨今、ペーパレス化・脱ハンコの動きがようやく日本でも見られるようになってきました。

様々な企業が「電子署名システム」を開発・提供するようになりましたが、「部内で使うだけだし、そこまで立派なものはいらないんだよなぁ・・・。」なんてことありませんか？

ちゃんとした電子署名システムは中々良いお値段しますよね？

今回は、Excel＋VBAで簡単に作れる電子署名システムを紹介します。

紹介するシステムの機能を要約するとこんな感じです。

①マスターに名前を登録している人以外は承認欄に署名が出来ない。
②マスターに名前を登録している人も自分の名前以外では署名が出来ない。

とりあえずで使う分には十分ではないでしょうか？

◇概要

今回の画面イメージはこんな感じです。


「承認欄」があるだけのシンプルなシートです。
プルダウンで名前を選択するようにしてあります。

今回の肝は、「自分の名前以外を選択できないようにする」ことです。

この機能を実装するために、同ブック内の別シートにマスターを設けます。

今回は例としてこんなマスターを用意しました。

ユーザーIDは、ログインする時の名前(ユーザー名)のことです。

◇ソースコード

ソースコードはこんな感じです。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim user_name As String
Dim AppName As String
Dim appNo As Variant
   user_name = Environ("UserName")
   Sheets("メイン").Activate
   AppName = Range("C3").Value
   If AppName <> "" Then
       Application.ScreenUpdating = False
       Sheets("マスター").Activate
       Dim ws As Worksheet
       Dim Master As ListObject
           Set ws = ActiveSheet
           Set Master = ws.ListObjects("マスター")
           appNo = WorksheetFunction.VLookup(AppName, ws.Range(Master), 2, False)
           If user_name <> appNo Then
               MsgBox "入力された名前とログインユーザーのＩＤが一致しません。"
               Range("C3").ClearContents
           End If
           Sheets("メイン").Activate
           Application.ScreenUpdating = True
   End If
End Sub

Environ("UserName")で取得したログイン中のユーザー名と、署名しようとした名前の整合性を確認するコードになっています。

標準モジュールではなく、署名欄のあるシートに記述します。

◇使い方

実際に動かしてみましょう。
プルダウンから「A部長」を選んでみます。

すると、こんなメッセージが表示されます。


ログインしているユーザー名とマスターのユーザーIDが一致しない限り、署名できないようになっていますね。

◇最後に

いかがだったでしょうか？
比較的短いコードですが、汎用性の高いコードだと思います。

マスターにパスワード付きでシート保護をかけておけば大抵の人は、不正な署名をすることが出来なくなります。

「書類の電子化をしてみたいけど、お金はかけたくないなぁ」
という時は、このコードを活用してみてはいかがでしょうか？

この記事も誰かの役に立つと嬉しいです。

【VBA】配列を使ってSQL文(CREATE TABLE)をすっきりまとめる方法

◇前置き

私の勤め先ではMicrosoft Accessが至るところで利用されています。
昔から使われているようで、別のデータベースに切り替える予定もなさそうです。

そのため、データベース操作は基本的にVBAを使って行っています。

VBAでSQL文を定義・実行するのですが、カラムの定義を1個ずつしなくてはいけないため、CREATE句を使ったSQL文は長くなりがちです。

今日は、配列を使ってSQL文を定義する方法を紹介します。

◇概要

以下のようなテーブルを作成するSQL文を書いていきます。
テーブル名は「Table1」とします。

◇配列を使わない場合

まずは比較対象として配列を使わない場合のSQL文を見てみましょう。
こんな感じになります。

Dim strSQL As String
   strSQL = "CREATE TABLE Table1(" & _
                   "Column1 DATE," & _
                   "Column2 LONG," & _
                   "Column3 DOUBLE," & _
                   "Coulmn4 STRING," & _
                   "Column5 INTEGER)"

カラム数が少なく、他の句(PRIMARY KEY等)がないのでスッキリして見えますが、カラム数が数十個になってくるとかなり見づらくなってしまいます。

※ちなみにVBAでは改行記号「& _」は1つのコード中に24回までしか使えません。
　↑少し凝ったSQLを定義しようとするとあっさり足りなくなったりします。

◇配列を使った場合

ここからが本題になります。
配列を使ったSQL文はこんな感じになります。

Dim strSQL As String
Dim myarr(4) As String
   myarr(0) = "Column1 DATE"
   myarr(1) = "Column2 LONG"
   myarr(2) = "Column3 DOUBLE"
   myarr(3) = "Coulmn4 STRING"
   myarr(4) = "Column5 INTEGER"
   strSQL = "CREATE TABLE Table1(" & Join(myarr, ",") & “)”

配列の定義があるのでプログラム全体の長さは大差ありません。
しかし、SQL文のみで比較するとかなりスッキリしたと思います。

↓SQL文だけを比較するとこんな感じですね。

‘配列なし
strSQL = "CREATE TABLE Table1(" & _
               "Column1 DATE," & _
               "Column2 LONG," & _
               "Column3 DOUBLE," & _
               "Coulmn4 STRING," & _
               "Column5 INTEGER)"
‘配列あり
strSQL = "CREATE TABLE Table1(" & Join(myarr, ",") & “)”

今回の肝はJoin()の部分ですね。
Join()は、一つ目の引数に配列名、2つ目の引数に区切り文字を指定して使います。
指定した配列を指定した区切り文字で区切って、1つの文字列にしてくれます。

◇最後に

いかがだったでしょうか。

割とマニアックな内容ではあるのですが、日頃の業務で重宝している方法なので、ご紹介させていただきました。

この記事の例のようにシンプルなケースでは、あまり重宝しないかもしれません。

しかし、SQL文の中に変数をねじ込んだり、そもそものカラム数がめちゃくちゃ多かったり、何段階にもネストしないといけなかったり 等々、複雑なことをやる時には非常に役に立つ知識だと思います。

この記事も誰かの役に立つと嬉しいです。

【VBA入門】環境構築の手順

◇前置き

「VBAプログラミングをやってみたいけど、始め方が分からない！」

そんな人のために、VBA開発環境の設定方法を紹介します。とても簡単に設定できるので、少しでも興味のある方は、ぜひやってみてください。

◇必要なもの

VBAを使うために必要なものは、「ExcelのインストールされたPC」のみです。

PCがない人は、PCを買ってきましょう！PCにExcelがインストールされていない方には、「MicroSoft365」がおすすめです。

Microsoft365とは？

　Microsoftが提供するサブスク型のOfficeアプリケーションです。従来のような買い切り型のアプリケーションとは異なり、毎月定額の支払いが生じますが、常に最新版を利用することができます。不要になったら、簡単に解約できるので「とりあえずお試しで短期間だけ利用してみたい」という方にもおすすめです。

◇設定手順

さあ、PCとExcelの準備ができたら早速設定していきましょう！

VBAの環境設定は、簡単2ステップです！

（他の言語は環境の構築がめんどうくさいですからねぇ・・・。）

①開発タブの追加

Excelを起動したら「オプション」をクリックしてください。

オプション画面が開いたら、「リボンのユーザー設定」から「開発」にチェックを入れてください。

メニューバーに「開発」タブが追加されていれば、1つ目の設定は完了です。

②マクロの有効化

先ほど追加した「開発」タブを開いて、「マクロのセキュリティ」をクリックしてください。

トラストセンター画面が開いたら「警告して、VBAマクロを無効にする(A)」にチェックを入れてください。

これでVBA開発環境の設定は完了です！（簡単だったでしょう？）

ここまで出来た方々は「この後はどうすれば・・・？」となっていることでしょう。今回は、プログラムを書く一歩手前までの手順をご紹介します。

ここからの手順は簡単3ステップです！

①「マクロ有効ブック」として保存する。

Excelファイルを開いたら「名前を付けて保存」をクリックし、「マクロ有効ブック（*.xlsm）」を選択してファイルを保存してください（ファイル名は何でもOKです）。

これで1つ目の手順は完了です！

②VBE(Visual Basic Editor)を開く。

先ほどのExcelファイルで、「開発」タブを開き、「Visual Basic」をクリックしてください。

VBEが起動したら、2つ目の手順も無事完了です！

③標準モジュールを追加する。

いよいよ最後の手順です。「挿入」タブを開き、「標準モジュール」をクリックしてください。

「Modue1」が追加され、テキストエディタ（プログラムを書く場所）が開いたら、プログラミングの準備完了です！

あとは思いのままにプログラムを書いちゃってください！！

◇最後に

今回は、VBA開発環境の設定方法について紹介しました。VBAの環境構築は、他の言語と比べて非常にお手軽なので、これからプログラミングを始める人にはオススメです。

この記事も誰かの役に立つと嬉しいです。

【VBA】ファイルの存在有無を確認するマクロ

◇前置き

VBAでマクロを組む際、.csvや.txt等のファイルからデータを参照することってありませんか？
私はよくあります。

データ参照(インポート含む)の流れはざっくりこんな感じです。

①参照するファイルパス(ファイル名)を指定

②データ参照を実行

この流れの中で、指定した名前のファイルが存在しなければ実行時エラーが出ます。
↑デバックか終了を選ぶメッセージボックスです。

コードを読める人であれば、デバックから「あぁ、ファイルがないのか。」とすぐわかりますが、読めない人はパニックになること必至です。

今回はこのエラーを回避し、「ファイルがないよ！」と知らせてくれるマクロを紹介します。

◇概要

こんなExcelを用意しました。

ファイルパスはC3に入力する仕様です。
マクロはボタンに登録します。

C3にファイルパス(今回はExcelファイル)を入力してボタンをクリックすると、指定したExcelファイルが存在する場合は開き、存在しない場合はその旨のメッセージを表示します。

◇ソースコード

今回のソースコードはこんな感じです。

Sub File_check()
 
Dim FilePath As String
FilePath = Range("C2").Value
 
If Dir(FilePath) = "" Then
   MsgBox "指定されたファイルは存在しません。"
   Exit Sub
End If
 
Workbooks.Open (FilePath)
MsgBox "指定のファイルを開きました。"
 
End Sub

今回の肝となるのが、「Dir()」です。

これは()内で指定したファイルが存在する場合はファイル名を、存在しない場合は空文字を戻り値として返す関数です。

※Dir関数は、ワイルドカードが使ったり、ファイル属性を指定したりすることで複雑な処理をすることも可能な関数です。
　↑今回はシンプルにファイルパスで検索をかけています。

◇使い方

実際に動かしてみます。

デスクトップに新しいフォルダを用意しました。

このフォルダ内にある「test.xlsx」を開きたい、という設定で進めます。

まずはフォルダ内が空のまま進めます。
C3にファイルパスを入力します。


ボタンをクリックすると・・・・

メッセージボックスが出てきました。
OKをクリックしてプロシージャを終了することで、実行時エラーを回避できます。

次は、フォルダ内に「test.xlsx」を配置して実行してみます。

この状態でボタンをクリックすると・・・・

test.xlsxが開きました！

狙い通り、ファイルの存在有無で処理を分岐することができました。

◇最後に

今回は、「別のExcelファイルを開く」というシンプルな処理を例に紹介しましたが、csvのインポートやデータのコピー等にも利用できます。

マクロを個人利用する場合は、ここまで気をつかう必要もないのですが、複数人で共有する場合は今回のような配慮が大切になってきます。

この記事も誰かの役に立つとうれしいです。

【VBA】重複を削除するマクロ

◇前置き

大量のデータを扱っていると、重複の確認が大変だったりしませんか？
今日はワンクリックで重複したデータを削除するマクロを紹介します。

◇概要

サンプルとしてこんなExcelを用意しました。
※行数や列数は必要に応じて増やしてください。

データを入力してボタンを押すと、重複したデータを削除してくれます。
今回は、項目1・2ともに完全に重複したデータだけを削除します。
※項目1が重複していても、項目2の値が違えば別データとして残します。

◇ソースコード

今回のソースコードがこんな感じです。

Sub Overlap_Delete()
Dim max As Long
   max = Cells(Rows.Count, 2).End(xlUp).Row
   Range("B4:C" & max).RemoveDuplicates Array(1, 2)
   Range("B4:C13").Borders.LineStyle = xlContinuous
   MsgBox "重複データの確認・削除が完了しました。"
End Sub

非常にシンプルなコードですね。
今回は、B/C列しか使っていませんが実際に使う時は、Cのところを実際に使うデータ範囲に書き換えて使用してください。

また重複の判定に使用する列は、Array()で指定しています。
判定用の列を変えたい・増やしたいときはArayy()の中身を編集してください。

◇使い方

それでは実際に使っていきましょう。
とりあえず適当にデータを入力します。

ボタンをクリックすると・・・

無事、重複したデータのみが削除されました！

◇最後に

今回は例として少ないデータ数で紹介しましたが、データや列が多ければ多いほど、このマクロの有難味が増すと思います。

コードも非常にシンプルでVBAを始めたての方にも、とっつきやすいマクロだと思います。

この記事も誰かの役に立つと嬉しいです。

【VBA】フォームコントロールのボタンを使ったリンク集

◇前置き

先日、いろんなところに保存されたExcelを開くためのショートカット集を作ってほしいという依頼があり、ショートカット集を作る機会がありました。

最初は「ハイパーリンクでいいや」と思っていたのですが、「見た目がかっこ悪い！」的な指摘を受け、コントロールフォームのボタンを採用することになりました。

今日はボタンに登録したマクロのコードを紹介します。

◇概要

今回のショートカット集はこんな感じです。
※仕事で作った実物はファイルパスを別シートに記載しています。
　↑分かりやすいようにボタンの隣に並べてみました。

Ｆ列にファイルパスを入力すると対応するボタンにリンクが張られます。

◇ソースコード

ソースコードはこんな感じです。

Sub Link1()
 
   Dim book1 As Workbook
   Set book1 = ActiveWorkbook
   Workbooks.Open Filename:=Range("F4").Value
   
End Sub
 
Sub Link2()
 
   Dim book1 As Workbook
   Set book1 = ActiveWorkbook
   Workbooks.Open Filename:=Range("F5").Value
   
End Sub
 
Sub Link3()
 
   Dim book1 As Workbook
   Set book1 = ActiveWorkbook
   Workbooks.Open Filename:=Range("F6").Value
   
End Sub
 
Sub Link4()
 
   Dim book1 As Workbook
   Set book1 = ActiveWorkbook
   Workbooks.Open Filename:=Range("F7").Value
       
End Sub

この仕様だと1ボタンに1プロシージャ必要です。
今回の例ではボタンを4つ配置しているのでプロシージャも4つ用意します。

それぞれのプロシージャで内容が異なるのはここだけです。

Workbooks.Open Filename:=Range("F4").Value

Range()でファイルパスを参照しているので、ここの中身だけ合わせてあげればコピペで何個でもプロシージャを増やせます。

◇使い方

実際に動かしていきましょう。
今回はデスクトップに「リンク先」というフォルダを作り、その中に適当なExcelファイルを4つ用意しました。

※実際に使う時はリンク先がバラバラでもOKです。

まずはF列にファイルパスを入力します。

リンク①をクリックしてみると……

無事リンク先のリンク①.xlsxが開きました！

◇最後に

リンクを貼るだけならハイパーリンクの方が楽なのですが、見た目的にはボタンの方が多少スッキリしますね。

ファイルパスについてはVBEから直打ちでも良いのですが、そうすると使用感がハイパーリンクに劣ってしまう気がします。
※いちいちVBEを開かないとリンク先の編集ができませんから。

そこで今回は、特定のセルに入力したファイルパスをRange()でプログラム中に反映させる手法を用いました。

使う人がVBAを使えないケースも往々にしてありますので、こういった工夫は意外と大切だと思います。

※そもそも自分でコードをかける人は、わざわざこんなこと頼んできませんよね。

この記事も誰かの役に立つと嬉しいです。

【VBA】8進数を10進数に変換するマクロ

◇前置き

先日、10進数を8進数に変換するマクロを紹介しました。
↓詳しくはこちら
wwwmotou.hatenablog.com

毎度のことながら逆も作ってしまおう！ということで8進数を10進数に変換するマクロを紹介します。

◇概要

いつものようにこんなExcelを用意します。

B列に8進数を入力して変換ボタンをクリックするとC列に10進数が出力されます。

◇ソースコード

ソースコードはこんな感じです。

Sub Number_8_to_10()
 
Dim Num8 As Long
Dim i As Integer
Dim count As Integer
   i = 0
   count = 0
   
   For i = 3 To 14
       Num8 = Range("B" & i)
       If Range("B" & i).Value <> "" Then
           Range("C" & i).Value = WorksheetFunction.Oct2Dec(Num8)
           count = count + 1
       End If
   Next
 
   MsgBox count & "件のデータを8進数⇒10進数に変換しました。"
 
End Sub

◇使い方

実際に動かしてみましょう。

まずは適当な値をB列にいれます。

変換ボタンをクリックすると........

無事変換出来ました！

◇最後に

このマクロ単体で見ると、さほど便利なものではありません。
※マクロを組まなくても、ワークシート関数だけで実現出来ますからね。

他の処理と組み合わせることで便利なマクロに仕上げることが可能です。

この記事も誰かの役に立つとうれしいです。