Excel VBA refresh closed workbook

Posted on April 11, 2015May 29, 2017 by Wyman W

This Excel tutorial explains how to refresh a closed workbook. You may also want to read: Excel VBA refresh all Pivot Table or Pivot Cache Excel VBA AskToUpdateLinks Property Excel refresh closed workbook First of all, to refresh closed workbook involves opening the workbook, refresh and then close it, it is impossible to refresh closed workbook without opening it, but we can open a workbook by vba without seeing it physically opened. Second, ask yourself what kind of refresh you ...

Excel VBA AskToUpdateLinks Property

Posted on April 10, 2015 by Wyman W

This Excel tutorial explains how to enable and disable Application AskToUpdateLinks Property (Ask to update automatic links in Excel Options). You may also want to read Differences among Function, Sub, Method, Property Excel Options - Ask to Update automatic links When you link external data source outside your workbook, such as another workbook or Access database, you will be asked whether to update the data automatically when you open the workbook. This workbook contains links to one ...

Excel VBA Function sum colored Cell count colored Cell

Posted on April 10, 2015 by Wyman W

This Excel tutorial explains how to sum colored Cell and count colored Cell in Excel worksheet. Excel VBA Function sum colored Cell and count colored cell In my previous posts, I have explained how to use ColorIndex Property to find the first colored Cell. In this Post I will create a custom Function to count colored Cell of a Range and sum colored Cell To recap the previous posts, you can click on the followings. Excel VBA custom Function Find the first colored Cell value VBA Excel...

Access Excel Scripting Dictionary store unique array item

Posted on April 7, 2015February 26, 2017 by Wyman W

This Access Excel tutorial explains how to use Scripting.Dictionary in VBA to store unique array item. You may also want to read: Access Excel VBA Array Remove duplicates in text using Dictionary Access Excel VBA Scripting.Dictionary to store unique array item An associative array, map, symbol table, or dictionary is an abstract data type composed of a collection of (key, value) pairs, such that each possible key appears just once in the collection. Put it simply, you can avoid duplica...

Excel generate random letter A to Z using RANDBETWEEN

Posted on April 5, 2015March 20, 2016 by Wyman W

This Excel tutorial explains how to generate random letter A to Z / a to z or a mix using RANDBETWEEN Function. You may also want to read Assign unique random number using Excel Use VBA to generate non-duplicated random number Excel generate random letter A to Z using RANDBETWEEN In order to generate random letter, we have to make use of two Functions. - RANDBETWEEN - CHAR In the below sections, I will briefly go going through these two Functions. For details, click on the link...

Excel VBA Add Autofilter Cancel AutoFilter sorting in worksheet

Posted on March 30, 2015July 11, 2015 by Wyman W

This Excel tutorial explains how to use Excel VBA to add AutoFilter, cancel AutoFilter, add sorting in Worksheet AutoFilter. You may also want to read: Sort data using custom Function by Bubble Sort Sort data using custom Function by assigning number to Text Sort data using Range Sort Method Sort data using Table AutoFilter Excel VBA Add AutoFilter or Cancel AutoFilter Using VBA to add AutoFilter or cancel Auto Filter is very simple if you know the code, but you should note that...

Access Excel assign number for custom sorting sequence

Posted on March 26, 2015April 20, 2015 by Wyman W

This Access Excel tutorial explains how to assign number to Text for custom sorting sequence and how to use Excel Custom Lists. You may also want to read: Sort data using custom Function by Bubble Sort Sort data using Range Sort Method Sort data using Table AutoFilter Sort data using Worksheet AutoFilter Access Excel Assign number to Text for custom sorting sequence Suppose your company has departments IT, HR, Finance. Normally if you sort the sequence in ascending order, it wou...

Access Excel VBA group number with larger than smaller than (age group)

Posted on March 26, 2015December 4, 2015 by Wyman W

This Access Excel tutorial explains how to create custom Function to categorize /group number with larger than smaller than symbol, such as age group. Access Excel categorize / group number with larger than smaller than symbol Sometimes when you have a group of numbers, you may need to categorize / group them into different categories for analysis. For example, you may need to make age group for employees, such as <20 >=20 to <30 >=30 to <40 >=40 In order ...

Excel convert column letter to column number vise versa

Posted on March 26, 2015March 26, 2015 by Wyman W

This tutorial explains how to convert column number to column letter, and convert column letter to column number in Excel. Excel column letter to column number / column number to column letter conversion Column number refers to the column expressed in integer, while column letter refers to column expressed in alphabet. For example, for Cell D1, column number is 4, while column letter is D. Excel convert column number to column letter To convert column number to column letter, make use o...

Excel VBA Application Calculation Property

Posted on March 11, 2015March 12, 2015 by Wyman W

This tutorial explains Excel Application Calculation Property and difference among xlCalculationAutomatic, xlCalculationManual, xlCalculationsemiautomatic Excel VBA Application Calculation Property In Excel worksheet, there are three options under Workbook Calculation. The options in Workbook Calculation uses Calculation Property in Excel VBA, which means if you execute the corresponding VBA code, the Excel options change accordingly. Excel Option Description VBA Automatic...