Custom Functions not available in spreadsheet

[jacques330ci]

Hello all,

I am experience a problem accessing the custom functions I have created via VBA code in Excel. This occurs in both 2007 and Office 365 versions:

I am able to write the code (see below) for the function named CalculateArea(), compile it successfully with no errors, and it appears in the Declarations drop-down box of the VBA code editor. But when I switch over to the accompanying spreadsheet, I am unable to access the function (it does not appear in the shortcut list when I type "=CalculateArea" into a blank cell; nor does it appear in the Insert Function dialog box). There isn't even a category listed for 'Custom', 'User-Defined', etc. in the Insert Functions dialog box.

Both installations of Excel were made to include all features (everything set to 'Run from My Computer').

Can someone please advise me on what I am missing?

Public Function CalculateArea(Length As Double, Width As Double) As Double
Dim Area As Double

Area = Length * Width
CalculateArea = Area
End Function

Thank you in advance for any help you can provide me. BTW, this website's design is AWESOME!

jacques

 

[hiker95]

jacques330ci,

Welcome to the MrExcel forum.

If I understand you correctly, how about something like this:

Excel 2007
	A	B	C	D
1	275.7865
2
3	12.23	22.55	275.7865
4
Sheet1

Cell Formulas
Range	Formula
A1	=CalculateArea(12.23,22.55)
C3	=CalculateArea(A3,B3)

Public Function CalculateArea(Length As Double, Width As Double) As Double
Dim Area As Double
Area = Length * Width
CalculateArea = Area
End Function

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The function is installed and ready to use as shown in the example above.

 

[jacques330ci]

Hello hiker95,
Thanks for responding.
When I enter the same code and save the worksheet as macro-enabled, this is what displays in the cell:

#NAME?

My screenshot is a jpeg, and could help to see what's happening, but on my computer, not accessible via URL

jacques

 

[jacques330ci]

Don't see how this should make a difference, but the Excel 2007 is installed in a Virtual PC instance of Windows 7 OS for training purposes, and the Excel 2013 is installed on the Windows 7 host. I have Excel 2010 on another PC but I haven't tried that one.

 

[pgc01]

Hi jacques

Did you insert the code in a general module?
The udf code cannot be inserted in the worksheet module.

Remark: really (really) bad choice of variable names. All Length, Width and Area are names of object properties in vba. This is bad practice.

 

[hiker95]

Hello hiker95,
Thanks for responding.
When I enter the same code and save the worksheet as macro-enabled, this is what displays in the cell:

#NAME?

My screenshot is a jpeg, and could help to see what's happening, but on my computer, not accessible via URL

jacques

jacques330ci,

Can we see your actual workbook/worksheet(s)?

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[jacques330ci]

Hi PGC

Thank you! That was the issue. I was using a General module. Once I used the Class Module, it worked.

And thanks for the tip regarding the choice of variable names. This is actually a tutorial that I am doing and just using what they suggest, which excluded the Class Module requirement.

Much appreciated,
Jacques

 

[jacques330ci]

ok, thanks hiker95.

that's so cool. I wondered how you got the worksheet views in here.
You and PGC are doing great work and a much need service to the Excel user community.

Have a great weekend,
Jacques

 

[jacques330ci]

hey guys, should this post be marked as Solved or something?

 

[hiker95]

ok, thanks hiker95.

that's so cool. I wondered how you got the worksheet views in here.
You and PGC are doing great work and a much need service to the Excel user community.

Have a great weekend,
Jacques

jacques330ci,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.