Retrieve highest number from an array

[jolivanes]

Related to Mark's code in
http://www.vbaexpress.com/forum/show...213#post284213
Is it possible to store sheetnames in an array as numbers or convert them to numbers while in the array and then retrieve the highest number?
Mark's code works like a charm of course but I was wondering if there is another way.
I was trying with the below code
Some of the Sheet names are 2009, 2010, 2011 etc

[VBA]
Sub Macro1()
Dim ShtNames() As Variant
Dim i As Variant
ReDim ShtNames(1 To ActiveWorkbook.Sheets.Count)
For i = 1 To Sheets.Count
ShtNames(i) = Sheets(i).Name
Next i
Range("B12").Value = Application.Max(ShtNames) '<---- Gives a zero (0)

'Following works OK
For i = 1 To Sheets.Count
Cells(i, 1) = ShtNames(i)
Next i
End Sub
[/VBA]

Or am I on a totally wrong track with this?
Thanks and Regards
John

[patel]

ShtNames(i) = Val(Sheets(i).Name)

you can not have max of a text Array

[snb]

[vba]
Sub M_snb()
x=1
for each sh in sheets
x=application.max(x,Val(sh.name))
next

msgbox x
End Sub[/vba]

or

[VBA]sub M_snb()
dim sn(sheets.count-1)

for j=1 to sheets.count
sn(j-1)=val(sheets(j).name)
next

msgbox application.max(sn)
End Sub[/VBA]

[jolivanes]

@patel
Perfect. I was trying to multiply the values by 1 but that didn't work
Thanks patel

@snb.
Perfect also. The 2nd code needed changing (ReDim instead of Dim) maybe because I use Option Explicit. Did not try it without it.
Tanks snb

Thanks and Regards

John