Rapid Application Development / Advanced Excel Development

Other Excel Resources

Simon Murphy's Responses to Excel Lists etc

This page allows me to simply upload files to demonstrate issues to help users of the lists I contribute to. As I answer questions I'll post [a summary of] the question, my response and any required workbook here for anyone to look at. I'll also post anything else I think people may find useful, like white papers and slides from presentations. I'll keep adding at the top so the oldest posts move down as new ones are added. Any problems email me.

Simon is now regularly blogging over at smurfonspreadsheets, that is well worth a visit

July 06

Excel user conference

Spreadsheet Design slides

Converting VBA XLA add-ins to COM using VB6 Slides

Basic Excel COM add-in starter VB6 project (Zip of .vbp)

VBA Best Practice Slides

Eusprig

Reviewing Spreadsheets paper

Reviewing Spreadsheets Slides

Feb 06

After a discussion of Excels place in the development landscape with XL-Dennis I sent him this photo from a course spec I planned. I know its not beautiful, but hopefully its understandable and/or useful. It describes some of the ways you can extend Excel. At some point we have done pretty much all of these for someone.

Jan 06

Sorry things have been so quiet for a couple of months - having a few RSI problems. I have been avoiding all non essential computing since October. However a change of physio and a change of painkillers and I can manage an hour or 2 per day now. Joinery and Cabinet making remain unaffected for anyone wanting a nice piece of hand made furniture!

October 05

Using and Extending Excel with Visual Studio (Presentation at DDD2 Microsoft, Reading)

Simon presented a session covering the different ways of working with Excel from Visual Studio. This included a VSTO demo, creating a COM add-in and creating an Automation add-in (for a worksheet function). It was only a small audience as mainstream developers continue to fixate on 'sexy' web developments rather than leveraging the powerful tools already on peoples desktops. The session was well received with plenty of in depth techy discussion afterwards. If you attended please get in touch to swap ideas and war stories.

More info about DDD2 (DeveloperDeveloperDeveloper - a community event) go here.

I've actually set up a section on the site to host all the VS stuff I do, check out the Visual Studio Excel page for the slides and source code.

September 05

Spreadsheet Design Concepts slides from Excel User Conference

First ever Excel User Conference - Forth Worth USA

Summary

Content Details

Jim Spicer discussed some of the technical challenges they had to overcome combining Excel and Jet (the Access data engine) to create their dynamic worksheet generator.

Bob Umlas wowed the crowd with tips and tricks that could save all the audience many hours of misery. I think everyone at some point thought - 'if only I had known that a couple of weeks ago.'. Bob now has a book out, to remind us in the future.

Olav Mjelde demonstrated advanced use of VBA arrays to get around the 64k row limit and also to boost performance. These techniques are ones he uses in enterprise level commercial applications.

Jon Peltier demonstrated some powerful charting techniques to present information in more effective ways. One very useful one was how to create a graph with a broken Y scale.

Bob Umlas gave a basic introduction to Excel VBA userforms and also demonstrated some very useful techniques for reusing previous work.

Simon Murphy (me) presented a session on spreadsheet design and demonstrated some free tools to help build robust spreadsheets. Main point was to separate out the different parts of a model to make it easier to understand.

Mike Alexander gave a demonstration of most of the main features of pivot tables, an essential technology. Mike has a book out Pivot Table Data crunching which is excellent.

Jon Peltier demonstrated some of the issues in combining Excel and PowerPoint and in particular how charts often misbehave between the two applications.

Bob Umlas gave a session on array formulas and solved some seemingly impossible questions with concise (although complex) formulas.

Bernard Liengme closed the conference with a session on using Solver and a demonstration of some of the teaching aids currently being used in spreadsheet training.

Other aspects

Don't miss out next time, the next one is due spring 2006.

www.exceluserconference.com

August 05

Question: The following code crashes Excel (all versions) whenever more than 1 cell is selected - how can I insert the event code?

Sub AddCheckBoxes()
' For each cell in the selection, sticks a check box, with a simple event handler

Dim cel As Range
Dim ctl As OLEObject

For Each cel In Selection
Set ctl = ActiveSheet.OLEObjects.Add(Classtype:="Forms.CheckBox.1", _
Link:=False)
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet. CodeName).CodeModule
.InsertLines .CountOfLines + 1, "Private Sub " & ctl.Name _
& "_Click()" & Chr(13) _
& " MsgBox """ & ctl.Name & """" & Chr(13) _
& "End Sub"
End With

Next cel

End Sub

Answer: (After a bit of testing and lots of crashes) VBA doesn't seem to like the repeated call to insert code - I suggest you build the string in a loop and then write all the vba in one go:

Sub AddCheckBoxes()
' For each cell in the selection, sticks a check box, with a simple event handler

Dim cel As Range
Dim ctl As OLEObject
Dim strVBA As String

Application.EnableEvents = False

For Each cel In Selection
Set ctl = ActiveSheet.OLEObjects.Add(Classtype:="Forms.CheckBox.1", _
Link:=False, Top:=cel.Top + 2, Left:=cel.Left + 2, Height:=10, Width:=10)
MsgBox "box in ok?"
DoEvents
strVBA = strVBA & vbCrLf & "Private Sub " & ctl.Name _
& "_Click()" & Chr(13) _
& " MsgBox """ & ctl.Name & """" & Chr(13) _
& "End Sub"
Next cel
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
.InsertLines .CountOfLines + 1, strVBA
End With
MsgBox "code in ok?"
Application.EnableEvents = True

End Sub

Also tried the usual adding DoEvents, setting objects to nothing, splitting routines. Other posters suggested possible anti-virus interference, but the original poster said code had crashed on a machine with no anti-virus intalled.

 

Question: Can you write User Defined Functions in C#? And do you use Visual Studio Tools for the Office System (VSTO) to do it?

Answer: yes you can create user defined functions in c#.
the best reference is Andrew Whitechapels book .net development for Office.

This is separate from VSTO. VSTO effectively turns office into smart clients, where your .net app is the boss. For UDFs excel will be the boss, so VSTO is not required. Also VSTO is 2003 only, C# UDFs will work in 2k, xp and 2003.(They are Automation add-ins)

Using .net is pretty similar to COM or Automation add-ins ( as in VB6).

Consider performance though, its expensive going across so many interfaces, your c# code will need to be doing plenty of work to make up.

cheers
Simon

July 05

Simon presented a paper to the European Spreadsheet Risk Group discussing some of the strengths and weaknesses of spreadsheets compared to other development tools. Download a pdf of the paper here and the powerpoint slides here.