Access Excel VBA Array

This VBA tutorial explains how to create and use VBA Array in Access and Excel VBA. Access Excel VBA Array In Excel worksheet, you can convert a formula arguments to Array by pressing CTRL+SHIFT+ENTER, click here to see example. In VBA, Array is a variable that can store multiple data of the same data type. There are two types of Array in VBA One-dimensional Array Two-dimensional Array For most of the time we use One-dimension Array, therefore this tutorial will only talk abou...
More

Access Excel INT Function to get the integer of number

This Access / Excel tutorial explains how to use INT Function to get the integer part of a number. Access Excel INT Function to get the integer of number When you have a number with decimal places, you can separate the number into integer part using INT Function, and then work around to get the decimal part. INT Function can be used in Access and Excel, and also VBA. Syntax of INT Function INT(number) INT Function only contains one argument, which is the number from which you want to get th...
More

Excel VBA convert cell contents into table

This Excel VBA tutorial explains how to convert cell contents into table using Exel VBA. Excel VBA convert cell contents into table Yesterday when I tried to copy a table from a website to Excel, all the table contents are pasted into a single cell instead of copying the whole table. I believe there are workarounds to copy table but since this is an Excel blog, I want to write a Macro to convert cell contents back into a table. Previously I also wrote a post to convert cell contents into ro...
More

Excel automatically refresh pivot table

This Excel tutorial explains how to automatically refresh pivot table using Macro / VBA and Pivot Table Options. Excel automatically refresh pivot table You may have a pivot table that links to data in the same workbook or external data, and you may want to refresh pivot table automatically in order to see the most updated data. In this tutorial I will show several ways to automatically refresh pivot table. Method 1 - Automatically refresh pivot table when opening the file This is the simple...
More

Access Excel VBA delete all workbooks in folder

This VBA tutorial explains how to delete all workbooks in folder using Access VBA and Excel VBA. Access Excel VBA delete files in folder Suppose you want to write a Macro to export files to a folder. There could be problem if the folder already contains the files you want to export. To avoid this, you probably want to delete the folder and create a new folder before exporting the files. Alternatively, you can also delete all files in the folder. In order to delete files, you can use FSO Fil...
More

Access Excel VBA delete folder if folder exists

This tutorial explains how to use Access / Excel VBA to delete folder if folder exists already. Access Excel VBA delete folder if folder exists Suppose you want to write a Macro to export files to a folder. There could be problem if the folder already contains the files you want to export. To avoid this, you probably want to delete the folder and create a new folder before exporting the files. Alternatively, you can also delete all files in the folder. In order to delete a folder, you can u...
More

Excel VBA delimit Cell value into rows

This Excel tutorial explains how to delimit cell value into rows using VBA and non VBA. You may also want to read: Convert cell contents into table Access Excel custom split Function to delimit text Excel VBA delimit Cell value into rows To demonstrate how to delimit Cell value into rows, I use email address as an example. One day I tried to copy a list of email addresses from Outlook into Excel.   When I paste to Excel 2013, all email addresses are pasted to a singl...
More

Excel get formula of a cell using FormulaText Function and VBA

This Excel tutorial explains how to show / get formula of a cell using FormulaText Function and VBA. Search formula of a Cell Assume that you have the below spreadsheet and you don't know whether any cells contain a formula.   Press CTRL + F to open Find and Replace dialog In the Find what text box, enter equal sign = , then press Find Next button Note that if a cell contains equal sign in Cell Text, it will also be searched. Show formula of a Cell Navigate to Formul...
More

Excel VBA Worksheet.Select Method to select worksheets

This Excel VBA tutorial explains how to use Worksheet.Select Method to select a single worksheet or multiple worksheets. Select worksheets in Excel When you click on a worksheet tab, the worksheet is highlighted. To select multiple worksheets, you can hold down Ctrl and then left click the mouse on each worksheet tab. To select all worksheets at once, right click on one of the sheet, and then click on Select All Sheets One practical use of selecting multiple worksheets is to print sel...
More

Excel VBA sort worksheet tab

This Excel VBA tutorial explains how to sort worksheet tab in alphabetic order (ascending order or descending order). You may also want to read: Excel Range Sort Method to sort data Use VBA Excel Function to sort data in ascending order Access Excel assign number for custom sorting sequence Sort worksheet tab by name When you have a lot of worksheets in a workbook, you may want to sort them in alphabetic order for easy searching. As a behavior of Excel sorting, when an arr...
More