Resources on VBA Excel @ ExcelOptimize.Com - Part 2
Currently Browsing: VBA Excel
Posted by Andrian in VBA Excel on 03 20th, 2009 | No Comments

Applying chart formatting

Applying chart formatting
This example applies several different types of formatting to the active chart. I created this macro by recording my actions as I formatted a chart. Then I cleaned up the recorded code by removing irrelevant lines. Sub ChartMods() ActiveChart.Type = xlArea ActiveChart.ChartArea.Font.Name = “Calibri” ActiveChart.ChartArea.Font.FontStyle...
Read More
Posted by Andrian in VBA Excel on 03 19th, 2009 | No Comments

Modifying chart properties

The following example changes the Legend font for all charts on the active sheet. It uses a For-Next loop to process all ChartObject objects: Sub LegendMod()Dim cht As ChartObjectFor Each cht In ActiveSheet.ChartObjectsWith cht.Chart.Legend.Font.Name = “Calibri”.FontStyle = “Bold”.Size = 12End...
Read More
Posted by Andrian in VBA Excel on 03 18th, 2009 | No Comments

Looping through the ChartObjects collection

This example changes the chart type of every embedded chart on the active sheet. The procedure uses a For-Next loop to cycle through each object in the ChartObjects collection, access the Chart object in each, and change its Type property. Sub ChartType()Dim cht As ChartObjectFor Each cht In ActiveSheet.ChartObjectscht.Chart.Type...
Read More
Posted by Andrian in VBA Excel on 03 17th, 2009 | No Comments

Modifying the chart type

Here’s a confusing statement for you: A ChartObject object acts as a container for a Chart object.To modify a chart with VBA, you don’t have to activate the chart. Rather, the Chart method can return the chart contained in the ChartObject. Are you thoroughly confused yet? The following two procedures...
Read More
Posted by Andrian in VBA Excel on 03 16th, 2009 | No Comments

Changing non-Boolean settings

Use a Select Case structure for non-Boolean settings. This example toggles the calculation mode between manual and automatic and displays a message indicating the current mode: Sub ToggleCalcMode()Select Case Application.CalculationCase xlManualApplication.Calculation =xlCalculationAutomaticMsgBox “Automatic...
Read More
Posted by Andrian in VBA Excel on 03 15th, 2009 | No Comments

Changing Boolean settings

Like a light switch, a Boolean setting is either on or off. For example, you might want to create a macro that turns the worksheet page break display on and off. After you print or preview a worksheet, Excel displays dashed lines to indicate the page breaks. Some people (author included) find this annoying.Unfortunately,...
Read More
Posted by Andrian in VBA Excel on 03 14th, 2009 | No Comments

Identifying a multiple selection

As you know, Excel allows multiple selections by pressing Ctrl while choosing objects or ranges. This can cause problems with some macros. For example, you can’t copy a multiple selection that consists of nonadjacent cells. (Try it if you don’t believe me.)The following macro demonstrates how to...
Read More
Posted by Andrian in VBA Excel on 03 13th, 2009 | No Comments

Determining the selection type

If you design your macro to work with a range selection, the macro must be able to determine whether a range is actually selected. If something other than a range is selected (such as a chart or a shape), the macro will probably bomb. The following procedure uses the VBA TypeName function to identify...
Read More
Posted by Andrian in VBA Excel on 03 12th, 2009 | No Comments

Prompting for a cell value

As shown in Figure 14-2, you can use VBA’s InputBox function to get a value from the user. Then you can insert that value into a cell. The following procedure demonstrates how to ask the user for a value and place the value in cell A1 of the active worksheet, using only one statement: Sub GetValue() Range(“A1”).Value...
Read More
Posted by Andrian in VBA Excel on 03 11th, 2009 | No Comments

Looping through a range efficiently

Many macros perform an operation on each cell in a range, or they might perform selected actions based on each cell’s content. These macros usually include a For-Next loop that processes each cell in the range.The following example demonstrates how to loop through a range of cells. In this case, the...
Read More
 Page 2 of 3 « 1  2  3 »