VBA Design Pattern

'SELECT RECORD' from Sheet

'Standard Module, Defenition TYPE

Type Employee
  ID as String
  PersonalName as String
  FamilyName as String
  PhoneNumber as String
End Type

Sub FindPhoneNumber(ByVal Id as String)

Dim Employee as Employee
Employee = EmployeeList.GetPersonalData(Id)

MsgBox Employee.PersonalName & Employee.FamilyName & bvlf & Employee.PhoneNumber

End Sub

'WorkSheet Object "EmployeeList" Code 
Function GetPersonalData(ByVal ID as String) as Employee

  Dim FoundRow
  FoundRow = Me.Column("A").Find(ID).row

  With GetPersonalData
    .PersonalName = Me.Range("B" & FoundRow).Value
    .FamilyName = Me.Range("C" & FoundRow).Value
    .PhoneNumber = Me.Range("D" & FoundRow).Value
  End With

End Function

Get-Set Method in SheetObject

'In WorkSheet Object

Sub SetFirstName(ByVal FirstName as String)

  Dim LastRow as Long
  LastRow = Me.Usedrange.rows.count
  Me.Cells(LastRow + 1, 1).Value = LastRow + 1
  Me.Cells(LastRow + 1, 2).Value = FisrtName

End Sub

Function GetFirstName(ByVal ID as String) as String

  Dim FoundRow
  FoundRow = Me.Column("A").Find(ID).row
  GetFirstName = Me.Cells(FoundRow,2).Value
  
End Function

Like Constractor with args

'Class Object Name : ClassWitnConstractor

'Class Implement Method
Sub Constractor(ByVal Args1 As Variant, ByVal Args2 As Variant)

  'Something initialize method
  
End sub

'Standard Modlue
dim obj as object
set obj = New ClassWitnConstractor
Call obj.Constractor(variant1, variant2)

Like Try-Catch

On Error Resume Next

    'try

    Quantity = WorkSheetFunction.Vlookup(Code,Range("A1:B400"),2,False)    

    'catch
    
    If Err Then

        Quantity = 0
    
    End If
   
'ReSet error arise, and sets err object false 
On Error GoTo 0

Put RegExp Execute

Dim re As Object
Set re = CreateObject("VBScript.RegExp")
re.Global = True

ReceivedOrderProducts Class

'Fields
'Constant
Const WAREHOUSE_DATA_PATH As String = "\\Server\Logistics\"

'Variant
Code As String
RequireQuantity As Long
ShouldPurchase As Boolean

Private StockQuantity As Long
Private IsStockout As Boolean

'Procedure
Sub SetCode(ByVal ArgsCode As String, ByVal ArgsQuantity As Long)
'Like Constractor Method

    Code = ArgsCode
    RequireQuantity = ArgsQuantity
    StockQuantity  = InventrySheet.GetQuantity(Code)
    IsStockout = Function IsShortage()

End Sub

Function ShouldPurchase() as Boolean

    If IsStockout = True Or StockQuantity = 0 then
        ShouldPurchase() = True
    Else
        ShouldPurchase() = False
    End If

End Sub

Private Function IsShortage() as Boolean
    Dim ProductInventry as Long
    InventryQuantity =  InventrySheet.GetQuantity(Code)

    If RequireQuantity > InventryQuantity then
        IsStockout = True
    Else
        IsStockout = False
    End if

End Sub

Replace HTML Tags RegExp

'正規表現オブジェクトはRegExp
Dim re As Object
Set re = CreateObject("VBScript.RegExp")
re.Global = True

'<>の最短一致パターン
re.Pattern = "<.*?>"

str = re.Replace(str, "")