VBA/Excel/Access/Word/Data Type/Type

An Example of Using a Type Structure

   <source lang="vb">

Type CompanyInfo

   SetUpID As Long
   CompanyName As String * 50
   Address As String * 255
   City As String * 50
   StateProvince As String * 20
   PostalCode As String * 20
   Country As String * 50
   PhoneNumber As String * 30
   FaxNumber As String * 30
   DefaultPaymentTerms As String * 255
   DefaultInvoiceDescription As String

End Type Public typCompanyInfo As CompanyInfo Sub GetCompanyInfo()

   Dim strSubName As String
   Dim rst As ADODB.Recordset
   Set rst = New ADODB.Recordset
   rst.ActiveConnection = CurrentProject.Connection
   rst.Open "Select * from CompanyInfo", Options:=adCmdText
   With typCompanyInfo
       .SetUpID = rst!SetUpID
       .rupanyName = rst!CompanyName
       .Address = rst!Address
       .City = rst!City
       .StateProvince = rst!StateOrProvince
       .PostalCode = rst!PostalCode
       .Country = rst!Country
       .PhoneNumber = rst!PhoneNumber
       .FaxNumber = rst!PhoneNumber
   End With
   rst.Close
   Set rst = Nothing

End Sub

</source>
   
  

Create a new data type

   <source lang="vb">

Option Explicit Type article

 artname As String
 price As Currency

End Type Sub macro()

 Dim a As article, b As article
 a.artname = "Screw"
 a.price = 3.5
 b = a
 Debug.Print b.price

End Sub

</source>
   
  

Save type info to form

   <source lang="vb">

Type CompanyInfo

   SetUpID As Long
   CompanyName As String * 50
   Address As String * 255
   City As String * 50
   StateProvince As String * 20
   PostalCode As String * 20
   Country As String * 50
   PhoneNumber As String * 30
   FaxNumber As String * 30
   DefaultPaymentTerms As String * 255
   DefaultInvoiceDescription As String

End Type Public typCompanyInfo As CompanyInfo Sub PopulateControls()

   txtCompanyName.Value = Trim(typCompanyInfo.rupanyName)
   txtAddress.Value = Trim(typCompanyInfo.Address)
   txtCityStateZip.Value = Trim(typCompanyInfo.City) & ", " & _
       Trim(typCompanyInfo.StateProvince) & _
       "  " & Format(Trim(typCompanyInfo.PostalCode), "!&&&&&-&&&&")
   txtPhoneFax.Value = "PHONE: " & _
       Format(Trim(typCompanyInfo.PhoneNumber), "(&&&)&&&-&&&&") & _
       "       FAX: " & _
       Format(Trim(typCompanyInfo.FaxNumber), "(&&&)&&&-&&&&")

End Sub

</source>
   
  

User-Defined Data Types

   <source lang="vb">

Type Ledger

    Number As Integer
    Description As String
    Debit As Currency
    Credit As Currency
    Balance As Currency

End Type Sub typeDemo()

   Dim ledgerRow As Ledger
   ledgerRow.Number = 1001
   ledgerRow.Description = "Rent"
   ledgerRow.Debit = 750
   ledgerRow.Balance = ledgerRow.Balance - ledgerRow.Debit

End Sub

</source>