Excel Howtos

Posted on 03. Oct, 2009 by Krishnan in Computers, Howto

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.

Public  Function MyFunc(MacroName As String)
     Dim Msg, Style, Title, Help, Ctxt, Response, MyString
\r\nMsg = \"Do you want to continue ?\"    ' Define message.
\r\nStyle = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
\r\nTitle = \"MsgBox Demonstration\"    ' Define title.
\r\nHelp = \"DEMO.HLP\"    ' Define Help file.
\r\nCtxt = 1000    ' Define topic
\r\n        ' context.
\r\n        ' Display message.
\r\nResponse = MsgBox(Msg, Style, Title, Help, Ctxt)
\r\nIf Response = vbYes Then    ' User chose Yes.
\r\n    MyString = \"Yes\"    ' Perform some action.
\r\nElse    ' User chose No.
\r\n    MyString = \"No\"    ' Perform some action.
\r\nEnd If

“, “style=\”background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;\”");” onmouseout=”GAL_hidepopup();”>MsgBox “Inside MyFunc. Passed argument ” & MacroName
Application.Run MacroName
End Function