Following are the STEPS ; We take advantage of Protection Structure in Excel. This is where the magic begins, follow carefully: 1. Stay in the Excel File and go to Review TabNote: (Excel and not Power Query) 2. Choose Protect Workbook 3. Ensure the Structure box is ticked 4. Provide a Password (optional) 5. Confirm by re-entering ( if password is Read More
Category: Microsoft Office
How to Refresh Data Connections in Excel VBA
Quick reference: The first one will “refresh” only one file. The second will “refresh” all open excel files. ThisWorkbook.RefreshAllActiveWorkbook.RefreshAll With the release of integrated stock pricing in Microsoft Excel, it was unfortunate there wasn’t any sort of setting that allowed for automatic updating of the stock data while the file was open. Even worse, there Read More
How to Count Unique Values in Excel – With COUNTIF
This example shows you how to create an array formula that counts unique values. 1. We use the COUNTIF function. For example, to count the number of 5’s, use the following function. 2. To count the unique values (don’t be overwhelmed), we add the SUM function, 1/, and replace 5 with A1:A6. 3. Finish by pressing CTRL Read More
How to Use Excel VBA Autofilter: A Complete Guide with Examples
A lot of Excel functionalities are also available to be used in VBA – and the Autofilter method is one such functionality. If you have a dataset and you want to filter it using a criterion, you can easily do it using the Filter option in the Data ribbon. And if you want a more advanced version of Read More
How to Get Current Working Directory Using VBA
Use Application.ActiveWorkbook.Path for just the path itself (without the workbook name) or Application.ActiveWorkbook.FullName for the path with the workbook name. CurDir() is also very useful in some circumstands For example, When you open an Excel document D:\db\tmp\test1.xlsm: CurDir() returns C:\Users\[username]\Documents ActiveWorkbook.Path returns D:\db\tmp So CurDir() has a system default and can be changed. ActiveWorkbook.Path does not change for the same saved Workbook.
How to Check if a Worksheet Exists in Excel VBA
There is a couple of ways to do this. Solutions 1, use a for – next loop to check worksheet one by one. Function WorksheetExists(ByVal WorksheetName As String) As BooleanDim Sht As WorksheetFor Each Sht In ThisWorkbook.WorksheetsIf Application.Proper(Sht.Name) = Application.Proper(WorksheetName) ThenWorksheetExists = TrueExit FunctionEnd IfNext ShtWorksheetExists = FalseEnd Function Solution 2, Setting the non-existent sheet Read More