UDF

Validating file names

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!!!

PLAIN TEXT
VBA:
  1. Private Sub txtFileName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  2.     If (Me.txtFileName.Text Like "*[/\:*?""<>]*") Then
  3.         a = MsgBox("Invalid filename format, please try another, " _
  4.             & "Stuff like /, @, ~, ?, ! etc does not help.", , _
  5.             gsAppName & gsAppVersion)
  6.  
  7.         Me.txtFileName.SetFocus
  8.     End If
  9. 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: ,
Categories: UDF • 1 Comment »

More Fuzzy Logic!!

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: , ,
Categories: UDF • 6 Comments »

Fuzzy Logic!

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: , ,
Categories: UDF • 6 Comments »

Standard Deviation of Counts

4 May 2008 by Ross McLean

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:

countscores1.PNG

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:

PLAIN TEXT
VBA:
  1. Function SDFromCount(Counts As Range, Values As Range)
  2. Dim Scores() As Double
  3.  
  4. Dim i As Integer
  5. Dim j As Integer
  6. Dim k As Integer
  7. k = 1
  8.  
  9.  
  10. For i = 1 To Values.Cells.Count
  11. j = j + Counts.Cells(1, i).Value
  12. Next i
  13. ReDim Scores(1 To j)
  14.  
  15.  
  16. For i = 1 To Values.Cells.Count
  17. For j = 1 To Counts.Cells(1, i).Value
  18. Scores(k) = Values.Cells(1, i).Value
  19. k = k + 1
  20. Next j
  21. Next i
  22.  
  23.  
  24. SDFromCount = WorksheetFunction.StDev(Scores)
  25.  
  26. End Function

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

Tags: ,
Categories: UDF • 10 Comments »

Geocoding in Excel using Google Maps API

12 November 2007 by Ross McLean

Heres a little example of some VBA functions I put together to get geocodes into excel using the google maps API.
It's not the best geocoder in the world, but it does ok in the test I've done so far, although it does just come up with blanks for some locations. To use it you will need a Google maps API key which you need to store in a text file on the same path as the workbook, details are on the first sheet of the workbook.

geocopic.png

As for the code itself I had to automate IE rather than use an API or the HTTP or XMLHTTP libraries, as my corporate firewall would not allow access using these. The function only asks for 1 string, so you have to add spaces yourself, this might be done better, but it suited my needs (that is to say, I didn't bother writing in optional arguments etc.)

It's worth while looking at the code as I have done a few things that might not be considered "best practice", mainly not closing the IE application when the function has run - this is to speed up the function - there is a public function that you can use to close it from a worksheet if you wish (see code as it's not explained in the "documentation")

Theres probably some other stuff that I should mention, but I did this a week or so ago and have not been able to post it as my internet connection was down, so I have forgotten that!

Feel free to post any questions and I'll feel free not to answer them!!!!

Enjoy
Ross

Download M.I.E Google Maps Geocoding Example (zip)

Tags: , , , ,
Categories: Downloads, Excel + Web, UDF • 22 Comments »

« Older Entries