Trying to Start a function causes Runtime Error 424

Question

I am trying to start the following function

Function MitteOben(Bereich As Range)

    With Bereich
        .VerticalAlignment = xlTop
        .HorizontalAlignment = xlCenter
    End With

    With Bereich.Font
        .Name = "Tahoma"
        .Size = 10
        .ThemeColor = xlThemeColorLight1
    End With

End Function

When I try to define a range and start the function it creates Runtime Error 424.

I am trying to start it this way:

Set Hlpr = Range(Cells(ObjStartRow, 14), Cells(ObjStartRow, 28))
MitteOben (Hlpr)
Fett (Hlpr)

I have also tried to Swap "ObjStartRow" with a number or Change the whole range to: Range("N29:AC29") both have failed.

That is not possible in the way you do it, but in a way @Tim Williams does [here](https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) and all other ways described in the link he posted. — L42, Jul 07 '17 at 09:50
Since you are not returning any value or Object, you can use a regular `Sub` instead of your `Function`. And if you want to call it, either use `MitteOben Hlpr` , or the old way of `Call MitteOben (Hlpr)`. **BTW** your `Hlpr` range is not fully qualified with the `Worksheet` it is in. — Shai Rado, Jul 07 '17 at 10:50

score 1 · Answer 1 · answered Jul 07 '17 at 11:46

It should be a Sub, not a function. In genreal, functions return values, subs do something.

Having said that, as a VBA person working in Germany, I beg you to start naming your Functions, Variables and Subs in English. It really saves a lot of passive anger from the next person working over your code. And it makes your code better.

Public Sub MitteOben(Bereich As Range)

    With Bereich
        .VerticalAlignment = xlTop
        .HorizontalAlignment = xlCenter
    End With

    With Bereich.Font
        .name = "Tahoma"
        .Size = 10
        .ThemeColor = xlThemeColorLight1
    End With

End Sub

This is how to call it: call MitteOben(selection) or MitteOben selection

Trying to Start a function causes Runtime Error 424

1 Answers1