エクセルマクロ オンライン講座コメント紹介

コメント紹介

[11368] 2019-04-20 14:11:42 田中 宏明さんからの投稿です。

小川慶一先生:

その後、別の解法を探し、ArrayListと呼ばれる配列(自作クラス)でうまく動作しました。ファイルも添付しますので、この感激を共有していただければ幸いです。

Sub mondai4_Arr1() 'ArrayListに数値を格納

    Range("J1:O" & Range("O" & Rows.Count).End(xlUp).Row).ClearContents '初期化
    
    Dim cFm As Long
    Dim dic As Scripting.Dictionary '市区名毎の情報管理用
    Set dic = New Scripting.Dictionary
    Dim st As String
    
    For cFm = 2 To Range("A" & Rows.Count).End(xlUp).Row
        st = Range("C" & cFm).Value
        If Not dic.Exists(st) Then
            'インスタンス化したArrayListへの参照をDictionaryに格納
            dic.Add st, New ArrayList
        End If
        'ArrayListに行番号を追加
        dic.Item(st).Add cFm
    Next cFm

    Dim cBe As Long
    Dim cTo As Long
    Dim sp() As String
    cTo = 2
    
    For cFm = 0 To dic.Count - 1
        st = dic.Keys(cFm)
        Range("J" & cTo).Value = st & "のマンションは" & dic.Item(st).Count & "件ヒットしました!"
        cTo = cTo + 1
        
        '市区名をキーとするDictionaryが参照したArrayListから行番号を取り出す
        For cBe = 0 To dic.Item(st).Count
            Range("K" & cTo).Value = Range("F" & dic.Item(st).GetVal(cBe)).Value
            Range("L" & cTo).Value = Range("D" & dic.Item(st).GetVal(cBe)).Value
            Range("M" & cTo).Value = Range("E" & dic.Item(st).GetVal(cBe)).Value
            sp = Split(Range("G" & dic.Item(st).GetVal(cBe)).Value, "/")
            Range("N" & cTo).Value = sp(0)
            Range("O" & cTo).Value = sp(1)
            cTo = cTo + 1
        Next cBe
        cTo = cTo + 1
    Next cFm
End Sub

 


[11370] 2019-04-22 11:23:07 小川慶一さんからの投稿です。

田中 宏明さん:

がんばっていますね。

mondai5_Col1と比較して、理解に必要な技術が増えてコードが増えて可読性が落ちたのは分かりました。
それで、その引き換えに得られたものは何でしょうか?
ぱっと見、クラスモジュールを持ち出したメリットが感じられません。

クラスモジュールのほうも、DoLoop内でインデントが崩れているところが何箇所かあるとか引数の型指定がないとかたとえば「ContainsU」てのは定義したけど使ってなさそうだけど?とか、気になることはいろいろあります。
これは、田中さんの自作なんでしょうか。
自作でないならば、そもそも New Collection で済ませれば簡単に済むところ、デメリットが増えるだけなのにもかかわらず別ライブラリを持ち出したこと自体ダメでしょう。「クラスモジュールの勉強のために」ということでしたら、スクラッチで書くべきです。
自作であるならば、クラスモジュール内のコードについてもご自身の責任において内容を検証し、コードをもっと洗練させるべきです。

参考用に、mondai5_Col2を作りました。
Collectionに数値ではなくセルへの参照を設定したサンプルです。

しつこく言いますけど、「クラスモジュール使ったほうが鮮やかに仕事が片付く」なんてこと、エクセルVBAではまずないですよ。
僕はdLibの開発以外で使ったことはありません。

'自作クラスArrayListを用いた別解
'Written by 達人養成塾 Hiroaki Tanaka

Sub mondai5_Arr1() 'ArrayListに数値を格納

    Range("J1:O" & Range("O" & Rows.Count).End(xlUp).Row).ClearContents '初期化
    
    Dim cFm As Long
    Dim dic As Scripting.Dictionary '市区名毎の情報管理用
    Set dic = New Scripting.Dictionary
    Dim st As String
    
    For cFm = 2 To Range("A" & Rows.Count).End(xlUp).Row
        st = Range("C" & cFm).Value
        If Not dic.Exists(st) Then
            'インスタンス化したArrayListへの参照をDictionaryに格納
            dic.Add st, New ArrayList
        End If
        'ArrayListに行番号を追加
        dic.Item(st).Add cFm
    Next cFm

    Dim cBe As Long
    Dim cTo As Long
    Dim sp() As String
    cTo = 2
    
    For cFm = 0 To dic.Count - 1
        st = dic.Keys(cFm)
        Range("J" & cTo).Value = st & "のマンションは" & dic.Item(st).Count & "件ヒットしました!"
        cTo = cTo + 1
        
        '市区名をキーとするDictionaryが参照したArrayListから行番号を取り出す
        For cBe = 0 To dic.Item(st).Count
            Range("K" & cTo).Value = Range("F" & dic.Item(st).GetVal(cBe)).Value
            Range("L" & cTo).Value = Range("D" & dic.Item(st).GetVal(cBe)).Value
            Range("M" & cTo).Value = Range("E" & dic.Item(st).GetVal(cBe)).Value
            sp = Split(Range("G" & dic.Item(st).GetVal(cBe)).Value, "/")
            Range("N" & cTo).Value = sp(0)
            Range("O" & cTo).Value = sp(1)
            cTo = cTo + 1
        Next cBe
        cTo = cTo + 1
    Next cFm
End Sub

'VBAのCollectionクラスを用いた別解
'Written by Keiichi Ogawa

Sub mondai5_Col1() 'Collectionに数値を格納

    Range("J1:O" & Range("O" & Rows.Count).End(xlUp).Row).ClearContents '初期化
    
    Dim cFm As Long
    Dim dic As Scripting.Dictionary '市区名毎の情報管理用
    Set dic = New Scripting.Dictionary
    Dim st As String
    
    For cFm = 2 To Range("A" & Rows.Count).End(xlUp).Row
        st = Range("C" & cFm).Value
        If Not dic.Exists(st) Then
            'インスタンス化したCollectionへの参照をDictionaryに格納
            dic.Add st, New Collection
        End If
        'Collectionに行番号を追加
        dic.Item(st).Add cFm
    Next cFm

    Dim Var As Variant
    Dim cTo As Long
    Dim sp() As String
    cTo = 2
    
    For cFm = 0 To dic.Count - 1
        st = dic.Keys(cFm)
        Range("J" & cTo).Value = st & "のマンションは" & dic.Item(st).Count & "件ヒットしました!"
        cTo = cTo + 1
        
        '市区名をキーとするDictionaryが参照したCollectionから行番号を取り出す
        For Each Var In dic.Item(st)
            Range("K" & cTo).Value = Range("F" & Var).Value
            Range("L" & cTo).Value = Range("D" & Var).Value
            Range("M" & cTo).Value = Range("E" & Var).Value
            sp = Split(Range("G" & Var).Value, "/")
            Range("N" & cTo).Value = sp(0)
            Range("O" & cTo).Value = sp(1)
            cTo = cTo + 1
        Next
        cTo = cTo + 1
    Next cFm
End Sub

Sub mondai5_Col2()
    Range("J1:O" & Range("O" & Rows.Count).End(xlUp).Row).ClearContents
    
    Dim cFm As Long
    Dim dic As Scripting.Dictionary
    Set dic = New Scripting.Dictionary
    Dim st As String
    
    For cFm = 2 To Range("A" & Rows.Count).End(xlUp).Row
        st = Range("C" & cFm).Value
        If Not dic.Exists(st) Then
            dic.Add st, New Collection
        End If
        dic.Item(st).Add Range("C" & cFm) '[1] 数値ではなくセルへの参照を設定
    Next cFm

    Dim Var As Range '[2] せっかくなので、データ型をVariant型→Range型に変更
    Dim cTo As Long
    Dim sp() As String
    cTo = 2
    
    For cFm = 0 To dic.Count - 1
        st = dic.Keys(cFm)
        Range("J" & cTo).Value = st & "のマンションは" & dic.Item(st).Count & "件ヒットしました!"
        cTo = cTo + 1
        
        For Each Var In dic.Item(st) '以下ではOffsetで表現する
            Range("K" & cTo).Value = Var.Offset(, 3).Value
            Range("L" & cTo).Value = Var.Offset(, 1).Value
            Range("M" & cTo).Value = Var.Offset(, 2).Value
            sp = Split(Var.Offset(, 4).Value, "/")
            Range("N" & cTo).Value = sp(0)
            Range("O" & cTo).Value = sp(1)
            cTo = cTo + 1
        Next
        cTo = cTo + 1
    Next cFm
End Sub


Option Explicit
'//----------------------------------------------------------------------------
'// ArrayListクラス
'//----------------------------------------------------------------------------

'引用サイト
'Excel作業をVBAで効率化 -> VBAでArrayListクラス
'https://vbabeginner.net/vba%E3%81%A7arraylist%E3%82%AF%E3%83%A9%E3%82%B9/

Private mvArray() As Variant

'//----------------------------------------------------------------------------
'// 関数名  :Class_Initialize
'// 引数    :なし
'// 戻り値  :なし
'// 機能    :コンストラクタ
'// 備考    :
'//----------------------------------------------------------------------------
Private Sub Class_Initialize()
    ReDim mvArray(0)
End Sub

'//----------------------------------------------------------------------------
'// 関数名  :Add
'// 機能    :値をクラスに追加する
'// 引数    :(I) asVal
'// 戻り値  :なし
'// 備考    :JavaのArrayList.Addと一緒
'//----------------------------------------------------------------------------
Public Sub Add(asVal)
'    On Error Resume Next
    
    Dim vArray() As Variant
    Dim iCnt As Long
    
    iCnt = Me.Count
    
    If (IsEmpty(mvArray(iCnt)) = True) Then
        mvArray(iCnt) = asVal
    Else
        ReDim Preserve mvArray(iCnt + 1)
        mvArray(iCnt + 1) = asVal
    End If

End Sub

'//----------------------------------------------------------------------------
'// 関数名  :Remove
'// 機能    :値をクラスから削除する
'// 引数    :(I) asVal
'// 戻り値  :なし
'// 備考    :JavaのArrayList.Removeと一緒
'//----------------------------------------------------------------------------
Public Sub Remove(aiIndex)
'    On Error Resume Next
    
    Dim vArray() As Variant
    Dim iCnt     As Long
    Dim i        As Long        '// ループカウンタ
    Dim iAdjust  As Long        '// mvArrayからvArrayへコピーするためのIndex

    iAdjust = 0
    iCnt = Me.Count
    ReDim vArray(iCnt - 1)      '// 要素を削るのでその分もマイナスする
    
    For i = 0 To iCnt
        If (i <> aiIndex) Then
            vArray(iAdjust) = mvArray(i)
            iAdjust = iAdjust + 1
        End If
    Next i
    
    Me.Clear
    ReDim mvArray(iAdjust)
    mvArray = vArray

End Sub

'//----------------------------------------------------------------------------
'// 関数名  :GetVal
'// 機能    :指定要素位置の値を取得する
'// 引数    :(I) aiIndex
'// 戻り値  :指定要素位置の値
'// 備考    :JavaのArrayList.Getと一緒
'//----------------------------------------------------------------------------
Public Function GetVal(aiIndex)
    Dim vRet As Variant
    
    If (aiIndex > Me.Count) Then
        vRet = Null
    Else
        vRet = mvArray(aiIndex)
    End If
    
    GetVal = vRet
End Function

'//----------------------------------------------------------------------------
'// 関数名  :GetIndex
'// 機能    :指定要素のリスト内で最初に検出された位置のインデックスを取得する
'// 引数    :(I) asVal
'// 戻り値  :指定要素のインデックス。要素がない場合は-1を返す。
'// 備考    :JavaのArrayList.indexOfと一緒
'//----------------------------------------------------------------------------
Public Function GetIndex(asVal) As Long
'    On Error Resume Next
    
    Dim iCnt As Long
    Dim i    As Long         '// ループカウンタ
    Dim bExistFlg As Boolean '// 要素存在フラグ(True:要素あり、False:なし)
    Dim iIndex As Long
    
    bExistFlg = False
    iCnt = Me.Count
    
    For i = 0 To iCnt
        If (mvArray(i) = asVal) Then
            bExistFlg = True
            Exit For
        End If
    Next
    
    If (bExistFlg = True) Then
        iIndex = i
    Else
        iIndex = -1
    End If
    
    GetIndex = iIndex
End Function

'//----------------------------------------------------------------------------
'// 関数名  :Clear
'// 機能    :配列要素をクリアする
'// 引数    :なし
'// 戻り値  :なし
'// 備考    :JavaのArrayList.Clearと一緒
'//----------------------------------------------------------------------------
Public Function Clear()
    Call Class_Initialize
End Function

'//----------------------------------------------------------------------------
'// 関数名  :Count
'// 機能    :配列要素数を調べる
'// 引数    :なし
'// 戻り値  :Long :マッピング数
'// 備考    :JavaのArrayList.Countと一緒
'//----------------------------------------------------------------------------
Public Function Count() As Long
    Count = UBound(mvArray)
End Function

'//----------------------------------------------------------------------------
'// 関数名  :Contains
'// 機能    :引数値がリストに含まれるかを確認する
'// 引数    :(I) asVal
'// 戻り値  :Boolean :True:引数がリストに含まれる、False:含まれない
'// 備考    :JavaのArrayList.containsと一緒
'//----------------------------------------------------------------------------
Public Function Contains(asVal) As Boolean
    Dim iCnt As Long
    Dim iListCnt As Long
    Dim bRet As Boolean
    
    bRet = False
    iListCnt = Me.Count
    
    Do While (iCnt <= iListCnt)
        If (asVal = mvArray(iCnt)) Then
            bRet = True
        Exit Do
    End If
    
    iCnt = iCnt + 1
    Loop
    
    Contains = bRet
End Function

'//----------------------------------------------------------------------------
'// 関数名  :ContainsU
'// 機能    :引数値がリストに含まれるかを確認する。大文字で統一して比較。
'// 引数    :(I) asVal
'// 戻り値  :Boolean :True:引数がリストに含まれる、False:含まれない
'// 備考    :JavaのArrayList.containsと一緒
'//----------------------------------------------------------------------------
Public Function ContainsU(asVal) As Boolean
    Dim iCnt As Long
    Dim iListCnt As Long
    Dim bRet As Boolean
    
    bRet = False
    iListCnt = Me.Count
    
    Do While (iCnt <= iListCnt)
    If (UCase(asVal) = UCase(mvArray(iCnt))) Then
        bRet = True
        Exit Do
    End If
    
    iCnt = iCnt + 1
    Loop
    
    ContainsU = bRet
End Function

'//----------------------------------------------------------------------------
'// 関数名  :ContainsInstr
'// 機能    :引数文字列値がリスト文字列の一部に含まれるかを確認する。大文字で統一して比較。
'// 引数    :(I) asVal
'// 戻り値  :Boolean :True:引数がリストに含まれる、False:含まれない
'// 備考    :JavaのArrayList.containsと一緒
'//----------------------------------------------------------------------------
Public Function ContainsInStr(asVal) As Boolean
    Dim iCnt As Long
    Dim iListCnt As Long
    Dim bRet As Boolean
    
    bRet = False
    iListCnt = Me.Count
    
    Do While (iCnt <= iListCnt)
        If (InStr(1, UCase(asVal), UCase(mvArray(iCnt))) > 0) Then
            bRet = True
            Exit Do
        End If
    
        iCnt = iCnt + 1
    Loop
    
    ContainsInStr = bRet
End Function


> 小川慶一先生:
>
> その後、別の解法を探し、ArrayListと呼ばれる配列(自作クラス)でうまく動作しました。ファイルも添付しますので、この感激を共有していただければ幸いです。

 


[11371] 2019-04-23 00:34:03 田中 宏明さんからの投稿です。

小川慶一先生:

確かに、あるデータ転記マクロを小川先生の“dLib for Excel VBA”に対応させた際、標準モジュールのコードが簡潔になり、可読性が良くなりましたので、ご指摘の内容を理解できました。
お忙しいところ、ありがとうございました。

> ぱっと見、クラスモジュールを持ち出したメリットが感じられません。
> …
> しつこく言いますけど、「クラスモジュール使ったほうが鮮やかに仕事が片付く」なんてこと、エクセルVBAではまずないですよ。
> 僕はdLibの開発以外で使ったことはありません。

 


[11373] 2019-04-23 22:55:50 小川慶一さんからの投稿です。

田中 宏明さん:

まとめます。

[1]
代替の技術を導入するならば、それによるメリットを明らかにすべきです。
デメリットしかないならば、その代替技術を導入することはとりやめるのが正解です。

[2]
[1]のメリットは「自分の勉強になる」ということでもよいですが、その場合は、その代替技術の部分については、自分でコードを書くべきです。
であれば、よい学びになるかもしれません。

[3]
エクセルVBAでは、すでに定義済、インスタンス生成済のオブジェクト(クラス)が十分に用意されています。
その十分さは、それらの既存オブジェクトだけでほぼすべての処理を自動化できるレベルです。

[4]
[1],[3] より、クラスモジュールを導入するメリットはエクセルVBAではまずありません。


[*] さらに、以下は、補足です。
そもそも、クラスモジュールを定義することが有効なのは、同一の性質を持ちつつも別のふるまいをする、複数のインスタンスを生成することに意義がある場合です。
逆に言うと、独自に定義した複数のインスタンスを作るのでなければ、クラスモジュールを作る意味はありません。
dLibがクラスモジュールを使っているのは、表領域の再定義を表のインスタンスごとに行うためです。


> 小川慶一先生:
>
> 確かに、あるデータ転記マクロを小川先生の“dLib for Excel VBA”に対応させた際、標準モジュールのコードが簡潔になり、可読性が良くなりましたので、ご指摘の内容を理解できました。
> お忙しいところ、ありがとうございました。
>
> > ぱっと見、クラスモジュールを持ち出したメリットが感じられません。
> > …
> > しつこく言いますけど、「クラスモジュール使ったほうが鮮やかに仕事が片付く」なんてこと、エクセルVBAではまずないですよ。
> > 僕はdLibの開発以外で使ったことはありません。

 


まずはここから!スマホでも学べる無料動画講座

今なら先着30名限定で無料!定価4,800円の、初心者のためのエクセルマクロ動画講座。
  1. Excel 97~Excel 2016まですべて対応。動画本数20本、総再生時間2時間44分53秒
  2. PC, Mac, iPhone, iPad, Androidのお好みの環境で、いつでも好きなときに学べます。
2004年から10年間述べ3,000名以上に実施した研修の経験と実績を集約した講座です。
いますぐ無料で試してください。

トップへ