How to: VBA email validation

by Robert Martim 2. July 2009 19:10

This is a follow up to my post on sending email messages using Windows Vista. You can read the previous post here: http://www.msofficegurus.com/post/How-to-Windows-Vista-SMTP-Server-Using-CDOMessage.aspx

In this post, I discuss email validation using VBA. VBA email validation is a very simple process that does not require much, but you will need to install the Microsoft VBScript Regular Expressions 5.5.

In order to install the reference, follow these steps:

1. Open VBE (Alt+F11)
2. Go to “Tools --> References…”
3. Search in the reference list for Microsoft VBScript Regular Expressions 5.5, select it and click OK to continue.

The figure below shows the reference installed:

Figure 1: Microsoft VBScript Regular Expressions 5.5 reference installed

NOTE:
Only today, after receiving a comment on this post, I noticed that I was using Excel in Portuguese and not English. Since I am always changing the language settings, I sometimes forget to change back to the language I am writing on. I will leave the image as it is, as I am sure everyone can get the picture (if you will forgive me the pun). Added: July 3rd, 2009

Now, you can create a boolean user-defined function to test an email string to check whether it is a “regular expression”. In my case, I check for the following pattern:

\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*

There are a number of patterns that you can you and you should be able to find my patterns, especially in sites that teach java or php (check RFC 2822 for info on pattern). You can then borrow one of those patterns and use in your code.

The user-defined function will look like this:

Function ValidateEmail(ByVal sEmail As String) As Boolean

    Dim oRegularExpression     As RegExp

'   Sets the regular expression object
    Set oRegularExpression = New RegExp

    With oRegularExpression
'   Sets the regular expression pattern
        .Pattern = "\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"

'   Ignores case
        .IgnoreCase = True

'       Test email string
       ValidateEmail = .Test(sEmail)
    End With

End Function

You can now test the user-defined function with a simple call:

Sub Test_ValidateEmail()
    MsgBox ValidateEmail("my.email@hotmail.com")
    MsgBox ValidateEmail("your.EMAIL@hotmail,com")
End Sub

Be the first to rate this post

Currently 0/5 Stars.
1
2
3
4
5

Tags: microsoft office, excel, vba, udf, vista

How to: Excel 2007 Classic PivotTable AutoFormat

by Robert Martim 25. June 2009 11:12

A few days ago, I was writing an article and someone asked me: how the heck do I use the classic AutoFormat for my Excel 2007 PivotTable?

The image below shows xlReport4 AutoFormat (the data is not in English, as the original article was not in English – but I am sure you get the picture):

Figure 1: Excel 2007 PivotTable Classic AutoFormat

Well, an alternative to this is to use VBA. The following code would suffice:

Sub Test()
ActiveSheet.PivotTables("PivotTable1").Format xlReport6
End Sub

In this case, I use xlReport6 instead of xlReport4. However, this approach may not be acceptable to all users, after all, not everyone is fluent with VBA. You could, of course, create a simple customization to apply this classic PivotTable format, but what about the other AutoFormat which are available?

Well, there is a simpler way to achieve that. Simply use the accelerator key combination. Follow these steps:

1. Select the Excel 2007 PivotTable
2. Type the key sequence: Alt à o à a. Notice that this sequence will vary according to the language. This sequence is for English. The sequence in Portuguese (BR), the language I was writing the original article, is Alt à f à a.

The Excel 2003 AutoFormat dialog box will open:

Figure 2: Excel 2003 Classic AutoFormat dialog box

Simply choose the AutoFormat you want and click OK. You’re set to go.

Currently rated 5.0 by 1 people

Currently 5/5 Stars.
1
2
3
4
5

Tags: excel, excel 2007, vba, pivottable

Microsoft Excel | Microsoft Excel - VBA | PivotTable

How to: Windows Vista SMTP Server - Using CDO.Message

by Robert Martim 22. June 2009 21:38

I had been using CDO object with Windows XP for a while and the code always ran without a problem, as long as the SMPT server was installed and properly set up. That was easy to do. As I moved the code to Windows Vista, I come to realize that my code no longer worked. I tried installing the SMTP Server on Windows Vista, but that was pointless since it is no longer available. I was having some trouble believing it was not there given that I used Windows Vista Ultimate, but fellow MVPs Bernard Cheah (http://www.iis.net/ http://msmvps.com/blogs/bernard/) and Gary VanderMolen confirmed my worries. Ron de Bruin, a fellow Excel MVP, also took part in the conversation about the issue. He also has an article on the subject which can be found here: http://www.rondebruin.nl/cdo.htm

According to Gary, this is pretty much useless anyway given that most ISPs nowadays block all port 25 traffic except to their own SMTP server.

In that case, I had to adapt things slightly so that I could route my emails through my SMTP Server. Nonetheless, I can still use Vista to route the email to a local pickup folder so that I can at least test things.

Here’s how it is done (uncomment the code to test locally):

Sub CDOTest_OnVista()
    Dim oCDO                    As Object
    Dim oCDOConfig              As Object

    Const c_sUsername           As String = "Your_Username_Goes_Here"
    Const c_sPassword           As String = "Your_Passoword_Goes_Here"

'   if you are using localhost to route the e-mail, you need to define
'   a pickup folder. In this case, I use: "C:\inetpub\mailroot\Pickup"
    Const c_sPickupFolder       As String = "C:\inetpub\mailroot\Pickup"
    Const c_iSMPTPort           As Integer = 25


    Dim iSendUsing              As Integer
    Dim sSMPTServerName         As String

   iSendUsing = 2      'remote host
'   iSendUsing = 1      'localhost

'   Define the SMTP Server here. "localhost" for using Vista's localhost
'   as the server. In the case below, you should use your own server.
'   sSMPTServerName = "localhost"
    sSMPTServerName = "Your_SMTP_server_goes_here" 'e.g.: smtp.youcompanyname.com

    Set oCDO = CreateObject("CDO.Message")
    Set oCDOConfig = CreateObject("CDO.Configuration")

    With oCDOConfig.fields
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = iSendUsing
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = sSMPTServerName
        .Item("http://schemas.microsoft.com/cdo/configuration/username") = c_sUsername
        .Item("http://schemas.microsoft.com/cdo/configuration/password") = c_sPassword
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = c_iSMPTPort
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverpickupdirectory") = c_sPickupFolder
    End With

    With oCDO
        Set .Configuration = oCDOConfig
            .From = Sender@Email.com
            .To = Receiver@Email.com
            .Subject = "Test Message"
            .Configuration.fields.Update
            .Send
    End With

    Set oCDOConfig = Nothing
    Set oCDO = Nothing

End Sub

Currently rated 5.0 by 1 people

Currently 5/5 Stars.
1
2
3
4
5

Tags: excel, vba, vista

Microsoft Excel | Microsoft Excel - VBA | Vista

What is the factorial of 0.5 (0.5!)?

by Robert Martim 16. June 2009 20:25

If you use Excel and enter the following formula: =FACT(0.5) the answer will be one. However, if you open a more rustic tool such as the Windows Calculator and try again, you will get 0.886226925510…

So, how can a tool such as the Windows calculator come up with the correct answer when Excel can only return 1? What about the factorial of 0.75 (0.75!)? Again, Excel returns 1 whereas the Windows calculator gets the correct answer: 0.919062526898...

Both numbers are close to one, but they are not exactly one.

The trouble is that Excel is using the general rule that states that the factorial is, in fact, the product of all integers in the series. So 4! Is given by =(4*3*2*1)=24. Unfortunately, the story does not end there for factorials.

When dealing with factorials the above works fine for integers. When dealing with non-integers (greater than zero), then we need the natural logarithm of the gamma function to get it to work. This function uses the concept of limits to determine the factorial of a number, so the factorial of 5 (5!) would be 119.99999998… when using gamma instead of the integer 120 (you can obviously round to 120, since it is effectively this number). For a discussion on the gamma function check this link: http://numbers.computation.free.fr/Constants/Miscellaneous/gammaFunction.html

Supposing the number you wish to find its factorial is located in cell A1, your formula using the gamma function would be:

=A1*EXP(GAMMALN(A1))

The last thing to rember is that this is for positive numbers only…

Be the first to rate this post

Currently 0/5 Stars.
1
2
3
4
5

Tags: excel, excel 2007, formula, function

Microsoft Excel

How to: VBA email validation

How to: Excel 2007 Classic PivotTable AutoFormat

How to: Windows Vista SMTP Server - Using CDO.Message

What is the factorial of 0.5 (0.5!)?

Calendar

Month List

RecentPosts

Mo	Tu	We	Th	Fr	Sa	Su
29	30	1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31	1	2
3	4	5	6	7	8	9

Mo	Tu	We	Th	Fr	Sa	Su
29	30	1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31	1	2
3	4	5	6	7	8	9

How to: VBA email validation

How to: Excel 2007 Classic PivotTable AutoFormat

How to: Windows Vista SMTP Server - Using CDO.Message

What is the factorial of 0.5 (0.5!)?

Newsletter

Thank you

Calendar

Month List

RecentPosts

Category list

Tag cloud

BlogRoll

Mo	Tu	We	Th	Fr	Sa	Su
29	30	1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31	1	2
3	4	5	6	7	8	9