Excel VBA find all Cells contain Named Range

This Excel VBA tutorial explains how to find all Cells contain Named Range using Macro. You may also want to read: Find all external links and broken links in workbook Replace Named Range with formula Find all Cells contain Named Range In Excel spreadsheet, we can define Named Range under Name Manager (Formulas > Name Manager) We can refer to Name Manager to see the formula the Named Range is referring to, but it does not tell which Cells are using the Named Range. Therefore...
More

Excel VBA Named Range Properties

This Excel VBA tutorial explains some common Properties of Name Object (Named Range), and demonstrate how to replace named Range with formula. You may also want to read: Excel find all external links and broken links in workbook Excel VBA Named Range Properties In Excel spreadsheet, we can define Named Range under Name Manager (Formulas > Name Manager) In the below screenshot, I have defined a Named Range called testNameRng, which refers to Cell A1 in Worksheet1. Currently A1 conta...
More

Excel extract text in brackets

This Excel tutorial explains how to extract text in brackets or in defined text in Excel spreadsheet and VBA. Extract text in brackets in Excel spreadsheet In the previous post, I demonstrated how to remove text in brackets or in any defined text, in this post I will explain how to extract the text in brackets or in any defined text. In Excel spreadsheet, you can extract any substring using Mid Function. To recap the syntax of Mid Function MID( text, start_position, number_of_characte...
More

Excel VBA select multiple files in dialog

This Excel VBA tutorial explains how to select multiple files in dialog using Application.GetOpenFileName. Excel VBA select file in dialog In many applications, you would see a "browse" button where you can select files for the application to process. In Excel VBA, you can create a Macro to open the browse dialog. After you select the target file, the file path is stored in a VBA Array, then you can loop through the array to get items, and then do whatever you like with the file. The...
More

Access Excel capitalize first letter in a sentence

This Access Excel tutorial explains how to capitalize first letter in a sentence using VBA custom Function. Capitalize first letter in a sentence In Excel spreadsheet, there is a Function called Proper, which converts all the first letter of each word to capital letter. Example  Using Proper Function he is a boy. she is a girl. He Is A Boy. She Is A Girl. he is a boy. his name is John. mary is a girl. He Is A Boy. His Name Is John. Mary Is A Girl. Mr.  smith is happy ...
More

Access VBA change Report Record Source

This Access tutorial explains how to change Access Report Record Source using VBA in order to modify Report criteria automatically. Change Report Record Source When you create an Access Report, you can specify the data source of the report in Report Record Source, which can be a Query or Table. Type the Table / Query name in the Record Source as below.   To add criteria to the Report, there are three ways Method 1 - Write SQL Type the full SQL statement directly in Record S...
More

Access Excel remove text in brackets

This Access Excel VBA tutorial explains how to remove text in brackets using custom Function. You may also want to read: Change text color in brackets Extract text in brackets Access Excel remove text in brackets I was inspired to create this post because the HR system I am using (Workday) is very annoying, some data fields are actually a combination of two different fields, the additional data field is displayed in brackets in the prefix. For example,  an employee has a English name ...
More

Excel VBA change partial text color in Cell

This Excel VBA tutorial explains how to change partial text color in a Cell using Macro. You may also want to read: Access Excel remove text in brackets Change partial text color in spreadsheet In Excel spreadsheet, you can change part of the text in a Cell by highlighting the text and then change the font color. Suppose you want to change the text World in Cell A1 to red color. First, highlight the text.   Then select red color in the font color option. Change p...
More

Access delete table records

This Access tutorial explains how to delete table records and delete records that exist in another table in Microsoft Access. Access delete table records You can always modify or delete table data manually by viewing the table and then press the delete button in keyboard. With Query, you can delete specific rows of record that meet the criteria. Note that you may delete data in an Access table, but you cannot delete data in linked table. For example, if your Access table is linked to Excel spr...
More

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