Typing May Be Good For You

Bob Phillips — Wed, 17 Feb 2010 13:03:00 GMT

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?

Bob Phillips — Sat, 12 Sep 2009 22:12:00 GMT

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.

Excel Do, Dynamic Does : User Defined Types

Typing May Be Good For You

What's My Type?