JP asked if I could expand on the Cancel post. I thought that I had already posted a DoEvent example, but I couldn’t find it on this blog, although when I dug out the org. file it did have a link to a blog post that didn’t work!!!! Maybe my blog is losing content!
Anyway here is an example file which shows what I mean:
MIE DoEvents Example 2008.xls
I like to allow users the opportunity to cancel out of long loops if they need to. I use a global variable, something like gbCanacel, which gets set to True when the user clicks the Cancel button.
Then my looping checks for this with an if statement, normally the last thing inside the next/loop statement. If it detects that gbCancel is True, then I reset gbCancel and exit the sub. With liberal use of DoEvents this works quite well.
If you implement Cancel, do you do it the same way. If not how and why!!!!
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
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
NB: See comments for errors and corrections to this code!
Today I needed to get the Standard Deviation for a number of counts of scores. Like this:
I dont know of a way to do this in the worksheet, and a quick google didnt turn anyhting up eirther, so it was off to VBA, this is what I came up with:
[VBA]
Function SDFromCount(Counts As Range, Values As Range)
Dim Scores() As Double
Dim i As Integer
Dim j As Integer
Dim k As Integer
k = 1
For i = 1 To Values.Cells.Count
j = j + Counts.Cells(1, i).Value
Next i
ReDim Scores(1 To j)
For i = 1 To Values.Cells.Count
For j = 1 To Counts.Cells(1, i).Value
Scores(k) = Values.Cells(1, i).Value
k = k + 1
Next j
Next i
SDFromCount = WorksheetFunction.StDev(Scores)
End Function
[/VBA]
This works with data in rows (one row, many columns), you would have to swap the counter i to the row index to get it to work with a column of data.
Any better ways? Thoughts on this code?
Cheers