28 May 2010 by Ross McLean
Charles Williams is probably best known for his work in Excel calculation speed and VBA performance, not to mention the worlds most famous Excel Addin Name Manager. In truth, he’s a expert in many areas of Excel and on top of all this he’s a great bloke. In this podcast we got down and dirty with Excel performance, uncovering some interesting insights along the way. It was a true pleasure talking with Charles.
Charles the First - King of Speed!
Charles in the founder of Decisions Models, a consulting firm specialising in Excel development, and the farther of the FastExcel addin, we talked about this addin and how it works and also his addin loader tool. This is one for the archives!
Thanks to Charles for making the time and sharing his knowledge with us all.
Podcast: Play in new window
| Download
Categories: Addins, Code, Functions, Podcasts, Spreadsheet Design, UDF •
No Comments »
20 December 2006 by Ross McLean
Here’s something i did not know about VBA!
http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl?num=1166424720/0#8
Cool, now i just have to get a job at the Natural History Museum.
Tags: Examples, VBA
Categories: Code •
1 Comment »
14 August 2006 by Ross McLean
Ran in to an old friend today, and it took me a while to remember what to do. After adding a list box to your user form, you’ll want to resize it so that it looks nice. At design time you can get all your controls lined up without too much effort. Like this:
However when the form is run, the list box automatically resizes itself! Like this!
The answer is to change the a default setting, the “IntegralHeight”, and problem solved. The difference is that now your list box will show parts of lines that don’t fill the list box exactly, rather than resizing to accommodate only whole lines.
Tags: Examples, Forms, VBA
Categories: Code •
2 Comments »
8 August 2006 by Ross McLean
The situation. Â
Back end Access DB, front ended with an Excel Form. Add a new record to a table in the DB and run a Make Table Query to sort the whole table in to alphabetical order. Reload the table into a list box.
I need to make a new table because simply sorting the existing table does not results in a alphabetica import into the list box.
The problem is that when I run the SQL for the update query, if the table name is the same as a table already in the DB then DAO errors. In Access you have to click a message box to allow the deletion of the old table.
From VBA I can use DAO with "TableDefs.Delete" to remove the old table then the solution works fine. But I don't really like this idea, it is clunky and I don't like deleting table danger!
So what's a better way?
The workhorse code looks like this:
Sub SendNewProject
(sPro
As String, sDes
As String)
   Dim i As Integer
   Dim db As Database, rs As Recordset, r As Long
   Dim sSql As String
   Set db = OpenDatabase(gsDataBase)
   Set rs = db.OpenRecordset("tblProject", dbOpenTable)
   With rs
       .AddNew
       .Fields("Project") = sPro
       .Fields("Description") = sDes
       .Update
   End With
   '''close table we will delete shortly
   rs.Close
   Set rs = Nothing
   '''run updatequery to update project list
   sSql = " SELECT tblProject.Project INTO tblProjectS" _
          & " FROM tblProject" _
          & " ORDER BY tblProject.Project;"
   '''delete old projectsS table
   db.TableDefs.Delete "tblProjectS"
   '''run make new table
   db.Execute sSql
   '''close DB
   db.Close
   Set db = Nothing
   MsgBox "Project has been added to the projects list" _
          & vbNewLine _
          & vbNewLine _
          , vbOKOnly, gcsMsgboxhead
End Sub
Tags: Examples, VBA
Categories: Code •
No Comments »
