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!!!
[vba]
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[/vba]
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 »
22 October 2008 by Ross McLean
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
Tags: Downloads, Examples, VBA
Categories: General •
3 Comments »
21 October 2008 by Ross McLean
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!!!!
Tags: Examples, VBA
Categories: General •
4 Comments »
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 »
