Excel VBA - Passing argument to a function

Question

I attempted to create an Excel function that will bold whatever range I tell it to in whatever form I request. Unfortunately, I've only had partial success in correctly passing the variable and obtaining this outcome. Of course, nobody likes a partial so can someone please let me know what I'm missing.

Sub Macro1()
On Error Resume Next

'Create & reset testing area.
   Range("A1:C6").value = "A"
   Range("A1:C6").Font.Bold = False
   [b2].Select

'The two lines below call the function perfectly and the cells are bolded without issue
   Text_bold ([a1])
   Text_bold (Cells(2, 1))

'However, the party stops there as the following code errors out.
   Text_bold ([b1].Address)
   Text_bold (Selection)
   Text_bold (Range("B3"))
'Similarly, the below fails as well...
   Text_bold (Range("B4:C4"))
'And even less surprising, the following also refuses to assist in the endeavor...
   Text_bold (Application.Union(Range("B5:C5"), Range("B6:C6")))
End Sub

Function Text_bold(x As Range)
   'MsgBox VarType(x)
   x.Font.Bold = True
End Function

Please help.

Gordon Bell · Accepted Answer · 2017-06-23 04:52:38Z

2

The parentheses around your function parameters are causing the problem. They are forcing the enclosed value to be evaluated before being passed as the function parameter, passing a Range.Value instead of Range object.

Sub Macro1()
    On Error Resume Next

     'Create & reset testing area.
    Range("A1:C6").Value = "A"
    Range("A1:C6").Font.Bold = False
    [b2].Select

    'The two lines below call the function perfectly and the cells are bolded without issue
    Text_bold [a1]
    Text_bold Cells(2, 1)

    'However, the party stops there as the following code errors out.
    Text_bold Range([C1].Address)
    Text_bold Selection.Range
    Text_bold Range("B3")
    'Similarly, the below fails as well...
    Text_bold Range("B4:C4")
    'And even less surprising, the following also refuses to assist in the endeavor...
    Text_bold Application.Union(Range("B5:C5"), Range("B6:C6"))
    MsgBox "OK"
End Sub

If you really want to use parentheses, prefix your function with Call statement.

Call Text_bold(Application.Union(Range("B5:C5"), Range("B6:C6")))

edited Jun 23, 2017 at 4:52

answered Jun 23, 2017 at 4:38

Gordon Bell

13.7k3 gold badges48 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

serakfalcon Over a year ago

For more understanding, I think it's helpful to note that the lines that are failing are failing because the Range object is providing Range.Value instead of being passed as the object itself

Gordon Bell Over a year ago

@serafalcon, yes noted. Thanks.

Gordon Bell Over a year ago

Also, you'll get syntax error if you try to call a multiple parameter function with parentheses without Call, like: FunctionX (param1, param2)

paul bica · Accepted Answer · 2017-06-23 04:38:01Z

In order to get more details about the issue you need to remove the statement
On Error Resume Next (aka On Error Hide All Bugs)

After I removed it I was able to determine the problems

The function (which should be a Sub because it doesn't return a value) is expecting a Range object: Text_bold(x As Range)
the line Text_bold ([b1].Address) is calling it incorrectly with parenthesis, and it is attempting to send as argument a string, not a range
all your calls to the function should be without brackets

Try this:

Sub Macro1()
    'Create & reset testing area.
    Range("A1:C6").Value = "A"
    Range("A1:C6").Font.Bold = False
    [b2].Select

    Text_bold [a1]
    Text_bold Cells(2, 1)
    Text_bold [b1]
    Text_bold Selection
    Text_bold Range("B3")
    Text_bold Range("B4:C4")
    Text_bold Application.Union(Range("B5:C5"), Range("B6:C6"))

    'A sub cannot return a value, a function can but it doesn't have to

    'To return a value from the Text_bold function
    Dim functionResponse As Boolean

    functionResponse = Text_bold([B3])  '<- THIS is where you need to use brackets
    MsgBox "Text in Cell [B3] is bold: " & functionResponse

End Sub

Function Text_bold(x As Range) As Boolean
    x.Font.Bold = True
    Text_bold = (x.Font.Bold = True)    'assign the return value to the function name
End Function

Collectives™ on Stack Overflow

Excel VBA - Passing argument to a function

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related