How do I get started in VB (Excel 2000 SR-1)

[JohnMac]

I *know* this is a very basic question but I am, as usual, becoming very frustrated with Microsoft's on-line help. It suggests that, to get started, create a macro and then edit it. This gets me into the VB editor and I can then create new functions in VB but I cannot figure out how to get Excel to "see" them as valid functions.
I am sure it is very simple but this makes it all the more frustrating that it is proving so difficult to find out via Help.

[LegareColeman]

Can you be a little more specific about how you are trying to get Excel to "see" your macro? If you have created a VBA Sub procedure, then you should be able to go to the Tools menu (from a worksheet), then select Macro then Macros from the pop-up menus. Then click on the macro name in the list and click run. If you have created a Function VBA procedure, the you should be able to enter a formula like:

<pre>=MyFunction()
</pre>


Can you tell us what you are doing and what happens when you do it?

[JohnMac]

Sorry, I should have been more specific but it is a general query - and another chance for me to grouse about Microsoft.
I can run macros using the method you suggest but it is running custom functions from within formulae that is the problem. I am sure it is because I am not saving them within the VB editor or something similar.

When I put custom function in a formula, e.g.

=MyFunction()

Excel returns #NAME? so is obviously not aware of MyFunction()

I am creating function via Tools|Macros|Visual Basic Editor
and then Insert|Module
to give me a blank screen

I then write the function using the following syntax:

Function MyFunction(ArgText)
Statement
Statement...
End Function

This is where I run in to trouble, as it is not clear what to do next. Saving the module (in Personal.xls) does not allow Excel to "see" it and I can find no other utility to do so.

Can you help? Sorry for being so brain-dead - last time I did any programming was via a text editor and a compiler!

[Andrew Cronnolly]

If you are saving your functions in the personal.xls file, you need to include that file name when referreing to the function in the worksheet, e.g.. =Personal.xls!MyFunction().

Check to see if you can locate the macors under the UserDefined category.

Andrew C

[JohnMac]

Added filename "Personal.xls" in fromt of function as you suggested but made no difference.

Could not find anything about UserDefined Category on the toolbars or menus of VB editor so did a search on "UserDefined (space and no space)" and "Category" then "Function" and, as usual, nothing useful was returned

Would you enlighten me?

Thanks.

[LegareColeman]

From a Spreadsheet, if you go to the Tools menu and select Macro, then select Macros from the pop up menu, do you have a Personal.xls listed in the drop down list labeled "Macros in:"? If you select the Function command from the Insert menu, and then click on User Defined, does your macro show in the list of macros?

[Andrew Cronnolly]

Oops, did you include the exclamation mark between personal.xls and the function name ?. I should have made that clearer, sorry.

If you goto Insert, Function you should get a dialog box with Function categories listed on the left and the actual functions for that category on the right. If you select the User Defined category, you should be able to find the reference to your functions, provided that the workbook (personal.xls?) containing them is open.

Hope that helps

Andrew C

[JohnMac]

Yes, Personal.xls is on list in "Macros in:"

Yes, my function is on the list of user-defined functions. This has also given me a clue as to why it wasn't working:
If I use Insert|Function|User defined|MyFunction, I see that the correct syntax for insertion into a cell is:
=PERSONAL.XLS!myfunction.myfunction()

With this syntax, function performs correctly. This is a rather long-winded way of inserting user defined functions. Is there a quicker way, e.g. storing function internally with spreadsheet?

Thanks.

[JohnMac]

Andrew
I did include the question mark but problem appears to be that syntax is:
"Personal.xls!myfunction.myfunction()
(thanks to Legare Coleman)

Seems rather long-winded to call a function but at least it works.

Is it just me or is Microsoft's on-line Help particularly poor - hence the need for very useful sites like The lounge?

[LegareColeman]

That is a rather strange syntax. I can't guess why you need the .myfunction without seeing your personal.xls. any chance you could attach a copy to a reply to this message?

[JohnMac]

Legare

Think I have found why the syntax was strange. In my increasingly desperate search to get Excel to recognise it, I had named the module "myfunction" instead of leaving it as module1. Excel obviously defaults to Module1,2,3 etc. when looking for the function and, as I had renamed it, I had to give an absolute path to the function. Renaming it as Module1 solved the problem.
Thanks for your help and do you know why on-line Help doesn't give step-by-step guidance for newcomers to VB? Is it just that I am too stupid to find it or is it not there to make me buy a Microsoft book?

[LegareColeman]

I think that the problem was probably that the module name and the function name were the same. There should be no problem changing the module name, but if the function name is the same, then XL must need the extra level of qualification to find it, otherwise when it gets to the module name it stops and decides that it does not know how to execute a module.