VBA

Do you want to learn Excel VBA Programming

Yes, I really want to learn
100% (2 votes)
No, I don't need it
0% (0 votes)
May be
0% (0 votes)
Total votes: 2

Application Volatile

In our earlier post we showed one example of User Defined Function or Custom function that one can write to perform operations that are not provided by in build functions in the excel. Though there is one limitation of User Defined Function that the formula will not automatically recalculate the values whenever the cells are updated.

Function to find Unique Items in a list

It happens many times when you are writing a Macro and you need to find the unique items out of the a range of items. Suppose you have an excel sheet that has the employee ids of you the people working in a company but the list has duplicate entries. Though there is a feature in excel where you can just display the unique name but to do the same using Macro you need to write a code.

Refresh all Pivots in the Excel workbook

As a part of my work I have to make many complex reports with so many Pivot tables to represent the data into various formats. On an average the reports that I work with has at least four to five pivot tables in a single workbook. Pivots work great but you have to manually refresh all the pivots whenever you update the data.

Change color of cell content using a shortcut

in

It is frustrating to format the report in excel. Most of the companies follows certain guidelines to present the report in a specified format like column headings should be in grey background color, the font size should be 8 etc. Changing the content color of the cell is done too often. To work faster you can use a shortcut to change the color of any cell or group of cells that you have selected.

Procedures in VBA

A procedure holds a group of VBA statements that accomplishes a desired Task. A procedure is a series of VBA statements that resides in a VBA module, which you access in the Visual Basic Editor. A module can hold any number of procedures. You have a number of ways to call, or execute, procedures. A procedure is executed from beginning to end (but it can also be ended prematurely).

Hello VBA World!

in

Ok so we have learned how to record a Macro and we discussed couple of examples where we recorded the macros. As we discussed in our earlier blog that for modifying our recorded Macros or to write our own Macro from beginning we need to use VBA Editor.

How to use VBA Editor

in

So you have recorded your first Macro in Excel VBA but most of the times you want to do something more that cannot be achieved with just recording and for customizing the Macro you need to Modify the VBA Code. VBA Editor is just for that, modifying and writing your own code.

Record a Macro to Merge/DeMerge cells in Excel

In our earlier posts we discussed how to record a Macro and we discussed one example to record a macro to AutoFit. Lets take one more example to create a Macro to Merge and De-Merge the selected cells quickly, which is otherwise a long procedure.

Record a Macro to AutoFit cells in Excel

AutoFit is something that makes the data in the Sheet more presentable and readable. This is one example where we can create a simple Macro to apply AutoFit property to the sheet.

How to Recording a Macro in Excel

The Easiest and the fastest way to learn Excel VBA Programming is by using the Recording Macro feature. This cool feature of Excel will record all the steps you perform on Excel sheet and generate a Code. You can later view the code and see how your actions are translated into VBA Code.

Now Never miss to mention the subject in the mail

It happens many time that you write a mail but forgot to mention the subject. I know it is embarrassing to send an important official mail without a subject. It has happened to me also when I used to do it a lot and I remember my Manager making strange faces at me.

Transpose Columns with corresponsing values

We all know that there is a feature in the excel sheet to transpose rows into columns or vice-versa. To transpose just select the row or column and paste-special with transpose checkbox selected but what if you want to do the transposing a complete table? There is no such feature in excel so for achieving two dimensional transposing we will write a macro.

Numeric Expressions

in

Numeric expressions are what I normally think of when I use the generic term "expression." Whether it's calculating gross margin, figuring out commissions, or determining the monthly payment on a loan, many expressions perform some kind of number crunching. You saw VBA's arithmetic operators earlier in this chapter. This section adds to that by giving you a quick look at VBA's built-in math and financial functions.

VBA's Math Functions

Understanding Operator Precedence

in

You'll often use simple expressions that contain just two values and a single operator. In practice, however, many expressions you use will have a number of values and operators. In these more complex expressions, the order in which the calculations are performed becomes crucial. For example, consider the expression 3+5^2. If you calculate from left to right, the answer you get is 64 (3+5 equals 8 and 8^2 equals 64). However, if you perform the exponentiation first and then the addition, the result is 28 (5^2 equals 25 and 3+25 equals 28).

Syndicate content

Suggested Book to learn VBA