workbook startup creates #NAME error for UDF
 

For some reason, we keep getting an occassional #NAME error in cells
that contain the user defined function 'rowheight()' created by the
code shown below when we open the workbook in which it resides. It is
important to note that the workbook is a shared workbook. However, the

problem can then be easily corrected by simply pressing F2 on any one
of the #NAME cells and then pressing <Enter. Then excel recognizes
the UDF. But why doesn't it recognize it when we open the workbook,
and what can I do to correct the problem? The code sits alone in Module
1 of the workbook. All other macros are
in Module 2. This is the only UDF in the workbook.

Thanks for your help...

Code:
Function rowheight(rng As Range)
Application.Volatile
rowheight = rng.Areas(1).Cells(1, 1).rowheight
End Function

workbook startup creates #NAME error for UDF
 

Couple of questions/comments...
Is Module1 a sheet module? If so, put the function in a standard module.
"RowHeight" is already used by Excel as a property.
Try another function name to avoid confusion.
"RowSize" would take fewer key strokes. <g
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



wrote in message
For some reason, we keep getting an occassional #NAME error in cells
that contain the user defined function 'rowheight()' created by the
code shown below when we open the workbook in which it resides. It is
important to note that the workbook is a shared workbook. However, the

problem can then be easily corrected by simply pressing F2 on any one
of the #NAME cells and then pressing <Enter. Then excel recognizes
the UDF. But why doesn't it recognize it when we open the workbook,
and what can I do to correct the problem? The code sits alone in Module
1 of the workbook. All other macros are
in Module 2. This is the only UDF in the workbook.

Thanks for your help...

Code:
Function rowheight(rng As Range)
Application.Volatile
rowheight = rng.Areas(1).Cells(1, 1).rowheight
End Function

workbook startup creates #NAME error for UDF
 

How do you differentiate a sheet module from a standard module? I used
a module in the Visual Basic Editor. I'm not very experienced in
Visual Basic..

Raul..


Jim Cone wrote:
Couple of questions/comments...
Is Module1 a sheet module? If so, put the function in a standard module.
"RowHeight" is already used by Excel as a property.
Try another function name to avoid confusion.
"RowSize" would take fewer key strokes. <g
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



wrote in message
For some reason, we keep getting an occassional #NAME error in cells
that contain the user defined function 'rowheight()' created by the
code shown below when we open the workbook in which it resides. It is
important to note that the workbook is a shared workbook. However, the

problem can then be easily corrected by simply pressing F2 on any one
of the #NAME cells and then pressing <Enter. Then excel recognizes
the UDF. But why doesn't it recognize it when we open the workbook,
and what can I do to correct the problem? The code sits alone in Module
1 of the workbook. All other macros are
in Module 2. This is the only UDF in the workbook.

Thanks for your help...

Code:
Function rowheight(rng As Range)
Application.Volatile
rowheight = rng.Areas(1).Cells(1, 1).rowheight
End Function

workbook startup creates #NAME error for UDF
 

Raul,
If you right-click a sheet tab and select view code you will see
the module associated with that sheet. A sheet module, generally,
should only have code in it dealing with "events" on that sheet.
In your case, you could look at each sheet module and see if
there is any code in them.
There are five types of modules...
1. sheet modules
2. "ThisWorkbook" module
3. Class Modules
4. User form modules.
5 standard/regular modules
(technically, items 1 to 4 are all class modules)
All of these modules are viewable in the VBE.

The Sheet and ThisWorkbook modules are the only ones
creating automatically by Excel. The user has to tell Excel
to create the other ones.

A standard module is created from the "Insert" menu in the VBE by
selecting "Module". They are usually named Module1, Module 2 etc.
You are probably all right in this regard as your module has the
default name. I of course didn't know your skill level or preferences.
You can change the name of any module to almost anything you want.

I would still change the name of the Function, no matter what.
Also, the fact that you are dealing with a shared workbook is a factor
that complicates things. I and a lot of others answering questions
here just don't deal with shared workbooks and questions relating to
them generally just lie there. <g
Shared workbooks have a reputation for being erratic.

Jim Cone
San Francisco, USA




wrote in message
How do you differentiate a sheet module from a standard module? I used
a module in the Visual Basic Editor. I'm not very experienced in
Visual Basic..
Raul..


Jim Cone wrote:
Couple of questions/comments...
Is Module1 a sheet module? If so, put the function in a standard module.
"RowHeight" is already used by Excel as a property.
Try another function name to avoid confusion.
"RowSize" would take fewer key strokes. <g
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




wrote in message
For some reason, we keep getting an occassional #NAME error in cells
that contain the user defined function 'rowheight()' created by the
code shown below when we open the workbook in which it resides. It is
important to note that the workbook is a shared workbook. However, the

problem can then be easily corrected by simply pressing F2 on any one
of the #NAME cells and then pressing <Enter. Then excel recognizes
the UDF. But why doesn't it recognize it when we open the workbook,
and what can I do to correct the problem? The code sits alone in Module
1 of the workbook. All other macros are
in Module 2. This is the only UDF in the workbook.

Thanks for your help...

Code:
Function rowheight(rng As Range)
Application.Volatile
rowheight = rng.Areas(1).Cells(1, 1).rowheight
End Function

workbook startup creates #NAME error for UDF
 

If that's how you create a 'standard' module, then I did it correctly.
I don't understand why it doesn't recognize it. I'm now trying to
change the name to "rowtall" instead of "rowheight." Let's see what
happens..

Raul..

Jim Cone wrote:
Raul,
If you right-click a sheet tab and select view code you will see
the module associated with that sheet. A sheet module, generally,
should only have code in it dealing with "events" on that sheet.
In your case, you could look at each sheet module and see if
there is any code in them.
There are five types of modules...
1. sheet modules
2. "ThisWorkbook" module
3. Class Modules
4. User form modules.
5 standard/regular modules
(technically, items 1 to 4 are all class modules)
All of these modules are viewable in the VBE.

The Sheet and ThisWorkbook modules are the only ones
creating automatically by Excel. The user has to tell Excel
to create the other ones.

A standard module is created from the "Insert" menu in the VBE by
selecting "Module". They are usually named Module1, Module 2 etc.
You are probably all right in this regard as your module has the
default name. I of course didn't know your skill level or preferences.
You can change the name of any module to almost anything you want.

I would still change the name of the Function, no matter what.
Also, the fact that you are dealing with a shared workbook is a factor
that complicates things. I and a lot of others answering questions
here just don't deal with shared workbooks and questions relating to
them generally just lie there. <g
Shared workbooks have a reputation for being erratic.

Jim Cone
San Francisco, USA




wrote in message
How do you differentiate a sheet module from a standard module? I used
a module in the Visual Basic Editor. I'm not very experienced in
Visual Basic..
Raul..


Jim Cone wrote:
Couple of questions/comments...
Is Module1 a sheet module? If so, put the function in a standard module.
"RowHeight" is already used by Excel as a property.
Try another function name to avoid confusion.
"RowSize" would take fewer key strokes. <g
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




wrote in message
For some reason, we keep getting an occassional #NAME error in cells
that contain the user defined function 'rowheight()' created by the
code shown below when we open the workbook in which it resides. It is
important to note that the workbook is a shared workbook. However, the

problem can then be easily corrected by simply pressing F2 on any one
of the #NAME cells and then pressing <Enter. Then excel recognizes
the UDF. But why doesn't it recognize it when we open the workbook,
and what can I do to correct the problem? The code sits alone in Module
1 of the workbook. All other macros are
in Module 2. This is the only UDF in the workbook.

Thanks for your help...

Code:
Function rowheight(rng As Range)
Application.Volatile
rowheight = rng.Areas(1).Cells(1, 1).rowheight
End Function