Shrapnel

I've begun to migrate with some of my clients to the 3 year old Excel.  I'm well past the whole Ribbon controversy - yet I still get a sense that the new ship is still a bit leaky.  Office has thrown a few curve balls at me, so here are some cheap and effective tidbits of advice I can pass along:

Don't... 

...create pivot tables in Excel 2007 and then save the file as a 2003 compatible .xls - it all goes to hell.


...try to draw a flow chart in a Word 2007 document using AutoShapes.  Visio is better if you have it.


...run untested Excel VBA code that references the MS Word object model without setting up error handler's first.


...insert a PDF object into a document that may end up being printed to PDF.


...dispair.  No matter how low the economy goes, businesses will always have spreadsheets.


Cheap and Effective

I've been working on a B2B application - a variation on the standard "request for quote" problem.  In this case, a request is assembled from data on the spreadsheet, dropped into an email, and sent to a slew of individuals somewhere who may not even have Excel (poor sods.) They hit reply, fill in some sections of the copied message, and send.  Another macro connects to Outlook, scans a particular in-box, and pulls the details from any messages found as they come back.

This is, like my advice above, cheap and effective.  It's a way to do email forms with no workgroup software or exchange programming.  It doesn't involve sending or sharing files, and is not limited to the corporate LAN.  Data can even be collected electronically from people who aren't sitting at their desk, working, like I am always.

Hey, isn't this what websites are for?  Um, yeah, but this approach bypasses the need for website programming - no offence to web programmers (since I used to be one,) but power macro artistes deal in turn-around times of hours, not weeks, and the data needs to get back to Excel in the end anyway, right?  
 
Excel talks quite nicely to outlook.  Here's some code that sends an email message - it's really dead easy as long as you set up the appropriate references.  Perhaps the next post can explore the response capture side of things.

Public Function SendAMessage(strSubject As String, _
         strBody As String, _
         strAddress As String, _
         Optional strAttachment As String = "") _
                As Boolean

 'strAttachment, if used, will be the full 
 'file path of a file
 'requires a reference to the Microsoft 
 'Outlook 12.0 Object Library
 'see Tools Menu - References
  
  Dim email As Outlook.MailItem
  
  On Error GoTo Fail
  SendAMessage = False

  Set email = Outlook.CreateItem(olMailItem)
     
 'make sure we've been passed sufficient info
  If Len(strAddress) = 0 Or Len(strSubject) = 0 _
  Or Len(strBody) = 0 Then
  
    'trigger an error
     Err.Raise 999, , "Missing Information"
     
  Else

    'assemble the message
     With email
        .Subject = strSubject
        .To = strAddress
        .Body = strBody
        
        'attachment if required
         If Len(strAttachment) > 0 Then
           .Attachments.Add strAttachment
         End If
        
        .Send
        
     End With
     
    'it worked
     SendAMessage = True
  
  End If

Cleanup:
  On Error Resume Next
  Set email = Nothing
  Exit Function

Fail:
 'it didn't work
  gGlobalError = Err.Description
  Debug.Print "SendAMessage Failed. " &_
               Err.Description
  Resume Cleanup
  
End Function

Back to Business

This post is so long overdue I'm almost ashamed. If I'd had the entire summer off, that would be one excuse, but the real reason for the delay was that I've been too busy writing code to write anything else.  Okay, I will admit to enjoying a reduced workload in July - 20 hours instead of my usual target of 120!  In any other month this would be a catastrophe, but I try to keep July work-free, as a personal reward for the year's efforts.  That's one of the principal advantages of being self-employed, after all.

As mentioned in a previous post, I landed a return engagement with the company for whom I created an Excel front end to an Access database.  Another department within the company saw how effective the first application was, and hired me to build something similar, to handle their Purchase Order processing workflow.  This has kept me busy for all of August.

The business logic was a lot more complex for this project, but I was able to re-use quite a bit of the architecture from the previous application: User Account Management, database connectivity code, list filtering and sorting... There's nothing like the efficiency and confidence you get from writing code around a proven framework: instead of messing with the fiddly bits, I was able to focus on adding business functionality and making the software friendly and usable.  Client's don't generally get excited to learn that the TCP/IP bottleneck has been optimized, but they are always thrilled to hear about the one-click report engine you've designed!  This project is currently in testing, and so far, the reception has been 100% positive.  I aims to please.

As a challenge to myself, I decided to do this development entirely in Excel 2007.  I usually do my work in 2003, since I find that version of Excel more responsive and pleasant to use.  If the client will be running 2007, the "xls" file from Excel 03 usually just works.  But this time I opted to skip 2003 altogether, to see what would happen.  To my surprise, I found I was able to make the transition with very little pain and suffering.  

It took me a few days to really get the hang of using the Ribbon - I still find myself hunting around for some Excel features, but the learning curve was not as steep as I had predicted.  The benefit is that the file is running "native" in Excel 2007 - no "compatibility mode."  I'm not sure if this is a real advantage (other than looking cleaner on the Excel title bar), but at least I'll know that, if some strange bug or behaviour pops up, it's not an artifact of the file conversion process.  More importantly, I am now more efficient using and interacting with Excel 2007, and feel comfortable porting my personal spreadsheets and Client work to this platform.

Looking forward to the Fall, it appears I will continue to be very busy for the foreseeable future.  Since starting out as an independent consultant 4 years ago, I have been slowly building my roster of clients.  Many projects are one-off, where I go in, figure out what needs doing, write the app, and that's more-or-less the end of the engagement.  But several of my clients are power Excel users who are constantly looking for new short-cuts, utilities, or techniques to apply to their daily business activities.  

As I add more and more of these types of clients to my roster, I'm given an increasing load of "casual" hours - short fixes, mini projects, and extension of existing work.  This ad-hoc stuff is taking up enough of my time that it effectively fills in the gaps between bigger projects.  It has taken a few years, but now I have the business humming along perfectly - working enough to pay the bills, but not so much that I'm overwhelmed.  If that starts happening, I'll just start raising my hourly rate.  Everything is going according to plan.

Applying the Application Object

VBA coders who want do do more than automate the basic Select, Copy and Paste functions of Excel eventually discover the object model, and the world of possibilities it brings.  One object that is sometimes overlooked is Application.  Here are a few examples of how we can use the Excel Application object in our VBA code.

One of my personal favourites is Application.StatusBar.  The status bar is the strip of real estate at the bottom of the Excel window where you see system messages and so forth.  Programmers can write to this area, which can be an intuitive and logical way to keep your users informed about what your macro is doing.  As explained in a previous post on Speed Optimization, if your process is long and involves a loop, periodic updates to the status bar can tell the user how far along the process has advanced.  I usually use a percent complete message, as exemplified in the following code:


For idx = 1 to intNumRows
   {... code does something here....}
   Application.StatusBar = "Running... " & _
   CInt((idx/intNumRows)*100) & "% complete"
Next


Given this feedback, the user can predict how long the macro will take to run, which actually makes it appear to run faster.  More importantly, users can see that SOMETHING is happening.  Its also nice to finish up with a parting word...


Application.Statusbar = "Calculation Complete."


Another really important use of the Application object is the WorkSheetFunction method.  This allows you to execute any function that would normally only work when placed directly in a worksheet cell.  For example, say you had a VBA process that needs to take an average of a bunch of cells.  You could loop through them and add each cell's value to a running total, then divide by the number of items, or you could use the built-in AVERAGE function, as in:


varResult = Application.WorksheetFunction.Average(rngRange)

I ofetn use this approach when trying to find the position of a value in a list, because the MATCH worksheet function has no equivalent in VBA.


intPos = Application.WorksheetFunction.Match(varVal, rngRange, 0)

What if you have written a custom function and you need to know what cell that function was entered into? This is important, for example, if the function must refer to other values on the same row or in the same column.  For this, we use Application.Caller, which returns a RANGE reference to the cell where the function was "called" from.


Function MyFunction(N as integer) as Integer
 'use whatever is in the Nth column of the row in which
 'this function is placed as an argument for another function
  intRow = Application.Caller.Row
  MyFunction = MyOtherFunction(Cells(intRow, N))
End function

Another very powerful method is Application.Run.  This allows you to specify the name of another macro as a text string, and execute it, along with up to 30 arguments.  Why do we need this?  Let's say we are working on a model that encodes a large set of business rules.  We might have dozens of macros that do specific things depending on what may be contained in the data.  Using the Run method, we can assemble the name of our macro in code based on changing data conditions, and then run the code.  I have used this in situations where macro names reside in a database, allowing me to change the behaviour of the application by modifying data.  This facility comes close to enabling Self-Modifying Code, which any old-school programmer will tell you is the holy grail of power in any language that allows it.

There are many many more powerful features available to the VBA programmer by way of the Application object.  Just open your Code Editor and type "Application.", and observe the miriad of methods and properties that the IDE exposes for you.  With a bit of experimentation I guarantee you will discover a way to do something you never thought possible in VBA.  Random code exploration is the gateway to advancing the art.

Excel in Front, part 2

Project Update: I'm happy to say that so far the Excel Front End project has been as smooth as glass.  No only were the client's expectations exceeded in every regard, but my personal experience writing the application has been immensely satisfying.

If you recall from last post: I was hired to design and build a work-order tracking system for a manufacturing facility, using Excel as the front-end to an Access Database.  When I set out on this project, the main challenge was that the client's shared workbook approach was causing a lot of trouble, and undermining their confidence in the data.  They asked for my help, as an Excel consultant, to sort out the file sharing issues and build in some business logic and data validation rules.  

I proposed two solutions: keep the shared workbook but apply sharing permissions effectively and write the required macros to ensure data integrity, or the more involved, expensive option: move the data to Access and create an intelligent front-end in Excel so users can interact with it.  They opted for the Access option, understanding the scalability advantages, and the cost savings they will achieve when the time comes to build out some of the other data management applications they need.

I developed a vision for how the system should work: no shared workbooks, no linked files, no open connections, and especially: no Access, as in, no developing in Access (because, well, it's crap.  Sorry, but I hate it.)  I have linked Excel to Access before, so I knew how to do it, but I didn't know if this architecture would perform acceptably with this particular set of business logic and data until I actually built it.   

Typical client-server apps use a persistent database connection.  This is fine when using an Enterprise RDBMS such as SQL Server, but in a small-scale deployment using an .mdb file, I just knew that trying to simultaneously connect 20 or so users to a shared Access db would be a recipe for failure.  So I designed the system to interact with the database in instant-batch mode: need some data? Connect to the db, fetch the data into memory, disconnect from the file, then display the formatted results.  This way, no single user has an open connection to the access file for more than a fraction of a second at a time, virtually eliminating any chance of network traffic bottlenecks, record-locking stalemates, and (hopefully) database corruption.

The following are some key design points I employed:

• Created a de-normalized view of the data, by way of an Access Query, which joins the related data tables to give a fully-qualified view of all Work Orders in the system.  This Master Query becomes the main Result Set that the Excel side queries from.
• Rather than code separate reports for all the different needs of the client, coded a Report Engine that can be configured as needed, enabling all current and future reports to be designed without programmer intervention.
• Matched the field names in the Master Query to the column headings used in Excel.  This allows the user to build their own filters and searches by selecting from known field-names.  
• All user-definable settings are stored in the database, keyed to the user's login.  This way, when the Excel front end file is updated, it can reload all the user's settings and they can continue to work without any setup time.
• Used generic database SELECT and EXECUTE routines, so all db-related code passes through a single gateway.  If we later to decide to change our Database server, we won't have to search through hundreds of routines to change all the affected code.
• Captured cell-edits using the Worksheet_Change event.  Updates are validated, and if allowed, instantly written to the database.  Cell formatting and locking are applied as the data is displayed, so the users know (and are only allowed to edit) the editable parts of each record.

Amazingly, this project came together without a hitch.  The incredible flexibility and programmability of Excel enabled me to build every requirement the client asked for, and since I didn't get bogged down searching for bug workarounds and advice, I had time left in the budget to add some really sweet features to the application.  Next Tuesday we deploy the app to the users, and they are really looking forward to the improved efficiency and productivity advantages they will be getting.  And the client has asked for help with a second project for another division in the company, which is obviously very welcome: follow-on work is a sure sign you're doing a good job.

Unfortunately I can't share many more details about this application because I have signed a non-disclosure agreement, which binds me to keep any client-specific or business-specific information secret.  But here is some code which I have used in this and other projects: I keep this in a code module called "DBConnectivity". It is offered without warranty, but it might come in handy for anyone attempting a project that connects Excel to an Access database file.  This code references the Microsoft DAO 3.6 Object Library, so you will need to create a reference to that library in your project.

Public DB as Database

Public Function OpenDBConn() As Database
     
  'Opens the database connection using global db object
     
   Dim strDBName As String
   
   On Error Resume Next
   Err.Clear
   
   Set OpenDBConn = Nothing
   
  'get DBName from cell on main worksheet
   strDBName = Range("DBPath")
   If Len(Dir$(strDBName, vbNormal)) = 0 Then
    gglobalerror = "The database could not be opened. " & _
          "Please select the database file on the " & _
          "Main Tab and try again."
      Exit Function
   End If

   Set OpenDBConn = DBEngine.OpenDatabase(strDBName)
   If Err.Number <> 0 Then
      Set OpenDBConn = Nothing
   End If

End Function


Public Function SQLSelect(ByVal strSQL As String, _
       ByRef sList() As String) As Integer

'Execute the passed SQL (strSQL) and return the 
'retrieved results in the passed array (sList)
'this method is coded for the Microsoft DAO 3.6 
'library.  modify this and SQLExecute
'routines if migrating to another data provider, 
'such as SQL Server.

  Dim rsList As Recordset
  Dim iColCnt As Integer, iRowCnt As Integer, 
  DIM idxRow As Integer, idxCol As Integer

  gGlobalError = ""
  On Error GoTo Err_SQLSelect

 'try db connection.
  SET DB = OpenDBConn
  If DB is Nothing then Goto Cleanup

  Set rsList = DB.OpenRecordset(strSQL, _
                                dbOpenSnapshot, _
                                dbReadOnly)

 'check for no data
  If rsList.EOF Then
     SQLSelect = 0
     GoTo Cleanup
  End If

 'Size the array
  iColCnt = rsList.Fields.Count
  rsList.MoveLast: rsList.MoveFirst
  iRowCnt = rsList.RecordCount
  ReDim sList(iColCnt, iRowCnt) As String

 'store recordset rows in array
  Do Until rsList.EOF
    idxRow = idxRow + 1
   'Load values for the row
    For idxCol = 1 To iColCnt
      If IsNull(rsList.Fields(idxCol - 1)) Then
        sList(idxCol, idxRow) = ""
      Else
        sList(idxCol, idxRow) = rsList.Fields(idxCol - 1)
      End If
    Next
    rsList.MoveNext
  Loop

SQLSelectExit:
  SQLSelect = UBound(sList, 2)

Cleanup:
  On Error Resume Next
  rsList.Close
  Set rsList = Nothing
  DB.Close

  Exit Function

Err_SQLSelect:
 'On Error Resume Next
  gGlobalError = "Select from the database failed!  " & _
    " & Err.Description & vbCrLf & " _
    SQL: " & strSQL
  Debug.Print gGlobalError
  SQLSelect = -1

  Resume Cleanup

End Function


Public Function SQLExecute(ByRef strSQL As String) _
 As Integer

 'Execute the passed SQL statement contained in the 
 'string strSQL
  Dim strErr As String

  SQLExecute = -1
  gGlobalError = ""
  On Error GoTo Err_SQLExecute

 'try db connection.
  SET DB = OpenDBConn
  If DB is Nothing then Goto Cleanup

  DB.Execute strSQL, dbFailOnError
  SQLExecute = 1

Cleanup:
  On Error Resume Next
  DB.Close

  Exit Function

Err_SQLExecute:
  strErr = Err.Description
  gGlobalError = "SQLExecute failed!" & _
       vbCrLf & "SQL: " & strSQL & " _
       Err:" & strErr
  Debug.Print gGlobalError
  SQLExecute = -1

End Function

Excel in Front, part 1

This post is long overdue - I've been flat-out, coding a very interesting application for a manufacturing client, using Excel as the front end on a shared Access database.  The goal is to give a workgroup the ability to perform simultaneous edits to their work-in-progress tracking data, without any of the hassles, bugs, hideous file growth, sharing violations, lock-outs, etc. that you get with the built-in workbook Sharing mechanism.  There's a host of business logic involved, and they want the ability to design their own reports at will.

Again, I find myself reinventing the wheel.  "Why not develop your application in Access?" one might ask. My reasoning: Customer didn't have it, doesn't want it, can't support it, and more importantly, developing in Access is painful.  I prefer Excel.  The advantages weigh in: custom business logic is more easily coded in Excel.  It is.  And we needed in-grid editing, flexible data presentation, filtering, sorting, graphing... and whatever else we can dream up for versions 2 through N.  That's all in my Excel quiver.

"Oh, right. Excel lets you Link to a data source and populate tables..." yeah, I thought about it, but the users don't want to fart around with Data Connections, and my experience tells me it's a long road coding for those APIs. (if you know better, please tell me.)  And I wanted this to be an always-disconnected setup, where the workbook simply grabs data or sends updates to the db in quick bursts - no live links to network files that might be unavailable, renamed or changed.

The db was designed using an SQL Script to create the tables, populate the lookup data, and set up primary keys, defaults, and so on.  I ran the script to create the database in SQL Server, and then imported the tables into my .mdb file using Access.

I did not use the Access database design tools, though you certainly can.  I prefer to use a build script whenever designing a relational database; you drop and recreate the db often as you rework your design - that's Agile.  I also find that Access is a bit too user-friendly, in other words, not powerful enough.  I guess I can type SQL faster than I can ponder through menus.

In Excel, I set up a file-select control, and some text boxes for username and password.  The users on the network will each have a local copy of the application workbook - It's not linked, welded, cross-referenced or otherwise umbilical'd to any other file.  Can't you smell the freshness?  No pop-ups... ever.  As updates come out, they will be emailed to the users.  They will enter their credentials, file-pick the .mdb file, and work. That's Data Separation.

Here, again, I'm not tying into any built-in user management.  In a secure enterprise situation you probably have to set up database user accounts linked to network id's, but my client is looking for (and I strive to offer) the simplest solution wherever possible (SSWP), so we won't be contacting network support for domain credentials and such - the .mdb file is on a password-secured network drive.  Our application will maintain it's own user list... VBA code does the rest.

The project is still underway - next post I'll explain how I set up my object classes, control cell-editing, filter and sort, and conclusions: will it be another home run, or a teetering, reeking, house-of-cards? Stay tuned.

Condition "L"

Back in 1989 while using Lotus 1-2-3 in college, printer quality was not measured in dots per inch, but in dots, as in, I just got a new 9-dot printer.  A Lotus add-in called Sideways let you print your spreadsheets in landscape mode by rendering the output as a picture, scaling it, rotating it, and printing it out 9 dots at a time.  This was a major breakthrough for me, because suddenly my spreadsheets were transformed from IBM, holes on the side, six-page, Utility Bills into one-page, big-picture, Annual Reports.  Design meets Computation / Left meets Right - it all made sense to me, and was the beginning of what is shaping up to be a life-long mania for the idea of the spreadsheet.

Forward in time: it's post-2007 and Excel, in version 12.0, gives us complete control over every aspect of the look and layout of our reports. But now the screen is where we consume the majority of our spreadsheet salad; what was a static page is now dynamic.  One way to exploit this trend is to let the data control it's own appearance.  As new numbers stream in, (conditions change) the visual appearance of the numbers also change, allowing our eyes to zero in on the Information hidden amongst the Data.

Excel's Conditional Formatting feature helps us achieve this.  Though somewhat limited in 2003, the 2007 version of Excel has greatly expanded the ways conditional formatting can be applied, and the user interface has been improved dramatically.  (This is, perhaps, the only actual improvement in Excel 07, but that's another topic entirely.)  Regardless of what version you're using, it is the "Formula" option where the power of this really comes through.

For those who haven't tried it, conditional formatting lets us apply a format to a cell that is only visible if a certain condition is met.  The interesting thing is, the actual condition doesn't even have to be directly related to the cells in question.  It's one thing to say "If the value is negative, make it red.", it's another to say "If the Region is North East or North West, and the current month is between November and April, highlight the "Salt" expenditures in Blue, otherwise in show them in Yellow.  In this second example, the salt expenditures are a dollar amount, but it's the intersection of several other factors that controls how the number will appear.  That right there is pure formatting power.

The secret to correctly applying a Formula-based conditional format is knowing how relative vs. actual cell references affect the condition, and this is only a factor if you're applying the format to a range of cells.  One tip I use is to plan my format formula as though I'm writing  it for the first cell in the range, and intend to copy it to the rest of the cells - because effectively that's what Excel is doing.

For example, say I wanted to highlight every other row of cells, to give that retro altenating green & white striped computer printout look to my report.  In Excel 2003, select the entire report, and choose Conditional Formating from the Format menu, and select "Formula Is..." in the first condition's drop-down.  In the formula field, enter an IF function to evaluate the current cell's row number, like so:

=IF(MOD(ROW(A4),2)>0,TRUE,FALSE)

And then select the formatting you want - a light green cell background in this case.  Notice that we're not actually testing the data in the actual cells, but just the row numbers of the cells.  The reference to cell A4 in this case is a relative reference to the first cell in the table.  After saving the format, if I select any other cell in the table and inspect it's conditional formatting, that cell's address should appear in place of the A4.  If not, Excel might have substituted a named range or something, which is equivalent to an absolute reference - no good, because this doesn't "move" with the formula.

Using this formula, we won't disturb the alternating row coloring if the data is re-sorted or we remove or add rows.  And using this approach, along with the boolean functions AND and OR, there's no limit to how many different conditions we can include in the formatting rules for a given cell.

In Excel 2003 you ARE limited to 3 distinct conditional formats, or looks, for a given cell, plus the default format when no conditions are true.  Unfortunately, the different visual effects are not cumulative, so you will only see the formatting for the first condition of the three that evaluates to True.  No so in 2007 - you can display the first applicable format OR indicate that the rules to be combined.  For example: a value exceeding the Budget will be red, and an item covering more than 10% of the overall total will be bold, and forecasts have a blue background but actuals are green.  One glance tells me where a given value lies in all three of these axis.

In Excel 2007 you can also define up to 5 distinct looks or formats for a cell, and you get a lot more fidelity over the order and way the conditions are evaluated.  You also get expanded formatting options - specifically, the ability to create sliding color scales and to highlight unique or duplicate values are both very cool additions.

If you haven't yet mastered the conditional formatting power of Excel, I encourage you to dive in and start experimenting.  I would also love to hear about any interesting c.d. tricks you have gleaned.  Happy formatting.

iPhone Prototyping in Excel?

I just completed what has to be the funniest project I've ever worked on.  Not a typo, I really mean Funny, as in I was laughing my ass off the whole time.  This is a departure for me, as I'm usually working on financial data (lately: not so funny), and like I've said before, there's no office banter around here.  But this particular gig amused me because I was "skinning" a workbook to appear on a Television Show, and the designers who supplied me with the graphics really nailed it.

The application is sort of secret so I can't say too much about it just yet, but the TV Show, on Canada's CBC network, is called Dragon's Den.  On this "reality" program, entrepreneurs pitch their ideas to a panel of wizened business executives - Dragons - who show no mercy and pay no compliments.  Occasionally, someone impresses one or more of the Dragons, and they literally "buy-in" to the idea.  Usually, they cut the hapless entrepreneur down and send him or her off, humiliated, (or worse: try to gain full control (51% ownership) of the start-up for a fraction of the asking amount.)

My Client will be appearing on Dragon's Den, which is taping next week.  He has invented an algorithm that he intends to evolve into an iPhone applicaton, among other things.  For now it resides as an Excel model, involving some reasonably sophisticated statistical analysis and math.  My mandate was to make the model presentable on the show.  Specifically, I needed to make the spreadsheet's input screen look like an iPhone app, so my client can get the actual Dragon's themselves to provide the input data.  This is a one-off re-skin, created just for this program, with the Dragon hosts depicted with oversized heads... it's going to be really funny when it airs.

My approach was to overlay a set of masking graphics over the input section of the spreadsheet, which contains a series of Drop-down list boxes.  Anyone interacting with the file should be able to select values in the list boxes as if doing so on an iPhone.  This design simultaneously proves the technical side (the model is running in Excel and it calculates... this!), while at the same time putting the idea into context - a portable application that you use... like this. Brilliant! I wish I'd thought of it.

It was easy enough to find the elements I needed: a hi-rez PNG file of a hand holding an iPhone does the trick.  You then need to slice it up into seperate pieces so you're removing the phone's screen - that's where your spreadsheet's elements will show through.  A transparent section won't do, because you'll need to click on the controls under it.   This graphic looks really good on a spreadsheet who's cells all have a black background.

Some traps: the graphics move around and stretch as you adjust column widths and such, so you'll want to anchor those down to keep them nice.  This is done by right-clicking, selecting Format Picture... PropertiesTab... "Don't Move or Size with Cells".  Excel is a bit buggy in that it can lose this setting if you group, copy or otherwise modify the graphic, so I wrote a little routine to flip this on and off universally.  This came in handy as I was laying out the screens.

Sub float_All_Objects(blnFloat As Boolean)
  
   Dim ctl As OLEObject
   Dim shp As Shape
   Dim plcmt As Variant
   
   On Error Resume Next
   
   plcmt = xlMove
   If blnFloat Then plcmt = xlFreeFloating
   
  'form controls
   For Each ctl In ActiveSheet.OLEObjects
       ctl.Placement = plcmt
   Next
   
  'shapes, meaning graphical elements
   For Each shp In ActiveSheet.Shapes
       shp.Placement = plcmt
   Next
   
   Set ctl = Nothing
   Set shp = Nothing
   
End Sub

Another issue is that you cannot change the font size in the standard screen controls (list boxes, buttons, etc) so if you need to scale your worksheet's zoom, (to make it readable on TV, for example,) the default font size is usually incorrect.  Instead, you must use oleControls - the difference is these controls require VBA code to interact with, but they also give you full... well, control over their property set, including size, font, source data, 3-D appearance, and a bunch more.

Many Power Users get these control types confused.  It's all about which tool bar you get them from, as this illustration illustrates:  Basic form controls come from the Forms toolbar, but oleControls come from the Control Toolbox.

It's not obvious how to configure the ole controls after you have placed one on your spreadsheet.  When you right-click on one, all you can do is control basic properties such as placement, border, and caption.  For more advanced properties you need to use the Visual Basic IDE.  Your worksheet must be in Design Mode so you can select the control, and then you may edit it's properties on the VB side.  One warning, these controls won't work at all if your user's fail to enable Macros, even if you haven't written any actual code.

I really enjoyed this challenge: make the model interactive, while completely removing any hint of "spreadsheet" so it doesn't beguile the television audience.  When the segment airs in a few months, you might not even realize that Excel is running the demo, which is just one (more) of the reasons why Excel is the ultimate prototyping framework.