This Excel VBA tutorial explains how to declare private variable, global variable / public variable in VBA, and explain the scope of variable.
You may also want to read:
Function, Sub, Method, Property
Difference between Dim and Set
Scope of local variable / global variable / private variable
Procedure Level
When you declare a variable inside a Sub Procedure or Function (local variable), the variable can only be used within them.
For example, in Sub Procedure1, you have declared...
Read More
Author: Wyman W
Excel change default delimiter of CSV files
This Excel tutorial explains how to change default delimiter of CSV files from comma to other symbols.
You may also want to read:
Excel VBA convert CSV to Excel
Excel change default delimiter of CSV files
When you open a CSV file in Excel, you can see all columns are delimited by comma, you don't have to define which character you want to use to delimit the row. It is because the default delimiter of comma is configured at Windows level, all Windows applications (including Excel) will us...
Read More
Excel VBA convert CSV to Excel
This Excel tutorial explains how to convert CSV to Excel in Excel VBA.
You may also want to read:
Access Excel FSO File Methods
Import Chinese CSV to Excel
Excel VBA Import CSV into Excel using Workbooks.OpenText Method
Convert CSV to Excel in Excel VBA
CSV is a commonly used format when you need to interface data from one system to another system. For modification purpose, it is best to convert to Excel format because it can contain formatting. This tutorial demonstrates how to wr...
Read More
Quickly search email in Outlook using criteria
This Microsoft Outlook tutorial explains how to search email in Outlook using criteria and logical operators.
Search email in Outlook
If you receive dozens of emails everyday, it is extremely difficult to search a specific email or relevant emails. I have seen most people would sort by date and then sort by sender / receiver to search email, but it is extremely time consuming. Microsoft Outlook has a Search Tool which allows you specify the criteria, but that is still not quick enough to nav...
Read More
Excel VBA copy each worksheet to new workbook
This Excel tutorial explains how to copy each worksheet to new workbook in Excel VBA.
You may also want to read:
VBA Worksheets.Copy Method to copy worksheet
VBA copy workbook or file
Excel VBA copy each worksheet to new workbook
Suppose you have a workbook that contains multiple worksheets, now you want to copy each worksheet to a new workbook. In this post, I will demonstrate how to do it in Macro.
Sub cpyWS()
Application.ScreenUpdating = False
Application.DisplayAlerts ...
Read More
Excel calculate year of service with different methods
This Excel tutorial explains how to calculate year of service in Excel using different calculation methods.
Excel calculate year of service
Having worked for human resources field for some years in different companies, I have seen different systems / companies use different calculation methods to calculate year of service for annual leave entitlement. In this post, I want to summarize how to use Excel to calculate year of service using different methods to help HR professionals.
Method 1 - ca...
Read More
Access VBA create Query using CreateQueryDef
This Access tutorial explains how to create Query in Access VBA using CreateQueryDef Method.
You may also want to read:
Change Query criteria
Access VBA create Query
Sometimes we don't want to create Query in Query Design View, because you may create a Query with criteria depending on a variable. To create Query in Access VBA, you can directly write a SQL statement in Access VBA, and then create a Query using the SQL statement with CreateQueryDef Method.
Syntax of CreateQueryDef
expres...
Read More
Access Excel VBA rename workbook or file
This Access Excel VBA tutorial explains how to rename workbook or file in VBA using Name As.
Access Excel VBA rename workbook or file
It is very easy to rename a workbook or file in VBA using the below syntax. Name and As are the keywords.
Name currentFilePath As newFilePath
For example, suppose we have a file called test.xlsx in Desktop, and we want to rename it as test2.xlsx, run the below Procedure
Public Sub renameWorkbook()
Name "C:\Users\WYMAN\Desktop\test.xlsx" As "C:\Users\WYMA...
Read More
Excel Security Warning – Macros have been disabled
This Excel tutorial explains how to solve the problem of Excel Security Warning - Macros have been disabled.
Excel Security Warning - Macros have been disabled
This Excel Security Warning is one of the most annoying warnings in Excel. If you click on the Options button, you will see the below message.
In fact, this message does say something about the reason for this alert, it explained that this error is caused by encrypted Macro. What it means encrypted is that the Workbook contai...
Read More
Access Excel VBA copy workbook or file
This Access Excel VBA tutorial explains how to copy workbook or file.
You may also want to read:
FSO File Methods
Worksheets.Copy Method to copy worksheet
Access Excel VBA copy workbook or file
There are two common ways to copy workbook or file
FSO.CopyFile Method
FileCopy Function
I have explained how to use different FSO Methods in my previous post, click to see more details.
Using FSO.CopyFile Method to copy workbook
You should be able to run FSO in Excel 2013. If you fa...
Read More