Re: [ExcelVBA] failing function

 

ok, a little training about VB macros.

first, in your File->options->Advanced configuration, there is a designation that says something like:

"At Startup, open all files in: "

by default, common installations of Excel use the XLSTART folder in the installation folder.

Any files placed in this folder open whenever you open an instance of Excel.

When you "record" a macro, the macro panel asks where the macro is to be stored.

The macros can reside within the open/active workbook, or in your "personal" workbook.

This workbook, usually called "Personal.xls" in earlier versions of Excel,

or "Personal.xlsm" or "Personal.xlsb" in later versions.

This workbook is a "hidden" workbook and will be automatically created in the "startup" folder defined above.

The macro recorded can then be used in any workbook opened in an Excel instance that also has this hidden workbook.

However, when you call this function, since the function isn't in the "currently active" workbook,

you must specify which workbook has the function.

=Func( A5:E5,A6:E6)

would work if the function is stored in the currently active workbook.

In his example, he stored the function in the hidden Personal.xlsb file, so he had to specify the location:

=PERSONAL.XLSB!Func( A5:E5,A6:E6)

(he could have eliminated this requirement if he changed the function to "public" by using:

Public Function Func( x as range, y as range)

...

End Function

Does that help explain what is going on with his explanation?

Paul

-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

On Tuesday, October 25, 2016 3:34 PM, "solutionbuilder@yahoo.com [ExcelVBA]" <ExcelVBA@yahoogroups.com> wrote:

 

OK, you lost me with the "I put :-=PERSONAL.XLSB!Func( A5:E5,A6:E6)," statement.   When I save while in my VBA editor it saved to my project folder as FuncTest.xlsm type file.   I am guessing that your Personal.xlsb is something different, because when I used FuncTest.xlsm!Func(A5:E5,A6:E6) it could not find the function.

I am not going to give up yet.  I just hope to get nudged in the right direction. 

__._,_.___

---

Posted by: Paul Schreiner <schreiner_paul@att.net>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (15)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___