Function isn't working on button click

[next]

I added a button to a sheet, but for whatever reason I can't call a function inside of it.
Button code:

Sub AddLabor_Click()
addRow (range("B7:B8"))
End Sub
Function that I need to run:

Private Function addRow(r As range)
Dim cell As range

For Each cell In r
If IsEmpty(cell) Then
Rows(cell.Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Exit For
End If
Next cell
End Function

If I just paste the function code into button event handler, then everything works, but I need this code to be reusable. What's the problem here?
I'm getting "object required" error.

I also tried something like this:

Private Function test()
Debug.Print "test"
End Function

Sub AddLabor_Click()
test()
End Sub
But this one fails completely, saying "=" is expected.

[Paul_Hossler]

but I need this code to be reusable. What's the problem here?


The Private on the Function






Public
Optional. Indicates that the Function procedure is accessible to all other procedures in all modules (http://office.microsoft.com/client/helppreview14.aspx?AssetId=HV080006915&lcid=1033&NS=EXCEL%2EDEV&Version=14&CTT=5&origin=HV080007929). If used in a module that contains an Option Private, the procedure is not available outside the project (http://office.microsoft.com/client/helppreview14.aspx?AssetId=HV080006915&lcid=1033&NS=EXCEL%2EDEV&Version=14&CTT=5&origin=HV080007929).


Private
Optional. Indicates that the Function procedure is accessible only to other procedures in the module where it is declared.





Try something like this



Option Explicit
Option Private Module

Function addRow(r As Range)
Dim cell As Range

For Each cell In r
If IsEmpty(cell) Then
Rows(cell.Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Exit For
End If
Next cell
End Function




Paul

[Aflatoon]

Your button code is incorrect - you must remove the parentheses around the range parameter:

Sub AddLabor_Click()
addRow range("B7:B8")
End Sub

[Aussiebear]

Okay I'll put my hand up to be shot at: Can we have a row calling for the function "addRow" and a range address on the same line? And, would the target address be better served inside the function?

[Aflatoon]

I'm afraid I don't understand the question. Care to elaborate?

[Paul_Hossler]

Okay I'll put my hand up to be shot at: Can we have a row calling for the function "addRow" and a range address on the same line? And, would the target address be better served inside the function?


Well, I wouldn't have done it that way either, so at least I'm in good company

To be honest I really wasn't sure what the OP was trying to do, but I thought I could at least address the issue about not seeing the function

Paul

[next]

Paul, Private is the correct scope, I don't need this accessible to outside modules.
Aflatoon, Thank you, that was exactly it!!! Now it works.

[mikerickson]

I noticed that, under some common conditions (if the argument being passed isn't on the ActiveSheet), the function might insert a row on the wrong sheet. Try

cell.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

[Paul_Hossler]

Paul, Private is the correct scope, I don't need this accessible to outside modules.


Sorry -- made bad assumption that 'addRow' was in a standard module to be reusable in other handlers

Paul

[Aussiebear]

I'm afraid I don't understand the question. Care to elaborate?
The original code has a line "addRow range("B7:B8")". My understanding is that "addRow" is calling the function, and the range("B7:B8") is the address suggested for the inserted row. Last night the code didn't make any real sense, and again looking at it this morning I still think its a long way off. Since we know the location for the insertion why are we calling for a function when with just a couple of lines we can have the one sub


Sub AddLabor_Click()
With Range("B7:B8)
If IsEmpty(Cell) Then
Rows(Cell.Row).Insert Shift:=xlDown, CopyOrigin:= xlFormatFromLeftOrAbove
End If
End With
End Sub

[Aflatoon]

Because then you might have to repeat the same code for multiple buttons. ;)
Much easier to have a function that takes a range as a parameter.