5 July 2010 by Ross McLean
In this podcast we caught up with Excel DNA author Govert van Drimmelen. Govert is a Mathematics lecturer at the University of Johannesburg, in South Africa. Excel DNA has had some attention from the Excel blog sphere in the past few months, Simon, Mike, and Mathias have all posted about it. I’ve been using it for a while now, and it’s brilliant. Here we talked about how Excel DNA came about, the basics of how it works, some of the new features and what’s planned for the further.
Govert van Drimmelen, famous for his Tom Selleck impression
If you don’t know what Excel DNA is, well then listen to the podcast, but in short it’s a free and easy way to get .Net code into Excel.
I’m sure you will all join with me in thanking Govert, not only for making time for the podcast, but also for all the hard work he’s done in getting Excel DNA out of the door – well done Govert!!!
And remember if you’d like to see more of Excel DNA, I’m giving a talk about it at the UK Excel Dev Con, in a weeks time!!!
Podcast: Play in new window
| Download
Tags: Addins, Excel DNA, VB.Net
Categories: C/C++, Podcasts, UDF, VB.Net, VBA, VSTA, VSTO •
No Comments »
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 »
22 October 2008 by Ross McLean
"Validating" file names is one of those things that no matter how hard you try you'll never get it 100% right. There are a loads of reasons when a file might not save, even if the name is valid, acess rights, disk space, problems with the file it's self, network issues...
Basically you have to trust the user a bit and try to pick up any basic errors.
With that in mind I found this was the simplest, and most 'bang for buck' type of function to use. It's easy and gets it right most of the time. You still need to manage errors well when you save the actual file though!!!
Private Sub txtFileName_Exit
(ByVal Cancel
As MSForms.
ReturnBoolean)
If (Me.
txtFileName.
Text Like "*[/\:*?""<>]*") Then
a = MsgBox
("Invalid filename format, please try another, " _
&
"Stuff like /, @, ~, ?, ! etc does not help.", , _
gsAppName & gsAppVersion
)
Me.txtFileName.SetFocus
End If
End Sub
Here I've added it to the Exit event of a text box, so it runs each time the user leaves the text box. Another option would be to run the check at the end of a form, i.e when OK is pressed. You'll have to think about which method will work best for your application.
Tags: Examples, VBA
Categories: UDF •
1 Comment »
9 October 2008 by Ross McLean
I've added a few more methods to the fuzzy logic ss. Namely Jaro-Winkler (nice name!) and a Longest Common Subsequence function and a Dice Coefficient function. The last 2 dont seem to be that useful, but what the hay, you never know when you might just need a Dice Coefficient right!
Heres the ss. Fuzzy Logic 2.xls
Tags: Downloads, Examples, VBA
Categories: UDF •
6 Comments »
17 September 2008 by Ross McLean
Last week I spent a bit of time working on a of Fuzzy Logic type problem. This was for a VB app, rather than anything to do with Excel, but I wanted to test the different methods quickly and in a flexibly way. Excel was obviously ideal for doing this.
I have complied about 5 methods into a workbook (very, very rough stuff mind), none of the code is my own work, it's all just gathered from around the net, I thought it might be useful for other folks (and myself again at some point!) to have it all in one work book let me know if you tied it up. The algorithms are:
Levenshtein distance
SoundEx
SoundEx2
SoundEx - Daitch-Mokotoff
Metaphone
DoubleMetaphone
So that's 6 ways then not 5 as I may have lead you to believe. The fastest by far is standard SoundEx (it should be, it much simpler than the rest!), the others have various strengths and weakness.
If you have any more, let me know!!!!
Fuzzy tests.xls
Tags: Downloads, Examples, VBA
Categories: UDF •
6 Comments »
