Just to add to Chip's response...

I think I'd use this:

L = worksheets("Sheet999").Evaluate("=MAX(LEN(""A1:A10 0""))")

Then I wouldn't have to worry about what worksheet was active when the code ran.

Chip Pearson wrote:

You can use the Evaluate method to have Excel parse and calculate a
formula passed in as a string. E.g.,

Dim L As Long
' watch for the pairs of " characters.
L = Application.Evaluate("=MAX(LEN(""A1:A100""))")
Debug.Print L

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 27 Oct 2008 22:24:30 +0100, "Joergen Bondesen"
wrote:

Hi Alan

Thanks, it works fine, but I need a VBA solution. My mistake, sorry.

--

Dave Peterson