Category Archives: 11541

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….).

Too many Modules Spoil the VBA Project?

So many times you see someone asking about whether they should split their VBA code into many modules, if so, how should they structure it?

We probably know that there is a limit to the amount of code that a VBA code module can handle. The code module is essentially an in-file text file (if exported it can be viewed with a simple text eitor), and can accomodate a size of approx 64k bytes. As such, the answer to that previous question should always be an emphatic yes. But it also should be yes from a code maintainability persepctive, even if it never approaches the 64k limit. The code should be organised into functionally discrete modules, for instance a module that contains all of the file handling procedures, another that contains all of the graphing procedures and so on.

For my projects, I tend to have a global module, which contains all of my global variables (although I try to keep these to a minimum), and all of my application constants, such as messages and enumerated lists. This way, it is simple to track the source of these variables, rather than scattering them amongst many modules.

I would normally have an application class, specific to the particular application, which would hold any procedures common to that application, such as a common validation procedure, and application variables, such as the AppId, AppName, AppVersion and so on. It could be argued, and I have gone this route in some projects myself, that many of those global variables are application specific and should be in the application class. I think I subscribe to this in theory, but don’t always practise it.

Of course, I have a common error handling procedure, a utilities procedure (which would hold my generic procedures such as SheetExists), and a history module, which is pure comments.

If I need to trap application events, I would have such a class. I tend to hand off the actual processing of these events to a helper module, the class module simply traps the event, decides whether it applies to this application in these circumstances, and then calls the procedure in another module. Theoretically, I am not happy with this from a design perspective, but I do find it makes the code a lot more manageable, and convince myself it is good from a maintainability perspective.

I also always have a scratch code module, where I have procedures to easily setup data, reset things, test specific aspects of the application, and so on.

Finally, I apply a strict naming convention, and add two underscores to a few modules to force them to the top of the list, and a single underscore to some others to forec them into the next batch. I find this categorisation also helps with maintenance.

Here is an example of one of my projects.



As you can see, it is a good size with over 8,000 lines of code, the 363 procedures are nicely scattered over the modules, no module having more than 30 procedures except AppClass (many of which would be property Let/Gets). Apart from AppClass, no module has more than 700 lines of code, and each procedure is circa 30 lines line. Of these code lines, nearly 1,500 are comment lines (circa 15%). I think this is a high comment rate for me, not typical, as I do not over-comment my code.  In fact, without the history module, the comments reduce to 12%. 10-12% I would think is my typical comments percentage.