Computers, Howto

Excel Howtos

Howto: Call one Excel Macro from another

If you have a spreadsheet that has multiple macros, it is possible to invoke one from the other. You need to use the statement “call [name of macro to be called]” each time you want a particular macro to be called.

eg: Body of macro0 could contain following code to invoke other macros

if x=1
call macro1
else if x=2
call macro2
else
call macro3
endif

*Edited later*

This applies only to Macros and it does not seem to be possible to invoke macros using the call function from user defined functions.

Howto: Add buttons, checkboxes, comboboxes in Excel2007

Office 2003 had “Forms toolbar” that contained all the objects like buttons, checkboxes, comboboxes etc.

In Office 2007, this functionality has been moved to the Developer tab. You have to manually enable the Developer tab in the ribbon bar as follows.

  • Click on the oversized Office logo button in the upper left corner
  • Click on the Excel Options button to display the Excel Options dialog
  • Click on Popular in the list in the left hand side of the dialog.
  • Check ‘Show Developer tab in the Ribbon’
  • Click the OK button.

In the Developer tab now should appear in the Ribbon bar. If you open the developer tab, you can find the Controls section which has a drop-down menu labeled Insert where the form and ActiveX controls are placed.

Howto: Change values of individual cells through macros

To store number 3 in cell F3, use the following command in the body of the macro:

Range(“F3”).Value = 3

This would place the value 3 into cell F3 of the active worksheet of the active workbook.

To place the value in another workbook use the following command.

Workbooks(“BookName”).Worksheets(“SheetName”).Range(“F3”).Value = 3

The .Value property can also read a value from a cell.

x = Range(“F3”).Value

Whether .Value property is used to read a value or assign a value depends on which side of the equal sign it is placed.

However, this applies only to Macros and not user defined functions. Though  it is possible to read a value to a user defined function and it does not seem to be possible to assign a value to a cell.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s