Created a Public Sub in Personal but Can't Call It

[chairs35]

I created a macro in Excel 2013 and saved it in personal.xlsb. I then went into VBA and added an input into the macro.
-Should I specify this as a function or a sub? I am not sure when to specify one versus the other
-Can a sub have an input?
-example: Public Sub Namecall(Cellinput As String)
-I am in the excel file (macros are enabled) I want to use the macro/function in. When I type in =Namecall(A2), it does not register a function or a macro and gives me the #NAME? error. Why is that? Is there something I need to do?
-Lastly, If I want it to call A2 from the sheet directly in front of the current sheet, how would I write that in VBA?
-I was thinking =Namecall('Priorsheet'!A2)

 

[RoryA]

If you want to call it from a sheet, it needs to be a function - functions return values, subs don't.

 

[Jerry Sullivan]

Adding to Rory's reply...

-Can a sub have an input?
-example: Public Sub Namecall(Cellinput As String)

Subs and Functions can both have input Parameters; however Sub's can't return values, but Functions can.

-I am in the excel file (macros are enabled) I want to use the macro/function in. When I type in =Namecall(A2), it does not register a function or a macro and gives me the #NAME? error. Why is that? Is there something I need to do?
-Lastly, If I want it to call A2 from the sheet directly in front of the current sheet, how would I write that in VBA?
-I was thinking =Namecall('Priorsheet'!A2)

Making your procedure a Public Function instead of a Sub is only one step to being able to use it as a User Defined Function (UDF) in your worksheet. shg posted a table at this link that explains 3 methods to reference a UDF stored in a Personal.xlsb file.

Anyway to skip reference to personal.xlsb when calling functions [SOLVED]

Excel 2013
	A	B	C
1	Method	Advantage	Disadvantage
2	Preface the function name with workbook name, e.g., '=Personal.xls!MyFunc(...)	Personals macros appear in the Macros dialog.	No reference required. A lot of typing
3	Name the VBA project something recognizable (like projPersonal) so you can find it in Tools > References, and then set a reference.	Personals macros appear in the Macros dialog	Requires a reference in every workbook. If the workbook includes a reference to Personal but uses no Personal UDFs, but does include and use some UDFs of its own, and the workbook is sent to someone else, the reference itself will result in a missing reference error. Thats a disincentive to including a reference in your standard workbook template.
4	Set Personals IsAddIn property to True and then save Personal	No reference required	Personals macros do not appear in the Macros dialog
Sheet1