0

I define my own simple Sub 

Sub test1()
    MsgBox "Hello"
End Sub

but when I try to reference it in a cell

=test1()

I get the following error prompter:

**The name that you entered is not valid
Reason for this:
1) The name does not begin with a letter or underscore
2) The name contains a space or other invalid character
3) The name conflicts with a Excel built in name or name of another object in the workbook**

I have no named ranges and the macro is runs without problems in the Macro manager window.

**What does this error message mean?

Do I need to name macro in a certain way to be able to use it?**

Peter Vogt
  • 353
  • 1
  • 4
  • 16

1 Answers1

1

Two things:

  • First, your Sub must be a Function.
  • Second, you need to place the code in a Module, not behind any Sheet or ThisWorkbook.
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • just to add to this, it still wouldn't work as a function due to UDF limitations. https://support.microsoft.com/en-us/kb/170787#mt1 – SierraOscar Jul 31 '15 at 21:47
  • Correct. The message box does appear after typing `=test1()` in a cell but of course returns zero. I believe the OP just wanted to test. – Parfait Jul 31 '15 at 21:54
  • Of course, rules are always made to be broken... http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet/31720319 – SierraOscar Jul 31 '15 at 21:59
  • The macro is in a module. Does it need to have a special name? The problem with converting it into a function is that apparently functions cannot change any attributes like background color the calling cell. II tried this. – Peter Vogt Aug 01 '15 at 01:20
  • To run a macro (Sub), you cannot use the method you posted. You must use the Macro Dialog box, or you could place a button on the worksheet. – Ron Rosenfeld Aug 01 '15 at 01:33