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
Excel VBA Auto-filter Based on Values of Another Cells
I and was trying to filter the data based a couple of cell values. Finally, I figured it out. Below, please see my sample. Filler Sheet “Trans” based on the values of C3, C5 and C6 from Sheet ” Reports”.
Make a Specific Column or Range to Absolute Values (Excel VBA)
See below for a sample to change all value in Column I to M to their absolute Values
How to Forces a Full Calculation All Open Workbooks in VBA (“F9”)
Forces a full calculation of the data in all open workbooks. Syntax Application.CalculateFull Also, see below for similar methods Calculate calculates only new, changed and volatile formulas. CalculateFull calculates all formulas regardless. As a general rule, this will therefore be slower. CalculateFullRebuild calculates all formulas and rebuilds the entire calculation dependency tree. This will be the slowest of all. Examples Read More
How to Create a Drop-Down List in Excel
You can help people work more efficiently in worksheets by using drop-down lists in cells. Drop-downs allow people to pick an item from a list that you create. In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you’ll have your list items in an Excel table. If you don’t, Read More