Excel VBA delete worksheet based on worksheet name

Posted on December 18, 2014December 25, 2014 by Wyman W

Solution to delete worksheet based on worksheet name I am providing the solution for the "Deluxe-Plus" version in the below code To determine which worksheet is to delete, create an array called delWS(), and then loop through all the worksheet name to find the worksheet name that contains "DELETE" or "OLD". If (InStr(WS.Name, "DELETE") <> 0 Or InStr(WS.Name, "OLD") <> 0) And InStr(WS.Name, "KEEP") = 0 Instr function is to check whether the worksheet name contains the key w...

Excel VBA change cell range to array

Posted on December 17, 2014October 24, 2016 by Wyman W

Solution to change cell range to array In the below solution, I have made a Sub to define array (defineArray), a Sub to delete worksheet(delWS), a Sub to move worksheet(moveWS) First, I define an array at Module level, so that all Sub within the Module can access the array. In Sub defineArray(), I put all the items from Range A4 to the last row of column A into wsArray() In Sub delWS(), I put "On Error Resume Next" to prevent error message when Macro tries to delete a wor...

Excel VBA Check cell value and cell value above

Posted on December 17, 2014March 4, 2015 by Wyman W

This tutorial provides a solution to check the Excel cell value and the cell value above in Excel worksheet using VBA. Check cell value and cell value above This was originally a question posted in Microsoft Community. I extract it to illustrate an example how to check Excel cell value and cell value above. Question Given a range of 12 cells in a row filled with only 0's and 1's... Would anyone know a way to return a true or false in a cell if looking in all the above cells, a 0 appeare...

Vertical line in Access Report using Line Method

Posted on December 11, 2014March 4, 2015 by Wyman W

This tutorial explains how to add vertical line in Access Report using VBA Line function, while the line tool fails to draw a continuous vertical line. Issue with using Line tool to add vertical line in Access Report Vertical line in Access Report is one of the hottest topic in this website. In Access Report, it is possible draw a vertical straight line using "Line" tool under "Design". However, the line is not continuous across records. Lets take a look at what I mean the line is n...

Excel DATEDIF Function to calculate date difference

Posted on December 8, 2014July 7, 2017 by Wyman W

This tutorial explains how to use Excel DATEDIF Function to calculate date difference (difference between two dates) in complete year, month or day. You may also want to read: Access Excel VBA day month year difference between two dates Excel DATEDIF Function DATEDIF function stands for "Date Difference". It calculates the difference between two dates in complete year, month or day. Note that DATEDIF Function returns the "complete" value, for example, if the year difference between two d...

Excel VBA For Loop / For…Next

Posted on December 7, 2014September 19, 2017 by Wyman W

This tutorial explains how to use For Loop (For...Next Loop) to perform iteration in Excel VBA. What is Loop? Loop is repeating a set of actions. When you listen to a song repeatedly, it is said you are looping the song. In VBA, there are different kinds of loop methods, they mainly differ in the way that how the loop is triggered and ended. What is For Loop? (For...Next Loop) For Loop (For...Next Loop) is the most frequently used loop, using For to begin loop, Next to end loop, Exit Fo...

Change font color and format in Visual Basics Editor VBE

Posted on December 6, 2014March 2, 2015 by Wyman W

This tutorial explains how to change font color and formats in Visual Basics Editor (VBE) to make your VBA code more readable. This article is suitable for Excel and Access. Change the VBE font color and format, why? Depending on the color and brightness setting of your monitor, the default font color in VBE (Visual Basic Editor) is not always easy to read. It is possible to change the VBE font color and format, for different types of codes, such as comment, syntax error, identifier, etc. ...

Custom VBA Excel Access Networkdays Function

Posted on December 4, 2014March 7, 2015 by Wyman W

This tutorial explains how to create a custom Excel Access Networkdays Function to simulate Excel Networkdays Function, exclude holiday and weekend Why do we need Access Networkdays Function? As of Access 2013 version, there is no Access Networkdays Function available, the function is only available in Excel all versions. In Excel , Networkdays Function is used to calculate the number of "net working days" between two days, excluding Saturday and Sunday, and include both start date and end ...

Run Excel Macro from Access VBA

Posted on December 4, 2014September 16, 2017 by Wyman W

This Access tutorial explains how to run Excel Macro from Access VBA, either write Excel Macro directly in Excel or you can write Excel Macro in Access. You may also want to read: Run Access Macro using RunCode Run Excel Macro from Access VBA One of the uses to run Excel Macro from Access is when you use VBA to export tons of Access reports to Excel. By default, exporting Access report as Excel gives you no formatting but plain text. However, it would be convenient if you can export th...