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:
- Above all else, it must enable realistic maintainability of the code.
- It must scale well, in both a code and graphical sense.
- 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
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
...
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
Public Property Get Text() As String
If IsInit Then
Text = Pos.Text
End If
End Property
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.
...
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
...
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
...
...
Set pRg = rgPosition.Cells(1, 1)
wk = 1
ytd = 2
lbl = 3
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)
...
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
...
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
Private pParentView As Variant
Private pSett As Settings
Private pRg As Range
Private pViewWidth As Integer
Private pViewHeight As Integer
Private pInit As Boolean
...
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
Public Function SetPosition(rgPosition As Range)
If pInit And (Not rgPosition Is Nothing) Then
Set pRg = rgPosition.Cells(1, 1)
End If
End Function
Public Function OffsetWidth(offsetIndex As Integer) As Integer
If IsInit Then
OffsetWidth = pViewWidth * offsetIndex
End If
End Function
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.
