Codematic Excel developers logo
Codematic spreadsheet-excel development image

Rapid Application Development / Advanced Excel Development

 
Products for Excel
Commercial Products:
  - Classic Ribbon
  - Alt-FileSearch
  - Password Remover
Spreadsheet Quality Products
Free Products
Excel Development
Excel Development Index
Excel VBA
- VBA IDE (editor)
- VBA Training
- VBA Best Practice
- VBA Performance
- COM Performance
- VBA Security
Excel and Databases
Excel and Pivot Tables
Excel Add-ins
Worksheet Functions
Excel and xlls
Excel (in)security
Excel testing
Excel and .net
Excel External Links
Excel Developer Types
Professional Excel Development
Excel 2007
Excel 2010
Excel Development Archive
Spreadsheet Services
Spreadsheet Development
Spreadsheet Migration
Spreadsheet Maintenance
Spreadsheet Review
Spreadsheet Management
Resources
Excel User Confs
Consultant Profile
Book Reviews
Links
Other
Site Map

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

Review by Simon Murphy

Summary

A superb event, well run, with great content in an excellent location. Overall fantastic value for money even including the flights from the UK and accommodation.

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

The location at the Stockyards in Fort Worth was superb with everything close at hand, so no need for taxis or anything. We ate out at several different places and the food was all excellent, including the delicious lunches. There was also plenty of chance for a drink and a chat with the social aspect being outstanding - we soon discovered that we had things other than Excel in common.

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.

 

Upcoming Events:

25 January 2012 - UK Excel Developer Conference - London


Products for sale:

AltFileSearch

Office 2007 FileSearch replacement logo

New information about the missing FileSearch feature in Office 2007 and details of our pragmatic solution (Current price GBP 30.00)


wsUnprotector

worksheet password remover logo

Instant Excel worksheet protection remover and password recovery (Current price GBP 15.00)


Classic Ribbon Tab

classic ribbon for office 2007 logo

Add Excel 97/2000/2002/2003 compatible menu structure to Excel 2007
(Current Price GBP 10.00)


 

Products coming soon:

Link Manager

(Find and control external links in Excel Workbooks)

Due by Q1 2111.

XLAnalyst Pro

(Excel VBA based spreadsheet auditing tool)

Due before the end of 2111.

  ;-)
This page was last reviewed on December 21, 2011

©Codematic Ltd 1999-2011