IT関連

AS400/IBMi からデータを取得 ExcelVBA クラス化編

注意

これは私の環境下における私の備忘録です。お使いの環境によっては内容が異なる場合があります。システム管理者でない方は絶対にやらないでください。システムにはログが残るものなので、万が一があればクビになるかもしれません。私は何も責任を負いませんのでご了承のうえご覧ください。

 

内容

一応私はVBAエキスパート(ベーシック・スタンダードの両方)という就職や転職に絶大な威力を発揮しない、100%自己満足の資格を持っているのですが、クラスのことを全く知らないのにエキスパートも無いだろうということで、前述のデータアクセスをクラスっぽくしてみました。PHPの勉強をしたときクラスのことは少しだけ理解したけど、どういう時に使うのか解らなかったというのも、やろうと思った理由です。こういう使い方で合っているのかよく解っていませんが、もし正しいやり方があればご教示頂きたいです。

 

なおクラス化のお題は前述した、

AS400からデータを取得 ExcelVBA 設定編

AS400からデータを取得 ExcelVBA カラム名COLHDG取得編

に基づいています。

 

まずクラスモジュールを作ります

VBEを開き、挿入からクラスモジュールを選びます。そしてオブジェクト名をDB_Access(仮名)にします。

 

クラスモジュールに全メソッドからアクセスできるオブジェクトを定義します

Public sSQL As String
Private oADOcn As New ADODB.Connection
Public oRst As New ADODB.Recordset
Private Const HOSTNAME As String = "アイピーアドレス"
Private Const UID As String = "ログインアイディー"
Private Const UPS As String = "パスワード"

設定編で定義したものを共通で定義します。ちょっと何言っているかわからないと言う人は、設定編を先にご覧ください。

クラスモジュールの外から利用するsSQL(プロパティ)とoRst(メソッド)のみPublicで指定しています。その他はPrivateです。

 

コンストラクタを作ります

VBAではInitialize(イニシャライズ)と言うのかも知りませんが、共通処理と思ってください。

Private Sub Class_Initialize()

    ’データベースopen
    oADOcn.Open "Provider=IBMDA400; Data Source=" & HOSTNAME & ";", UID, UPS

End Sub

データベースにアクセスしてテーブルの情報を取得するクラスにするつもりなので、データベースを開く動作は全て共通ということで、共通処理に定義しました。

 

メソッドを作ります

まず最初に簡単にテーブル情報だけ取得するメソッドです。

Sub Get_Posts()

'/--------------------------------
'/テーブルとカラムのオブジェクトだけ取得する
'/--------------------------------

    'テーブルopen
    oRst.Open sSQL, oADOcn, adOpenKeyset, adLockReadOnly

End Sub

メソッドとは命令ですね。今回定義した命令名がGet_Postsという名前です。内容にテーブルを開くだけの処理を書きました。

 

 

次に、最初に設定編で行った、セルA1に全レコードを張り付けるメソッドです。

Sub Get_Posts_Paste()

'/--------------------------------
'/セルA1にデータを張り付ける
'/--------------------------------
       
    'テーブルopen
    oRst.Open sSQL, oADOcn, adOpenKeyset, adLockReadOnly
    
    'A1に全レコード貼り付け
    ActiveSheet.Cells(1, 1).CopyFromRecordset oRst
   
End Sub

設定編の内容と同じなので、難しくないと思います。

 

Get_Postsがテーブルを取得するだけ、Get_Posts_Pasteがテーブルを取得してセルA1に張り付けるまで、という2つの命令を作りました。

 

これで簡単なクラスは完成しました。

SQL文の挿入はこのクラスを呼び出す側で行います。

 

 

実際に呼び出してみます

こんどは標準モジュールを作り、その中に処理を書いていきます。

 

A1を起点に全レコードを張り付けるメソッドを呼び出す

Sub ALL_DATA()

  'DB_ACCESSクラスをインスタンス化
  Dim db As New DB_Access

  'SQL文を代入
  db.sSQL = "SELECT * FROM ライブラリ名.テーブル名"

  'メソッド実行(テーブルを呼び出し、内容をA1に貼り付け)
  db.Get_Posts_Paste

End Sub

これでA1に全レコードが張り付きます。

 

 

各レコードごとに処理をするメソッドを呼び出す

Sub One_DATA()

  'インスタンス化
  Dim db As New DB_Access

  '行変数定義
  Dim i As Long

  'セル1行目から
  i = 1

  'SQL文組み立て
  db.sSQL = "SELECT * FROM ライブラリ名.テーブル名"

  'メソッド実行
  db.Get_Posts

  'テーブルを最終行まで回す
  Do Until db.oRst.EOF

    '各値を取得
    Cells(i, 1).Value = db.oRst.Fields("name").Value
    Cells(i, 2).Value = db.oRst.Fields("age").Value
    Cells(i, 3).Value = db.oRst.Fields("birth").Value

    'テーブルのレコードを1行送り
    db.oRst.MoveNext

    '行変数を1加算
    i = i + 1

  Loop

End Sub

Do Untilで回して処理しています。

 

 

クラスについて

クラスはいくら勉強してもさっぱり解りませんし身に付きません。色んな教材を見てきましたが、どの教材もこれでもか言うくらい丁寧に丁寧に書いてくれているのですが、読み終えてみると、やっぱり何の成果も得られませんでしたぁぁぁぁ!(絶叫)になるのです(笑)

 

そんな私が、あくまで自分の知識を深める為(自分のため)にクラスを解説してみます。底辺レヴェルの私が説明すれば詳しく説明できると思っています。

 

繰り返しますが、いま書いた場所は標準モジュールです。最初に作ったのはDB_ACCESSという名前のクラスです。クラスは設計図とも言われます。

Sub ALL_DATA()

  '①
  Dim db As New DB_Access

  '②
  db.sSQL = "SELECT * FROM ライブラリ名.テーブル名"

  '③
  db.Get_Posts_Paste

End Sub

 

なんで設計図を作るのか。それは使いまわしをするからです。プログラムとは同じコードを別の場所に何度も書くのは無駄無駄だと言われますよね。使いまわしは善なのです。

車の部品を作るときでも、設計図や型を作るはずです。大量生産にはそれが効率が良いからです。それが先ほど作ったクラスだと思ってください。

 

①で行っていることは、設計図を元にdbという名前で実体化しました。①のNewという宣言がそういう意味です。車の設計図を元に、車本体を作ったのと同じです。

これを実体(インスタンス)と呼びます。

このdbという実体(インスタンス)には、先ほど作ったGet_Postsと、Get_Posts_Pasteという2つのメソッド、そしてsSQLというプロパティを持っています。

持っていますと唐突に言いましたが、先ほどクラスを作るときに書いたアレです。

 

そして②の行で、db.sSQLにSQL文を代入しています。作成したdbという実体の、sSQLというデータ領域(プロパティ)にSQL文を代入したわけです。

これでdbという実体が持つsSQLという変数の中にSQL文が入りました。

 

そして最後の③の行で、dbという実体に備え付けたGet_Posts_Pasteというメソッドを呼び出します。

 

Get_Posts_Pasteを一部抜粋しました。

Sub Get_Posts_Paste()
       
    '④
    oRst.Open sSQL, oADOcn, adOpenKeyset, adLockReadOnly
    '⑤
    db.Get_Posts_Paste
   
End Sub

④にsSQLという変数があると思います。この中に標準モジュールの②で組み込んだSQL文を入れて、テーブル情報を呼び出しています。

そして⑤でエクセルのシート⑤に張り付けています。

 

なんでクラスとか設計図とかインスタンス化だとか実体とか、しないといけないの?と言われると私もあまり解っていません。

 

ただ、標準モジュールに書く1つのプログラムでデータベースを2つ3つから取得しないといけない場合があります。

例えば、データベースから認証情報テーブルを参照して、このプログラムを実行するにふさわしい権限があれば機密情報テーブルを照会するプログラムを実行する。こういう処理を書くと、認証データベースと機密情報テーブルの両方にアクセスすることになります。

このときインスタンスを下記のように、
dim Auth As New DB_Access
dim Sec As New DB_Access
DB_Accessの実体を2つ作ればそれぞれで独立してプログラム上にテーブル情報を存在させられます。

Auth.sSQL = "SELECT * FROM HOGELIB.AUTHTABLE"
Auth.Get_Posts

と、

Sec.sSQL = "SELECT * FROM HOGELIB.SECRETTABLE"
Sec.Get_Posts

と言った感じで、それぞれ独立して処理ができます。しかも書いたクラスは1つです。

こういうのがクラスの使い道かなと思っております。

 

クラスは一度書けば、標準モジュール10個、プログラムが100本あっても、下記みたいな定義は1回書けば済むわけです。確かに楽です。

    'ADODBクラスインスタンス
    Dim oADOcn As New ADODB.Connection
    Dim oRst As New ADODB.Recordset
 
    '定数定義
    Const HOST As String = "マシンノアイピーアドレス"
    Const USERID As String = "ログインアイディー"
    Const PASSWORD As String = "パスワード"

 

クラスとファンクションの違いは、あまりうまく説明できません。

 

SQL文を引数で与えて、セルA1に張り付けるFunction、ループで回して云々するFunctionをそれぞれ作ればいいんじゃないの?

と思われるかもしれません。私もそう思います。

ただ、今まで作ってきた感じでは、クラスの方が作りが楽ですね。変数を保持できますし、切り口ごとにメソッドも追加できますので。

 

私の解説はこんな感じです。お粗末さまでした。

-IT関連
-, ,

© 2021 なんで勉強するの