UDF and Excel 2011 for MAC

I want to create a small function that give me the date x amount of days ago. The code I have works when it is copied into the workbook's module I am currently working on. However I need it to be available access many workbooks. When inserted into a personal workbook project module, it returns: "#NAME?". The function is visable in the built in function list. I assume it has something to do with how the personal workbook functions. I recorded a basic macro the enters the number 7 into a cell. I saved this into the personal workbook and it is accessible in any other excel document.

Any Ideas?

Please Login or Register  to view this content.

Prgramming experience: Beginner
Previous Languages: Java, Python, HTML
Office 2011 14.2.3 for Apple MAC
Visual Basic 14.2.3

I got it to work. I wonder, do you have the cell where you used this function formatted as a date?

On the other hand, if you are wanting to put 7 in one cell and get the date - 7 in another cell, you need:

Please Login or Register  to view this content.

Is this just an exercise? =today()-7 would be easier

it is an partially an exercise. I use sumif and averageif with today()-X quite a bit and I was hoping to simplify those expressions while learning Excel VBA.

When I enter

Formula:

Please Login or Register  to view this content.

I get a the following message, "Cannot find personal.xlsm". Doing a spotlight search yields nothing either. The function's module is clearly under the Personal Macro Workbook project. A quick google on this file didn't yield much. Most of the data only deals with windows. Could the problem lie here?


The purpose of the recorded macro to enter the number 7, was purely a test. It shows that a recorded macro works across workbooks, but not functions.

Open the VBE, and in the workbook of interest, do Tools > References and set a reference to the VBA project in Personal (which you should rename to something recognizable like projPersonal)

Adding the project as a reference does work. Is there a way to make this automatic across all workbooks?

s your personal macro workbook called personal.xls or personal.xlsb perhaps?

I have no idea. I search both and come up blank. Did i need to create it? where are my recorded macros being stored?

Add a reference in Book.xlsx in your XLSTART folder.

This didn't work, what exactly and I referencing to what? also, I couldn't find the XLSTART file, however there was this file: /Applications/Microsoft Office 2011/Office/Startup/Excel
IT was empty so i created a new workbook in that location.