How do I write a macro that will selectively delete rows based on, say, negative values in column B?
	How do I write a macro to fill blank cells with zero values?
	How can I call the same macro from different controls and know which control started the macro?
	How do I pass arguments to macros assigned to buttons on dialogs? Enclosing the assignment in single-quotes works erratically.
	How do I pass arguments to macros assigned to worksheet objects?
	Can I pass arguments to macros assigned using Application.OnKey? How?
	Will my Excel 5 macros and applications run correctly in Excel 7?

How do I write a macro that will selectively delete rows based on, say, negative values in column B?
Keywords: Delete Rows Check Condition
Posted September 11, 1996

The following macro procedure deletes all rows in the active worksheet that contain a negative number in column B.

Sub DeleteNegativeRows()

    Dim rngArea As Range
    Dim lRows As Long
    Dim lCount As Long
    
    With ActiveSheet
        lRows = .UsedRange.Rows.Count
        Set rngArea = .Range(.Cells(1, 2), .Cells(lRows, 2))
    End With
    
    For lCount = lRows To 1 Step -1
        If rngArea.Cells(lCount, 1).Value < 0 Then
            rngArea.Cells(lCount, 1).EntireRow.Delete
        End If
    Next lCount
    
End Sub

How do I write a macro to fill blank cells with zero values?
Keywords: Zero Macro
Posted July 22, 1996

Here's one way:

Sub SetBlanksInSelectionToZero()
    If TypeName(Selection) <> "Range" Then
        MsgBox "No cell range selected."
        Exit Sub
    End If
    If Selection.Count = 1 Then
        Selection.Value = 0
    Else
        Selection.SpecialCells(xlCellTypeBlanks).Value = 0
    End If
End Sub

			

How can I call the same macro from different controls and know which control started the macro?
Keywords: Dialog Application.Caller Controls
Posted May 18, 1996

Application.Caller will return the name of the control that started the macro.

Generally this property is combined with a Select Case statement to take an action depending on which control was selected.

The following example assumes you have two buttons on your dialog box, an OK button named "Button 2" and a Cancel named "Button 3". Both buttons have been assigned to this routine:

Sub HandleButtons()
    ''' Use Application.Caller to figure out which button was pressed
    Select Case Application.Caller
        Case "Button 2"
            ''' default OK button
            MsgBox "The OK button was pressed."
        Case "Button 3"
            ''' default Cancel button
            MsgBox "The Cancel button was pressed."
        Case Else
            ''' good programming practice to trap for the unexpected
            MsgBox "Some other button or control called this routine."
    End Select
End Sub
			

You can use this same technique to allow multiple menus, toolbar buttons and/or on-sheet controls to call single routines.

Application.Caller has many other uses, check the on-line help for additional details.

How do I pass arguments to macros assigned to buttons on dialogs? Enclosing the assignment in single-quotes works erratically.
Keywords: Argument Macro Parameter Dialog Object Control
Posted May 15, 1996

Enclosing the entire macro assignment string in single quotes is a valid technique to assign macros to objects on worksheets and to toolbar buttons. You cannot do this with objects on dialogsheets. You will find it work while test running the dialog but when you show the same dialog via code and the event is triggered, instead of your macro running, you will receive an error message from Excel.

How do I pass arguments to macros assigned to worksheet objects?
Keywords: Argument Macro Parameter Object Button
Posted May 8, 1996

Enclose the entire assignment string in single quotes, and do not enclose the arguments in parentheses. Here are a few examples.

(See important note below examples)

To assign your macro ProcWithOneArg, passing the number 16 as the parameter, to a button (btnTest) on your worksheet (Sheet1), in the Tools->Assign Macro dialog, type

  'ProcWithOneArg 16'
			

To assign the macro via code, you could do something like 

  ''' Called from Auto_Open(). Assigns custom procedures to buttons
Sub AssignKeys()
   ThisWorkbook.Worksheets("Sheet1").Buttons("btnTest").OnAction = _
                "'ProcWithOneArg 16'"
      ''' Complex example: Assigns a function with two parameters, one being
      ''' a string and another, an expression containing built-in functions and
      ''' object properties.
      ''' Two double-quotes are needed whenever we have a string within a string
ThisWorkbook.Worksheets("Sheet1").Buttons("btnTest").OnAction = _
    "'FuncWithTwoArgs ""Some string"", ActiveSheet.Name & Chr$(47) & Now()'"
End Sub
Sub ProcWithOneArg(iArg1 As Integer)
   MsgBox "You passed a number - " & iArg1 & "."
End Sub
Function FuncWithTwoArgs(szArg1 As String, vArg2 As Variant)
   MsgBox "You passed " & szArg1 & " and " & CStr(vArg2) & "."
End Function
			

You can even specify complex expressions containing function calls as arguments. You can, if you need to, assign a Function instead of a Sub procedure. Excel, however, does not display procedures with arguments, in its Assign Macro dialog so you have to type in these assignments.

You can use the same technique and assign macros with arguments, to other drawing objects on worksheets as well as to toolbar buttons, but not to objects on dialog sheets.

Can I pass arguments to macros assigned using Application.OnKey? How?
Keywords: Argument Macro Parameter OnKey
Posted May 8, 1996

Yes. Enclose the entire assignment string in single quotes, and do not enclose the arguments in parentheses. You can even specify complex expressions containing function calls as arguments. You can, if you need to, assign a Function instead of a Sub procedure. Excel, however, does not display procedures with arguments, in its Assign Macro dialog so you have to type in these assignments. Here are a few examples:

  ''' Assigns keystrokes to custom procedures
Sub AssignKeys()
      ''' Call a macro when Ctrl-Shift-F4 is pressed
      ''' Prefix ^ (caret) for Ctrl, + (plus) for Shift
   Application.OnKey "^+{F4}", "'ProcWithOneArg 25'"
      ''' Two double-quotes are needed whenever we have a string within a string
   Application.OnKey "^s", "'FuncWithTwoArgs ""Some string"", Chr$(47) & Now()'"
End Sub
   ''' Called before exiting to restore keys
Sub ReassignKeys()
   Application.OnKey "^+{F4}"
   Application.OnKey "^s"
End Sub
Sub ProcWithOneArg(iArg1 As Integer)
   MsgBox "You passed a number - " & iArg1 & "."
End Sub
Function FuncWithTwoArgs(szArg1 As String, vArg2 As Variant)
   MsgBox "You passed " & szArg1 & " and " & CStr(vArg2) & "."
End Function
			

Will my Excel 5 macros and applications run correctly in Excel 7?
Keywords: Portable Compatible Excel
Posted January 16, 1996

In general the answer is absolutely yes. If your application uses either VBA or XLM your program should run without issues (we've tested tens of thousands of lines of code without incident). In most cases it will run faster and be more efficient in its use of resources.

But if your program is more sophisticated, and depends on either the Windows API or other calls to DLLs, it may need to be updated. This doesn't effect most users, but could effect a corporate user where the IS department has developed a custom line of business solutions.

Excel 7 and Excel 5.0NT are 32 bit programs that need to call 32 bit DLLs (which includes the Windows 95 APIs). Excel 5 (for Windows 3.1) is a 16 bit program and needs to call 16 bit DLLs. Without special translation software, you can't mix and match these two. Many 16 bit DLLs can be recompiled by the developer as a 32 bit version in a very short period of time.

Back to Top

Back to the FAQ Table of Contents

BUFFERS=20 FILES=15 2nd down, 4th quarter, 5 yards to go!