Home

Last month Microsoft annouced two new data types in Microsoft Excel.

Today, we are announcing new, cloud-connected data types in Excel, starting with Stocks and Geography. With added AI capabilities, Excel can now recognize rich data types beyond numbers and text strings. For example, Excel recognizes that “France” is a country and automatically associates it with additional attributes such as population and gross domestic product, using the same technology that powers Bing answers. These attributes can then be populated into different cells or used directly in formulas and stay updated with the latest data. Based on machine learning, these new data types will simplify the process of working with real-world data. In the future, we’ll add organizational data types backed by the Microsoft Graph to Excel—intelligently enhancing your spreadsheets with even richer content.

Here's a link to a preview on the new Microsoft Excel data types, Stocks and Geography, where they discuss the features and how to use the new data types. For example, you can use a dot “.” operator to get the population of a city. If cell B3 was 'France', in another cell you could type the formula =B3.Population to get the population of France. Other fields include; Area, Country/region, Latitude, Longitude.

Here's some code I adapted from two seperate threads at MrExcel's forum to check whether an email address is valid email pattern, i.e. This email address is being protected from spambots. You need JavaScript enabled to view it., AND also if the domain is valid.

IsEmailAddress("This email address is being protected from spambots. You need JavaScript enabled to view it.") = TRUE
IsEmailAddress("me@test@com") = FALSE
IsEmailAddress("This email address is being protected from spambots. You need JavaScript enabled to view it.") = FALSE

IsURL("http://test.com") = TRUE
IsURL("http://test.xom") = FALSE

Error Handling

No matter how carefully you write VBA code, there is always the possibility that an error will occur for a variety or reasons. It is essential that you include error handling and trapping so that the user doesn't just get a ugly generic error message.

The basic structure for adding error handling in you VBA code is a follows

Private Example()

  'Specify what happens when an error occurs
  On Error GoTo ErrMsg:

'Exit the function here.
ExitHere:
  'Place any code you always want to run before exiting the code, _
   e.g. set cursor to default cursor, close VBA objects

  'Finally exit the subroutine (or function)
  Exit Sub

'Error handling code
ErrMsg:
  'Handle Errors
  MsgBox "Uh-oh! An error occured!" & vbNewLine & vbNewLine & _
         "Error Number: " & Err.Number & vbNewLine & _
         "Error Description: " & Err.Description, vbCritical
  'Resume code execution @ ExitCode label
  Resume ExitHere:

End Sub