In different parts of the world, there are different languages spoken and written. With this, a VBA programmer also faces language related issues while writing a VBA program. Here is a common date related problem solved which converts a date written in MM.DD.YYYY format into Excel standard date.
'This function converts a date from MM.DD.YYYY to system date format
Public Function ConvertToDate(strInputDate As String) As Date
'Variable Declaration
Dim iMonth As Integer
Dim iDay As Integer
Dim lYear As Long
'
'Get month from first 2 digits
If IsNumeric(Left(strInputDate, InStr(1, strInputDate, ".") - 1)) = True Then
iMonth = Left(strInputDate, InStr(1, strInputDate, ".") - 1)
Else
ConvertToDate = Null
Exit Function
End If
'Get day from input date
If IsNumeric(Mid(strInputDate, InStr(1, strInputDate, ".") + 1, 2)) = True Then
iDay = Mid(strInputDate, InStr(1, strInputDate, ".") + 1, 2)
Else
ConvertToDate = Null
Exit Function
End If
'Get year from last 4 digits
If IsNumeric(Right(strInputDate, 4)) = True Then
lYear = Right(strInputDate, 4)
Else
ConvertToDate = Null
Exit Function
End If
'Create a complete date and return the value
ConvertToDate = CDate(iDay & "-" & MonthName(iMonth) & "-" & lYear)
'
End Function
1. Open an Excel file
2. Press Alt+F11
3. Insert a Module (Insert>Module) from menu bar
4. Paste the code in the module
5. Now write ConvertToDate formula in a cell and select the cell which contains a date in MM.DD.YYYY format
6. You may get the converted date in number format like below
7. To change the date format into the desired format, right-click on the cell and select ‘Format Cells…’
8. In the ‘Number’ tab, click on ‘Date’ category and select the format type you want to display (here I have selected DD-MMM-YYYY format)
9. Done, your date is converted. You can drag the formula to other cells as well
To help you practice this code, we have made this code available through practice file. Click on the below link to download the practice file.
To ensure that your VBA project works smoothly without any error, you need to put lot of error handlers and validations. Here is one of them, below code can be used to check if the given path is valid or not. You just need to provide the path of the file to the function and it will check if the file exist at the location. If it returns True that means the file is available at the path, if it returns False that means it is invalid path or the file does not exist at the location.
Learn how to write your first VBA macro in Excel to automate repetitive formatting tasks across multiple worksheets. This beginner-friendly guide will walk you through the process step-by-step, from recording your actions to customizing the code for your specific needs.
In MS Access, the best way to create a multiuser tool is to divide your solution. One part acts as interface and other one acts as database. You can have multiple copies of the interface distributed to users which are connected to central MS Access database saved at common shared drive. To connect the interface to database, you can use link table feature (Access>External Data>Import & Link) available in MS Access. Below is a commonly required VBA code which helps the developers to re-link MS Access linked tables when the database is renamed or moved to other location
Have you ever got into situation in office where you need to count the cells in Excel sheet with specific color? If yes then you can use following code which counts the number of cells…
VBA to Read Excel Data Using Connection String Sometimes as a programmer you need to read heavy (more then 5 MB) Excel files. There are two ways you can read data from Excel files: Open…
VBA Code To Add Items In Listbox Control Using ListBox in Userform is very common. You can use ListBox.AddItem function to add items in the listbox.; however, it is little difficult to add items in…