You are here: Home / Archives for Visual Basic

How to protect VBA code in Excel 2010

September 28, 2012 By

If you have an EXCEL workbook that includes some VBA code, and you want to hide that code, either to protect your intellectual property or simply to prevent others from messing up your code accidently , then follow these steps:

1. In the Developer tab, Code group, click Visual Basic. This will switch you to VBA development environment.

2. Create your project with the required code.

3. In the VBA development environment, click on the Tools menu and select VBAProject Properties.

The Project Properties window will open.

4. Select the Protection tab.

5. Select the checkbox Lock project for viewing.

6. Enter  Password and Confirm password.

7. Click OK.

8. Save the workbook and then close it so that the password protection takes effect. Next time you open the workbook and try to view the code you will be prompted for the password.

 

Filed Under: Excel 2007, Excel 2010, Security and Protection, Visual Basic Tagged With: , , ,

How to use colors in Excel 2010 VBA code

September 20, 2012 By

If you want to use colors in your VBA macros e.g. to modify cell background color or to change the font color, then you have two options on how to specify the colors:

1. By choosing from one of the preset EXCEL colors (56 colors). You do this by specifying a color index.

2. You can set your own colors by using RGB (Red, Green and Blue) values.

Examples of using the preset colorindex property.
1. Set background color:

Worksheets(“Calendar”).Range(“A1:M1”).Interior.ColorIndex = 34

2. Set font color:

Worksheets(“Calendar”).Range(“A1:M1”).Font.ColorIndex = 34

3. The following VBA code demonstrate how to use colorindex property. It is output is the complete list of the color index palette. The image below is the output from that code.

If you want to try it, name a sheet in your workbook : ”ColorIndex” and then paste the code in a new module and run it.

Sub ShowColorIndex()

Dim i As Integer, j As Integer

For i = 1 To 4

For j = 1 To 14

Worksheets(“ColorIndex”).Cells(j, (i – 1) * 2 + 1).Value = (i – 1) * 14 + j

Worksheets(“ColorIndex”).Cells(j, i * 2).Interior.ColorIndex = (i – 1) * 14 + j

Next j

Next i

End Sub

Examples of using the color property:

Set  background:

Worksheets(“Calendar”).Range(“A1:M1”).Interior.Color = RGB(218,225,130)

Set font  color:

Worksheets(“Calendar”).Range(“A1:M1”).Font.Color = RGB(218,225,130)

How do you figure out the right RGB color combination? Well you can experiment with the color palette. The following post will show you how.
How to create a custom font color or fill color in Excel 2010

Filed Under: Customization, Excel 2007, Excel 2010, Formatting, Visual Basic Tagged With: , , , ,

Excel 2010: How to access worksheet built-in functions in VBA

September 6, 2012 By

VBA has got it is own set of functions which you can use in your code. You can extend this set of functions by using EXCEL built-in functions from within VBA. These functions will have the same syntax and parameters as in EXCEL itself. Here are some examples:

1. Sum a range of cells and put the result in VBA variable “Total”.

Total = WorksheetFunction.Sum(Range(“A1:A10″))

2. Count a range of cells and put the result in VBA variable “Count”.

Count = WorksheetFunction.Count(Range(“A1:A10″))

3. Find the maximum value within a range of cells and put the result in VBA variable “Max”.

Max = WorksheetFunction.Max(Range(“A1:A10″))

4. Use the Vlookup function to look for a value and return its corresponding match.

LookupVal = WorksheetFunction.VLookup(“G”, Range(“A1:B10″), 2, False)

Note:

Vlookup will return a run time error if the lookup value is not found. To avoid this, enter the following statement somewhere before the VLookup statement:

On Error Resume Next

This way you will not have an error message and no value will be returned from the function.

One more example. The code shown below checks to see if cell “B15″ is numeric. If it is numeric, it will convert its value to hex and put the result in “C15″. If not it will give an error message.

Set myRange = Worksheets(“Sheet1″).Range(“B15″)
If WorksheetFunction.IsNumber(myRange) Then
c = WorksheetFunction.Dec2Hex(myRange)
Worksheets(“Sheet1″).Range(“C15″).Value = c
Else
MsgBox “Invalid decimal value”
End If

Filed Under: Excel 2007, Excel 2010, Functions, Visual Basic Tagged With: , , ,

Excel 2010 VBA objects: Handling cells and ranges

August 30, 2012 By

In previous posts of this series, we covered:

  1. Introduction.
  2. Handling workbooks.
  3. Handling worksheets.

In this post we will talk about handling cells and ranges.

A range can be a single cell or a group of cells. E.g. “A1” is a range consisting of a single cell ”A1”. “A1:B10” is a range consisting of 20 cells: A1-A10 and B1-B10.

You can manipulate ranges by retrieving values, modifying values, formulas, formats etc. You can refer to range by using “A1” style names or by index number, E.g. :

Worksheets(“Sheet1″).Range(“A5″).Value = 7
Worksheets(“Sheet1″).Cells(1, 8).Value = 59   ‘Row 1, column 8 or cell (“H1”)

Examples of range properties:

1. The following loop will clear an area equal to 6 rows by 7 columns:

For Row = 1 To 6
For Col = 1 To 7
ActiveSheet.Cells(Row, Col) = ” “
Next Col
Next Row

2. Update a cell with a formula:

Worksheets(“Sheet1″).Range(“A11″).Formula = “=SUM(A1:A10)”

3. Change cell color

You can change cell colors in one of two ways:

By using one of the preset 56 colors as follows:

ActiveCell.Interior.ColorIndex = 48

or you can use the standard RGB colors as follows:

Range(“A1:F1″).Interior.Color = RGB(120, 200, 135)

Filed Under: Excel 2007, Excel 2010, Visual Basic Tagged With: , , ,

Excel 2010 VBA objects: Handling worksheets

August 26, 2012 By

In a previous post we discussed how to handle workbooks through VBA code. In this post we will go one step furher to deal with worksheets.

To add a new worksheet:

ActiveWorkbook.Worksheets.Add

To activate a worksheet:

ActiveWorkbook.Worksheets(1).Activate   ‘specify worksheet by index number
ActiveWorkbook.Worksheets(“sheet12″).Activate   ‘specify worksheet by name

To delete a worksheet:

ActiveWorkbook.Worksheets(“sheet8″).Delete    ‘delete worksheet by name
ActiveWorkbook.ActiveSheet.Delete    ‘delete the active worksheet

To get the name of the active worksheet

ActiveWorkbook.ActiveSheet.Name

To print preview a worksheet

ActiveWorkbook.Worksheets(“sheet5″).PrintPreview

In the following example the ListWorksheets macro will loop through all the worksheets in the active workbook to retrieve their names and then, at the end of the loop, list all the names in a message box.

Sub ListWorksheets()
For i = 1 To ActiveWorkbook.Worksheets.Count
strNames = strNames & Chr(13) & ActiveWorkbook.Worksheets(i).Name
Next i
MsgBox strNames
End Sub

The next example, which you can find in the download page of this site, prints the hidden sheets in a workbook:

Sub PrintHiddenSheets()
‘*********************************************************
‘ Print only hidden sheets in the active workbook *
‘*********************************************************
Dim wSheet As Worksheet
Dim CurStat As VariantFor Each wSheet In ActiveWorkbook.Worksheets
If Not wSheet.Visible Then
CurStat = wSheet.Visible
wSheet.Visible = xlSheetVisible
wSheet.PrintOut
wSheet.Visible = CurStat
End If
Next
End Sub

Filed Under: Excel 2007, Excel 2010, Visual Basic Tagged With: , , , ,

Excel 2010 VBA objects: Handling workbooks

August 12, 2012 By

In a previous post I gave an introduction about VBA objects. In this post I will zoom a little bit into objects related to the workbooks. Remember that these posts are not intended to teach programming, but will rather give some highlights on the subject.

Let us start with an example:

Suppose we want to open a workbook called “Test.xlsx”, Get the workbook name with full path, find out how many worksheets are in the workbook and then close the workbook. We will use the following VBA code:

SUB ProcessWorkBook
Workbooks.Open Filename:=”Test.xlsx”   ‘open the  workbook
‘Once the workbook is open, we will use the ActiveWorkbook property of the Application object
strPath = ActiveWorkbook.FullName  ‘Get workbook name with full path
intSheets= ActiveWorkbook.Worksheets.Count  ‘get the count of worksheets in this workbook
Workbooks(“Test.xlsx”).Close  ‘close the  workbook
Msgbox strPath     ‘ Display name and path
Msgbox intSheets  ‘Display No. of worksheets
END SUB

Other methods that you can use:

‘To open a workbook as read only:
Workbooks.Open Filename:=”Test.xlsx”, ReadOnly:=True
‘To create a new workbook
Workbooks.Add

Once the workbook is open, then you can access the ActiveWorkbook property. There are a number of variables that you can retrieve e.g.

ActiveWorkbook.Name ‘Get workbook name
ActiveWorkbook.Path ‘Get workbook full path

You can also perform the following actions:

ActiveWorkbook.PrintPreview ‘print preview the workbook
ActiveWorkbook.SaveAs ‘save workbook with different name
ActiveWorkbook.Close ‘close the workbook

 

Filed Under: Excel 2007, Excel 2010, Visual Basic Tagged With: , , , ,

Excel 2010 VBA objects : Introduction

August 12, 2012 By

Excel programming is handled through Visual Basic for Applications (VBA). VBA uses objects to get information from Excel or to perform certain actions. Simply put objects are the building blocks of the Excel application.

Examples of objects:

  • Application
  • Workbook.
  • Worksheet.
  • Range (one cell or more).
  • Chart.

You get information from an object (or modify object characteristics) by using a specific object property.

Examples of properties:

Workbook.Name

Name is a property of the Workbook object.

Range.ColumnWidth

ColumnWidth is a property of the Range object.

You perform actions on an object by using a specific object method.

Examples of methods:

ActiveWorkbook.SaveAs

SaveAs is a method of the active workbook

Range(“A1”).select

select is a method of the Range object

Other important components of VBA are events. The event is some sort of a trigger which tells you that an action has occurred on the object. This will let the programmer to execute VBA code/procedure when a particular event occurs.

Examples of events:

Workbook_Open

Triggered when a workbook is opened.

Worksheet_SelectionChange

Triggered when a user has selected a different range of cells.

Filed Under: Excel 2007, Excel 2010, Visual Basic Tagged With: , ,

How to write a VBA macro in Excel 2010

July 1, 2012 By

If you know VBA (Visual Basic for Applications) programming then you can create your own VBA macros and run them from within Excel.

To write a VBA macro, follow these steps:

1. On the Excel Ribbon click the Developer tab. If it is not in the ribbon, click here to see how to show the Developer tab.

2. On the Code group click Visual Basic.

3. You will be switched to Visual Basic window. It has two panes. The left is the Project pane, which lists your workbook, worksheets and any VBA modules. The right is the code editor.

4. On the VBA toolbar click Insert and select Module.

5. The module name will be listed in the project pane, and you will be switched to the right pane where you can enter your code. The VBA window will finally look like this:

7. When you are done with your code, on the File menu click Close and return to Microsoft Excel.

8. Your new macro is now ready to be run.

9. To run your macro:

  • On the Developer tab, Code group click Macros. A list of available macros will be displayed.

  • Select your macro and click Run.

 

 

Filed Under: Excel 2010, Visual Basic Tagged With: ,

How to create a user-defined function in Excel 2007 or Excel 2010

April 23, 2011 By

If you know Visual Basic programming then you can create your own user-defined functions in Excel 2007 or Excel 2010. Follow the steps below:

1.      Click on Excel Developer tab. If you can’t see the developer click Here.


2. On the Code group click Visual Basic.

3. You will be switched to Visual Basic Editor Menu.

4. On the Insert menu (Visual Basic Editor) select Module.

5. This will open the code window.

6. Type the Visual Basic code for your function.

7. On the File menu click Close and return to Microsoft Excel.

8. Your new function should now be ready for use like any other Excel function.

Filed Under: Excel 2007, Excel 2010, Functions, Visual Basic Tagged With: , , ,

How to record a macro in Excel 2007

June 28, 2009 By

If you have a task that you perform repeatedly, then you can record all the events to achieve that task (keystrokes, mouse clicks … etc.) in a macro.

To record a macro in Excel 2007 follow these steps.

1. Click on the Developer tab. If the Developer tab is not visible, then check this post on how to show the Developer tab:

2. In the Code group Click Record Macro.

xl7macros1

3. The Record Macro window will open.

xl7macros2

4. Give a name to your macro.

5. Specify a shortcut key(optional).

6. Specify where you want to save the macro.

7. Click OK.

8. Now start performing the task which you want to record. When you are done click Stop Recording.

xl7macros3

9. Your macro is now created and is ready to be used. To run the macro:

  1. Click Macros.

    2. xl7macros4

  2. The Macros window will open.

    3. xl7macros5

  3. Select the macro then click run.

10. You can also run the macro by simply clicking the shortcut key combination.

Filed Under: Excel 2007, Visual Basic Tagged With: , , ,
« Older Posts