UDF x worksheet functions

[Guest]

I use the UDF below to find the value of the last cell in a certain column.
But I use this function in many worksheets which will be sent do many people.
I want to avoid the attachment of the function in each worksheet and also to
avoide the use of .XLA. Is there a way to have the same results only with
worksheet functions in a way that any standard Excel could calculate?

Function ultima(col As Integer) As Variant
Application.Volatile
If Cells(65536, col) = "" Then
ultima = Cells(65536, col).End(xlUp).Value
Else
ultima = Cells(65536, col).Value
End If
End Function

 

[David McRitchie]

Hi Rogerio,
see http://www.mvps.org/dmcritchie/excel/lastcell.htm#formulas

and your User Defined Function should not be using Integer instead use Long
and instead of 65536 use cells.rows.count

 

[Guest]

Thanks a lot for your answer. It is exactly what I was looking for. It is a
nice solution to use cells.rows.count instead of 65536 even because Excel's
new version will have more than 1 millino rows. But I disagree about defining
col as long instead of integer. col will receive the number of the column
which is between 1 and 256. I could almost use the byte data type (1 byte
storing values from 0 to 255), but it wouldn't work for the last column
(column 256). Using integer data type (2 bytes storing values from -32768 to
32767) I will have no problems and will save memory. Using long data type (4
bytes storing values from -2147483648 to 2147483647) I think I will be
wasting memory.
What do you think about?

Best regards

--
Rogerio Takejame
Americana - Sao Paulo - Brazil

Hi Rogerio,
see http://www.mvps.org/dmcritchie/excel/lastcell.htm#formulas

and your User Defined Function should not be using Integer instead use Long
and instead of 65536 use cells.rows.count

 

[David McRitchie]

You never know when limits may change, the limit in Excel 12
however on columns is known:
The total number of available columns in Excel
Old Limit: 256 (2^8)
New Limit: 16k (2^14) is 16384 (1 to 16384)

Integer is -32,768 to 32,767 so for Excel 12, you would be okay, but
things could change again in the future.

As far as saving space goes you will save nothing in compiled code,
and in what you see the word INTEGER is longer than the word LONG
and I would not worry about that in a 500K workbook, expecially when
processing INTEGER to convert to LONG will probably take more time.
Unless you actually find something in a Microsoft Blog to indicate that
Integer would be more efficient. There have been postings (not by Microsoft)
indicating that on a Mac Integer was automatically changed to Long.
Your point would be more valid in the definition of the Worksheet Columns.
where you might save some space.

In any case the limit on columns will not be 256 so you would want to
use cells.columns.count instead of 256. So you probably want to
actually use a limit based on usedrange or specialcells.
..

--

Thanks a lot for your answer. It is exactly what I was looking for. It is a
nice solution to use cells.rows.count instead of 65536 even because Excel's
new version will have more than 1 millino rows. But I disagree about defining
col as long instead of integer. col will receive the number of the column
which is between 1 and 256. I could almost use the byte data type (1 byte
storing values from 0 to 255), but it wouldn't work for the last column
(column 256). Using integer data type (2 bytes storing values from -32768 to
32767) I will have no problems and will save memory. Using long data type (4
bytes storing values from -2147483648 to 2147483647) I think I will be
wasting memory.
What do you think about?

Best regards