Holy Macro! Books publishes entertaining books for people who use Microsoft Office. 'Call MyFunction by value using the active cell, E6.
Set rng = Application.InputBox("Range:", Type:=8) 'Use the InputBox dialog to set the range for MyFunction, with some simple error handling. The value is stored in the active cell directly, instead of being returned by the UDF. Value Selection: This menu option is assigned the macro "Cbm_Value_Select", which uses an InputBox for the user select the range which the UDF "MyFunction" should calculate. MsgBox "Length, width and height are needed -" & _ĪctiveCell.Formula = "=MyFunction(" & rng.Address & ")" 'Check to see if the preceding offset has valid data, and if there are three values You must have values in the range B6:D6 and select cell E6 before clicking this menu option. Function MyFunction(rng As Range) As Doubleįormula Entry: This menu option is assigned the macro "Cbm_Active_Formula", which calls the UDF named "MyFunction" that multiplies the numbers in the preceding 3 cells, and stores value of the UDF in the active cell. The user-defined function (UDF) "MyFunction" multiplies three values in a range together and returns the result.
It provides four menu options, with a macro assigned to each option. The menu "MyFunction" is added when the workbook opens, and is deleted when the workbook closes. With Application.CommandBars("Worksheet Menu Bar") Private Sub Workbook_BeforeClose(Cancel As Boolean) The following code example sets up the custom menu when the workbook is opened, and deletes it when the workbook is closed. Sample code provided by: Holy Macro! Books, Holy Macro! It's 2,500 Excel VBA Examples The following code example shows how to create a custom menu with four menu options, each of which calls a macro.
0 kommentar(er)
