When writing VBA code that does calculations based on worksheet data and then writes data back to the worksheet you might be waiting longer for a result than needed. Even more so if there are formulas in that worksheet. There are or course more ways to optimize the calculation time. Obviously the alghoritm of your […]
Sometimes you want to retrieve information from the internet for use in an Office Application like Excel. For example, you might want to fetch the latest currency rates or the latest news topics from a website. One way to do that is by using the XMLHTTP object. Generally this is considered to be faster than […]
What are macros? Working with the spreadsheets can be headache at times, especially when the same data are to be entered in the same manner time and again. Hence, Microsoft has come up with the concept of “macros”. The macros are commands that record the actions of the “mouse” clicks as well as the keystrokes. […]
Microsoft Word does not have an automatic feature to count the number of rows in a table; however, you can create a simple macro to return these values Method 1: Use the Information property to return the number of rows Use the Microsoft Visual Basic for Applications Selection.Information property to return the number of rows […]
Fetch Email Addresses – Quick Install & Test Code Open MS Outlook and Select any mail in Inbox or any other Folder Open new Excel Workbook, Alt+F11, Copy Paste the code in this Article In Excel VB Editor go to Tools->References->Add Microsoft Outlook nn.n Object Library Press F5. All Email-Ids (“To”) in the mail you […]
When you are processing operations in Excel, sometimes, you may need to change the negative numbers to the positive numbers or vice versa. Are there any quick tricks you can apply for changing negative numbers to positive? This article will introduce you the following tricks for converting all negative numbers to positive or vice versa easily.
This function will allow you to compare one file to another. Copy code below and paste directly into your VB project. Sub CompareTextFiles() ‘********************************************************** ‘PURPOSE: Check to see if two files are identical ‘File1 and File2 = FullPaths of files to compare ‘will compare complete content of the file, including length of the document (Bit […]
Today I found some useful code I wanted to share with our readers. A macro to automatically add an IfError() function to every formula on every sheet in a selected workbook, assuming there was not already an IfError function used. Remember that macros have no Undo command. Once you run this VBA macro the only undo is to close […]
VBA Macro for Conditional Formatting Private Sub Worksheet_Change (ByVal Target As Range) Set MyPlage = Range(“C3:I11,C13:I34”) For Each Cell in MyPlage If Cell.Value Like “A*” Then Cell.Interior.ColorIndex = 38 ElseIf Cell.Value Like “B*” Then Cell.Interior.ColorIndex = 35 ElseIf Cell.Value Like “C*” Then Cell.Interior.ColorIndex = 34 ElseIf Cell.Value Like “D*” Then Cell.Interior.ColorIndex = 40 Else Cell.Interior.ColorIndex […]
VBA Macro Code to Plot A “XYScatter Chart” on Fly Sub chartData() On Error Goto ErrHandler Dim xRow As Long Dim wsName As String Dim ws As Worksheet Set ws = ActiveSheet wsName = ws.Name xRow = Cells(Rows.Count, “A”).End(xlUp).Row ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlXYScatterSmooth ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).Name = “='” & wsName & “‘!$A$17” ActiveChart.SeriesCollection(1).XValues = “='” & […]