This Excel tutorial explains difference between Form Controls vs ActiveX Controls for Excel worksheet. Excel difference between Form Controls vs ActiveX Controls There are two different kinds of Controls in Excel spreadsheet, both are very similar but we need to know the difference between them. UserForm also has its Control but it is not discussed in this topic. In Excel 2013, to insert a Co...

This Access/Excel VBA tutorial shows the difference between Dim and Set keyword in variable declaration. You may also want to read: Excel VBA Option Explicit to force declaration of variables Difference between ByRef ByVal Function, Sub, Method, Property Differences VBA Difference between Dim and Set In VBA, we can always see the use of Dim and Set, but in some occasions we don't. ...

This Excel tutorial explains the use of Application Volatile Method to recalculate custom Function (User Defined Function). You may also want to read: Excel Workbook Calculation Automatic and Manual Excel VBA Application Volatile Method In Excel VBA, you can create your own Functions, we call it User Defined Function (UDF), usually I simply call it custom Function. Function is a Procedure ...

This Excel tutorial explains how to sort Excel data using Excel VBA Sort Method, and how to sort dynamic Range. 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 Table AutoFilter Sort data using Worksheet AutoFilter Excel Range Sort Method to sort data There are mainly three kinds o...

This Excel tutorial explains how to find all external links and broken links in workbook using Find and Replace, Relationship Diagram, Macro. You may also want to read Excel VBA Workbook LinkSources Method Excel VBA refresh closed workbook Excel find all external links and broken links in workbook There are several ways to find external links and broken links in workbook, some are easy...

This Excel tutorial explains how to use Workbook LinksSources Method in Excel VBA to find all external links. You may also want to read: How to refresh all external data of closed workbook Excel find all external links and broken links in workbook Excel VBA Workbook LinkSources Method LinkSources can be used to return an array of names of linked documents, editions, DDE or OLE servers. ...

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 wo...

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 whethe...

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 ...

This Excel tutorial explains how to use Workbooks Open Method to open a closed workbook and check if workbook is opened. Excel VBA Workbooks Open Method In worksheet automation, we may need to programmatically open another workbook to change data based on the active workbook. Workbooks Open Method is very straight forward to use, you just need to specify the file name to open, but there are many...