Worksheet Forms using WPF style architecture

Introduction

I want to preface my first ever article here with an over-simplified plea: don't use this!  There are many alternatives (frameworks, architectures, software, etc.) that would be significantly more appropriate for this style of UI and UX construction.  This should only be used in small use cases or if Excel is truly your only option.

With that out of the way, let's dive right in...

While working on the UI and UX of a project, I ran into 3 main problems which this framework was made to solve:

1. Above all else, it must enable realistic maintainability of the code.
2. It must scale well, in both a code and graphical sense.
3. It must be simple and easy to use.

In the end, I feel that this framework is a great starting point to solve these problems within the scope and limitations of the environment.  That said, my hope here is that someone else adapts this to their needs and posts any improvements or alternative constructs for everyone's benefit.

Background

I really enjoy WPF and how easy it makes developing a good looking UI and UX.  It's these benefits that I ended up basing this framework on.

I needed to create a replacement scheduler for a client - mainly to save them from the atrocious version they were using at the time.  Something to manage employees' (just a handful per workbook) work shifts along with some other stats.  The real kicker was that, due to their security, I was limited to MS Office.  In a nut shell, I was stuck working with Excel and...VBA.

I ran into a growing problem rather early on in development on the scheduler: the code required to maintain the project was growing at what seemed an exponential rate.  I couldn't help but think about how much easier this whole thing would be if I could use WPF.  As I've already explained, this was not possible.  So I set out to, in essence, emulate the WPF benefits as best I could - rewriting most of code along the way.

I developed the workbook to a point where we could start testing it in the field.  That's when the project flat-lined - around six months ago as of this writing.  Some system updates (a proprietary Windows shell) had been pushed out which included tightened security.  It was the update to security that did the project in - macro enabled workbooks weren't allowed to run anymore.

Since my project's untimely demise, I've planned to post my work here in the hopes that it might flourish somewhere else.

Using the code

Overview

Please explore the provided Excel file and its code as I'm only touching on some highlights here.

Buttons

[Hit it with wrench] - re-enable drawing to the screen.  This was intended as an "in-dev" feature only since drawing is disabled when everything starts running and I like to stop it when debugging.

[<] and [>] - cycle through the weeks, alternatively you can just type over the current number and hit either the tab or enter key.  [<] = previous week; [>] = next week;

[Settings] - opens a form for general settings and employee maintenance.

Classes

There are two main classes - CellView and TemplateView.  The second class, as  the name suggests, is used as a template class and, as such, doesn't actually do anything on its own.  I'll review it's use in more detail later in the article.

The first class, CellView, simplifies the interaction with a single cell.  It's a very lightweight object consisting of only a single Range object to point to a specific cell and some boiler plate property accessors.  These accessors are where it makes things easier as I'll show later on.

Just for clarification: references to parent/child relationships between objects (within the scope of this article) is completely in a hierarchical sense, not a polymorphic one.

CellView

Private pRg As Range

' Gets/Sets cell this object points to
Public Property Get Pos() As Range
    Set Pos = pRg
End Property
Public Property Let Pos(Value As Range)
    Set pRg = Value
End Property

Private Function IsInit() As Boolean
    IsInit = (Not pRg Is Nothing)
End Function

...

' Value
Public Property Get Value() As String
    If IsInit Then
        Value = Pos.Value
    End If
End Property
Public Property Let Value(val As String)
    If IsInit Then
        Pos.Value = val
    End If
End Property

' Text
Public Property Get Text() As String
    If IsInit Then
        Text = Pos.Text
    End If
End Property

' Address
Public Property Get Address() As String
    If IsInit Then
        Address = Pos.Address
    End If
End Property

Example - EmpTtlView

The following example snippet should better illustrate how this class is used.  Note: EmpTtlView was copied from TemplateView, as explained earlier.

'
' EmpTtlView - Handles I/O and formatting for a single employee's daily totals.
'

' <Other Declarations>

...

' Properties
Public TimeWk As CellView
Public GoalWk As CellView
Public SalesWk As CellView
Public OverUnderWk As CellView

Public TimeYTD As CellView
Public GoalYTD As CellView
Public SalesYTD As CellView
Public OverUnderYTD As CellView

Public TimeLbl As CellView
Public GoalLbl As CellView
Public SalesLbl As CellView
Public OverUnderLbl As CellView

...

' Initialization...

Public Function Init(parentView As EmpView)
    If Not pInit Then
        Set pParentView = parentView
        Set pSett = Singletons.GetSettings
        
        pViewWidth = 1
        pViewHeight = 1
        
        Set TimeWk = New CellView
        Set GoalWk = New CellView
        ...
'...end Function Init

...

' Function SetPosition...

        Set pRg = rgPosition.Cells(1, 1)
        ' columns
        wk = 1
        ytd = 2
        lbl = 3
        'rows
        hrs = 1
        gls = 2
        sls = 3
        ou = 4
        
        TimeWk.Pos = pRg.Cells(hrs, wk)
        GoalWk.Pos = pRg.Cells(gls, wk)
        SalesWk.Pos = pRg.Cells(sls, wk)
        OverUnderWk.Pos = pRg.Cells(ou, wk)
        
        TimeYTD.Pos = pRg.Cells(hrs, ytd)
        GoalYTD.Pos = pRg.Cells(gls, ytd)
        SalesYTD.Pos = pRg.Cells(sls, ytd)
        OverUnderYTD.Pos = pRg.Cells(ou, ytd)
        
        TimeLbl.Pos = pRg.Cells(hrs, lbl)
        GoalLbl.Pos = pRg.Cells(gls, lbl)
        SalesLbl.Pos = pRg.Cells(sls, lbl)
        OverUnderLbl.Pos = pRg.Cells(ou, lbl)

' ...end Function SetPosition

...

'----[ Utils ]----------------------------------------------------
Private Function AppendFormula(rgCell As Range, firstStr As String, appendStr As String)
    If Not rgCell Is Nothing Then
        If rgCell.Formula <> "" Then
            rgCell.Formula = Mid(rgCell.Formula, 1, Len(rgCell.Formula) - 1) & ", " & appendStr & ")"
        Else
            rgCell.Formula = firstStr
        End If
    End If
End Function

...

' Some simple automated formula creation...
Public Function CalcSales(rgSales As String)
    If IsInit Then
        AppendFormula SalesWk.Pos, "=SUM(" & rgSales & ")", rgSales
    End If
End Function

Public Function CalcOverUnder()
    If IsInit Then
        OverUnderWk.Formula = "=" & GoalWk.Address & "-" & SalesWk.Address
    End If
End Function

...

TemplateView

The TemplateView objects needed some manual finagling to get the hierarchical placement and scaling to work as simply as possible.  I'll just cover this aspect of TemplateView, since it's basically the entire premise of the template/object.

Each view knows about and has a reference to its parent view and its child(ren) views.  I did this in a way that was as intuitive as I could, however there's certainly room for improvement.  Key

' Replace 'Variant' with actual parent class
Private pParentView As Variant

' Add child(ren) references here...

Private pSett As Settings

' position relative to parent
Private pRg As Range

Private pViewWidth As Integer
Private pViewHeight As Integer

Private pInit As Boolean

...

' TODO: replace 'Variant' with actual parent class
Public Function Init(parentView As Variant)
    If Not pInit Then
        Set pParentView = parentView
        Set pSett = Singletons.GetSettings
        
        pViewWidth = 1
        pViewHeight = 1
    End If
    pInit = True
End Function

'----[ Position ]---------------------------------------------
' SetPosition usage - used from 'pParentView'; usage snippet:
'
'        ' array of "day" views (children)
'        Private pDayViews(6) As DayView
'
'        ' simple day counter
'        Dim d As Integer
'
'        ' Set position of template view to the first cell of the position arg
'        Set pRg = rgPosition.Cells(1, 1)
'
'        ' <init/populate pDayViews & other logic>
'
'        ' for each day of the week, graphically append each day's view so none overlap
'        For d = 0 To 6
'            '            <first row>, <next empty/avail column>
'            '                         <current pos> + <this view's width> + <DayView width * day count>
'            pDayViews(d).SetPosition pRg.Cells(1, 1 + pViewWidth + pDayViews(d).OffsetWidth(d))
'        Next d
'
' rgPosition is the upper-left most cell of this view
Public Function SetPosition(rgPosition As Range)
    If pInit And (Not rgPosition Is Nothing) Then
        ' Range.Cells(1) lets us not care about merged cells/ranges
        Set pRg = rgPosition.Cells(1, 1)
    End If
End Function

' Returns this view's width multiplied by offsetIndex.
Public Function OffsetWidth(offsetIndex As Integer) As Integer
    If IsInit Then
        OffsetWidth = pViewWidth * offsetIndex
    End If
End Function

' Returns this view's height multiplied by offsetIndex.
Public Function OffsetHeight(offsetIndex As Integer) As Integer
    If IsInit Then
        OffsetHeight = pViewHeight * offsetIndex
    End If
End Function

This was an incredibly fun project and I may end up playing with/tweaking it later.  As I've stated above, I hope someone can make use of this, even in part.  I would love to hear from anyone who found this useful or about any changes you've made to your own copy.

Points of Interest

In hindsight, and in keeping closer to the WPF class framework, it would have been better to decouple the view objects from the business logic  (model objects).  So the views would only have the UI/UX aspects to worry about.  The model objects would then do all of the thinking, letting the views worry about making things look pretty.