Auto intantiating objects - how bad can they be?
2

Question

Auto intantiating objects - how bad can they be?

1DMF (Programmer)

(OP)

6 May 16 14:50

MajP posted a link to advise someone to properly format their code and declare vars.

I was perusing the link, http://www.cpearson.com/excel/declaringvariables.a... and found this..

Quote:
Don't Use Auto-Instancing Object Variables

IE don't declare a var as

CODE

Dim myVar as New clsMyClass

How true is this? or should I say how bad is using this syntax?

I especially use this when I am attaching a controller type class to a form or a global helper object such as audit trail....

CODE

Option Compare Database
Option Explicit
Private oAudit As New clsAuditTrail

Private Sub Form_Current()
    Call oAudit.SetFieldValues(Me)
End Sub

Surely I don't need to change that to...

CODE

Option Compare Database
Option Explicit
Private oAudit As clsAuditTrail

Private Sub Form_Open(Cancel As Integer)
    Set oAudit = New clsAuditTrail
End Sub

Private Sub Form_Current()
    Call oAudit.SetFieldValues(Me)
End Sub

Do I?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

strongm (MIS) · Answer 1 · 2016-05-07 13:28:07

Chip Pearson's article explains the two main reasons you might want to avoid it, namely that you have limited control when an object is created and it means that you cannot test whether an object is Nothing. There is also a performance issue, but on a modern PC this is so tiny that it probably isn't worth worrying about.

As long as you know the drawbacks and are happy to live with them, then - in my opinion - there's nothing wrong with using auto-instancing objects.

1DMF (Programmer) · Answer 2 · 2016-05-08 08:10:46

Thanks Mike,

I think in my use case shown above it's fine, but I also appreciate where it might be an issue, I never realised that checking it for Nothing would instantiate the object if the var is Nothing so it is never Nothing.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

jedraw (TechnicalUser) · Answer 3 · 2016-05-09 13:15:22

1DMF,

I'd be interested in looking at your Audit Trail and related class (modules) and usage if you'd care to share.

1DMF (Programmer) · Answer 4 · 2016-05-09 14:08:53

Sure, I simply refactored some code developed for us by the guy who gave me my 3 day access training back in 2004!

clsAuditTrail

CODE

Option Explicit
Private oFormFields As Object

Public Sub SetFieldValues(ByVal fForm As Form)

On Error GoTo Error_SetFieldValues
    
    Dim ctl As Control
    
    Set oFormFields = CreateObject("Scripting.dictionary")

    For Each ctl In fForm.Controls
    
        If ctl.Tag <> "SKIP" Then
        
            Select Case TypeName(ctl)
                
                Case Is = "Checkbox", "TextBox", "Combobox", "Memo"
                
                    'check if control source and not derived one (ie =Forms....)
                    If left(Nz(ctl.ControlSource, "="), 1) <> "=" Then
                        oFormFields.Add ctl.Name, Nz(ctl.value, "")
                    End If
                    
            End Select
            
        End If
        
    Next

    Exit Sub

Error_SetFieldValues:

    MsgBox "Error in clsAuditTrail.SetFieldValues : " & err.Source & " - " & err.Description
    Resume Next

End Sub

Public Sub SaveChanges(ByVal fForm As Form, ByVal sUser As String, ByVal lRecID As Long, ByVal sTable As String)

On Error GoTo Error_SaveChanges

    Dim vName As Variant

    For Each vName In oFormFields.keys
    
        If Nz(fForm.Controls(vName).value, "") <> oFormFields.Item(vName) Then
            Call LogIt(lRecID, sTable, oFormFields.Item(vName), Nz(fForm.Controls(vName).value, ""), "Modified field : " & vName, sUser)
        End If

    Next

    Exit Sub

Error_SaveChanges:

    MsgBox "Error in clsAuditTrail.LogChanges : " & err.Source & " - " & err.Description
    Resume Next
    
End Sub

Public Sub LogIt(ByVal lRecID As Long, ByVal sTable As String, ByVal sOld As String, ByVal sNew As String, ByVal sDesc As String, ByVal sUser As String)

On Error GoTo Error_LogIt

    Dim sUID As String
    Dim sSQL As String
    Dim SDate As Date
    Dim sTime As Date
    
    sOld = Replace(sOld, "'", "''")
    sNew = Replace(sNew, "'", "''")
    sDesc = Replace(sDesc, "'", "''")
    
    SDate = left(Now(), 10)
    sTime = Format(Time, "HH:MM:SS")
    
    sSQL = "('" & lRecID & "','" & sTable & "','" & sOld & "','" & sNew & "','" & sDesc & "','" & sUser & "','" & SDate & "','" & sTime & "')"
    
    CurrentDb.Execute "INSERT INTO [Audit Log] ([RecID],[Table],[Old],[New],[Desc],[User],[Date],[Time]) VALUES " & sSQL & "", dbSeeChanges

    Exit Sub
    
Error_LogIt:

    MsgBox "Error in clsAuditTrail.LogIt : " & err.Source & " - " & err.Description
    Resume Next

End Sub

The LogIt sub is a helper method that can also be used for manually creating audit log records, where as the other two methods are used for forms, so you can pass in a form and it will log any changes made by the user.

Then you simply need two events and a global audit trail object on your form...

CODE

Option Compare Database
Option Explicit
Private oAudit As New clsAuditTrail

Private Sub Form_AfterUpdate()
    Call oAudit.SaveChanges(Me, "User Name", Me.RecID, "DB_Table_Name")
    Call oAudit.SetFieldValues(Me)
End Sub

Private Sub Form_Current()
    Call oAudit.SetFieldValues(Me)
End Sub

As you see it's nothing fancy, but it does the job and keeps track of when users update data on forms very easily.

A couple of things to note, it only tracks "Checkbox", "TextBox", "Combobox", "Memo" controls and they must not be calculated / derived, you can also use the 'Tag' property on a control and set it to 'SKIP', if you want to exclude any controls from the audit trail.

The AuditLog table is very simple...

Quote:

Column      DataType
ID	    int	(Identity Insert)
RecID	    int	
[Table]	    nvarchar(50)	
Old	    nvarchar(MAX)	
New	    nvarchar(MAX)	
[Desc]	    nvarchar(500)	
[User]	    nvarchar(50)	
Date	    datetime	
Time	    datetime

Hope you find it useful.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

MajP (TechnicalUser) · Answer 5 · 2016-05-09 17:05:14

Your goal should always be to limit the scope and lifetime of all variables. Use the most restrictive scope (procedure level, private, public) and instantiate just when you need it. Think of it like working with some hazardous virus contained in a safe. Check it out as late as possible and dispose of it as early as possible. Only get out the mininum you need.

If you declare and instantiate the variable at the same time that me be OK if you will need it immediately from the module or class that uses it. If not then instantiate right when you need it. leaving things open and hanging around may work, but it is a bad habit to get into if you are not aware of what is going on.

strongm (MIS) · Answer 6 · 2016-05-09 19:32:39

RE: Auto intantiating objects - how bad can they be?

strongm (MIS) 9 May 16 19:32

But that's a somewhat different argument.

1DMF (Programmer) · Answer 7 · 2016-05-12 14:06:24

Appreciated MajP, the above example requires instant use and continual use, but I appreciate keeping scope and creation to bare minimum.

I'm surprised you didn't' pull me up on my class above interacting with the user via msg boxes, as technically it shouldn't, but hey ho, in perfect world and all that!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

Come Join Us!

Posting Guidelines

Jobs

Auto intantiating objects - how bad can they be?
2