Category Archives: 13048

Typing May Be Good For You



I have written previously about User Defined Types, where I showed what I feel is a good, legitimate use of UDTs, namely the saving of application settings at the start of my procedures, then re-instating them at the end. I have since extended that technique to make it more generic, as follows


Public Type ApplicationValues
    AppEnableEvents As Boolean
    AppScreenUpdating As Boolean
    AppDisplayAlerts As Boolean
    AppCalculation As XlCalculation
End Type

Public Function DoSomeStuff()
Dim myAppSettings As ApplicationValues

    Call AppSettings(State:=”Set”, _
                AppType:=mpAppSettings, _
                AppEvents:=True, _
                AppScreen:=False, _
                AppAlerts:=False, _
                AppCalc:=-1)
    On Error GoTo func_error

 … main code
 
 func_exit:
    Call AppSettings(State:=”Reset”, _
                    AppType:=mpAppSettings, _
                    AppEvents:=True, _
                    AppScreen:=False, _
                    AppAlerts:=False, _
                    AppCalc:=-1)

func_error:
    ‘your error handler
    Resume func_exit
End Function


Public Function AppSettings( _
   ByVal State As String, _
   ByRef AppType As ApplicationSettings, _
   ByVal AppEvents As Boolean, _
   ByVal AppScreen As Boolean, _
   ByVal AppAlerts As Boolean, _
   ByVal AppCalc As XlCalculation) As Boolean

   With AppType

       Select Case State

           Case “Set”
                If AppEvents Then

                   .AppEnableEvents = Application.EnableEvents
                   Application.EnableEvents = False
               End If
               If AppScreen Then

                   .AppScreenUpdating = Application.ScreenUpdating
                   Application.ScreenUpdating = False
               End If
               If AppAlerts Then

                   .AppDisplayAlerts = Application.DisplayAlerts
                   Application.DisplayAlerts = False
               End If
               If AppCalc <> appNotEnabled Then

                   .AppCalculation = Application.Calculation
                   Application.Calculation = AppCalc
               End If

           Case “Reset”
               If AppEvents Then Application.EnableEvents = .AppEnableEvents
               If AppScreen Then Application.ScreenUpdating = .AppScreenUpdating
               If AppAlerts Then Application.ScreenUpdating = .AppScreenUpdating
               If AppCalc <> appNotEnabled Then Application.Calculation = .AppCalculation
       End Select
   End With
End Function


That in itself was a nice improvement for my code, but recently I have started to use UDTs more frequently, in the way that they were probably meant for, creating data types.


I have found myself frequently creating datasets, so I created UDTs to cover this. For this sake of this discussion, I will cover one specific type in my application, User data.


I began by creating a UDT like so


PublicType AppUser
   LoginID As String
   Name As String
   DesignerId As Long
   Supervisor As Boolean
   UserReports As Boolean
   NewSalesTarget As Double
   RepeatsTarget As Double
   Deleted As Boolean
   UpdatedBy As String
   UpdatedOn As Date
End Type   


This was fine, it worked well, but in reality my user data was much larger, there were more permission types, and more targets. As such, I found that I was still working through each variable individually. I also use classes a lot, so my user class had every data item as a separate property. Although I could pass the UDT around my application quite well, I still had to refer to every item separately, define it everywhere.


I then had an idea. Why not create another UDT, for permissions say, and nest them. The UDT definitions would then look like this


Public Type AppPermissions
   Supervisor As Boolean
   UserReports As Boolean
End Type

Public Type AppUser
   LoginID As String
   Name As String
   DesignerId As Long
   Permissions As AppPermissions
   NewSalesTarget As Double
   RepeatsTarget As Double
   Deleted As Boolean
   UpdatedBy As String
   UpdatedOn As Date
End Type


Then I can use the familiar dot notation to refer to it, for instance


Dim myUser As AppUser

   MsgBox myUser.Permissions.Supervisor


In my class, it simplifies code. So instead of this,


Private mcSupervisor As Boolean
Private mcUserReports As Boolean

Public Property Get Supervisor() As Boolean
   Supervisor = mcSupervisor
End Property
Public Property Let Supervisor(ByVal Status As Boolean)
   mcSupervisor = Status
End Property

Public Property Get UserReports() As Boolean
   UserReports = mcUserReports
End Property
Public Property Let UserReports(ByVal Status As Boolean)
   mcUserReports = Status
End Property


I now have the UDTs shown above defined in a standard module, and in my class module


Private mcPermssions As AppPermissions

Public Property Get Permissions() As AppPermissions
   Permissions = mcPermissions
End Property
Public Property Let Permissions(ByRef Values As AppPermissions)
   mcPermissions = Values
End Property


[Note, that for UDTs, the argument must be passed ByRef]


In itself this is much cleaner and clearer in my view, but it gets better when you use it. To set the permissions, the code would be similar to


Dim myUser As User

    Set MyUser = New User
    With myUser

        .LoginID = “Bob”
        .UserName = “Bob Phillips”
        .Permissions.Supervisor = True
        .Permissions.UserReports = True
        ‘etc
    End With


or even


Dim myUser As User

    Set MyUser = New User
    With myUser

        .LoginID = “Bob”
        .UserName = “Bob Phillips”
        With .Permissions

             .Supervisor = True
             .UserReports = True
        End With

         ‘etc
    End With


As you can see, we are now breaking the user object down into properties which are objects in their own right, and using the dot notation to navigate down to the actual property, just as the Excel object does. This provides clarity and an element of object documentation within your code. Intellisense is retained, and is actually better because it is focussed.


In reality, I have nested even further as I have a Reports UDT which is nested within my permissions UDT, so the full definition is as follows, first the standard module UDT definitions, then the user class properties


‘UDT Definitions
Public Type AppReports
   TaskList As Boolean
   FollowUp As Boolean
   TotalQuotes As Boolean
   Dashboard As Boolean
   Commission As Boolean
End Type

Public Type AppPermissions
   Supervisor As Boolean
   Reports As AppReports
End Type

Public Type AppSalesTargets
   SalesGoal As Double
   BonusGoal As Double
   GPMGoal As Double
   ClosingRatio As Double
End Type

Public Type AppUser
   LoginID As String
   Name As String
   DesignerId As Long
   Permissions As AppPermissions
   Targets As AppSalesTargets
   Deleted As Boolean
   UpdatedBy As String
   UpdatedOn As Date
End Type


[Note that UDTs defined within another UDT have to be defined first, no forward referencing allowed]


‘Class Properties
Private mcUser As AppUser
Private mcPermissions As AppPermissions
Private mcTargets As AppSalesTargets

Public Property Get LoginID() As String
   LoginID = mcUser.LoginID
End Property
Public Property Let LoginID(ByVal Id As String)
   mcUser.LoginID = Id
   Call SetUserDetails(True)
End Property

Public Property Get UserDetails() As AppUser
   UserDetails = mcUser
End Property
Public Property Let UserDetails(ByRef Values As AppUser)
   mcUser = Values
End Property

Public Property Get Permissions() As AppPermissions
   Permissions = mcPermissions
End Property
Public Property Let Permissions(ByRef Values As AppPermissions)
   mcPermissions = Values
End Property

Public Property Get Targets() As AppSalesTargets
   Targets = mcTargets
End Property
Public Property Let Targets(ByRef Values As AppSalesTargets)
   mcTargets = Values
End Property


I always find the mass of Get/Let property definitions an impediment when reading class code, albeit a minor impediment, but an impediment all the same. Using this approach removes that code clutter whilst fully retaining the application functionality.


This approach fits perfectly with my beliefs in strong, structured coding style, making sure that the code is maintainable as well as functioning correctly.


The only real disadvantage that I can see is that the UDTs are not defined within the class itself, where they really belong. We could define Private UDTs in the class, but then they would not be available within the main application code. We could define Private UDTs in the class, and Public UDTs in a standard module, but this would be rather pointless. This is one of the main failings of UDTs in my view, one that has stopped me using them to any great degree before. With this approach, I envisage using them far more (I may be slow Ross, but I catch on eventually….).

What’s My Type?


I am a big advocate of using the Enum construct in VBA, I feel that it is a simple way of adding a set of values, and provides a grouping, abstracting advantage that is not given by simple variables. Plus they can add to intellisense. As such, I should also be a fan of another specialised construct, the Type statement, but it is quite the opposite, I naturally avoid it. This is not because I think the Type statement is a bad concept, in fact I think the basic idea is great, but more because of its implementation; whenever I try to use it in anger, it breaks on me. As such, I tend to avoid it and create classes.


Earlier this week, it occurred to me that there was a case in which I could implement a Type, an elegant solution, with no chance of it breaking on me.


We all know that when your masterpiece sets application variables, such as calculation mode, in its processing, they should be reset at the end to whatever state the user originally had them. The normal way to do this is to save them in a bunch of variables at the start of the process, and restore them at the end.


My solution is very similar, but the benefits of Type add an elegance that I don’t feel the variables give it.


Here is that solution.


 


Public Type ApplicationValues


    ScreenUpdating As Boolean


    Calculation As XlCalculation


End Type


 


Private AppValues As ApplicationValues


 


Public Function ObfuscateData()


 


    With Application


   


        AppValues.ScreenUpdating = .ScreenUpdating


        AppValues.Calculation = .Calculation


       


        .ScreenUpdating = False


        .Calculation = xlCalculationManual


    End With


   


    On Error GoTo func_error


 


… main code


 


func_exit:


    With Application


       


        .ScreenUpdating = AppValues.ScreenUpdating


        .Calculation = AppValues.Calculation


    End With


 


func_error:


    ‘your error handler


    Resume func_exit


End Function


 


I think this is a neat little approach. Furthermore, it is simple and extendible, what more can we ask?


This may be obvious to you, it may be the sort of thing that you do all of the time, but it made me smile after I had implemented it. So much so that it is now a code snippet in my MZ-Tools, implementable at the drop (click) of a button.