VBA Sort

[Guest]

I recorded a macro to perform a sort of a single column on a worksheet then
rewrote the code to perform the same function when a button on a form is
pressed. This is the code I used to append a column in a worksheet with a
value of a textbox input and then sort the column:

Private Sub CommandButton1_Click()
'sngCountAns is the number of entries contained in the target column
strAddApt = Me.TextBox1.Value
If Len(strAddApt) = 4 Then
Sheets("Lists").Range("P" & sngCountAns + 1).Value = strAddApt
ActiveWorkbook.Worksheets("Lists").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Lists").Sort.SortFields.Add Key:=Range("P2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Lists").Sort
.SetRange Range("P2" & sngCountAns + 1)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
UserForm_Activate
Me.TextBox1.Value = ""
Else
MsgBox ("Airport ID must be 4 digits to be added to the list")
End If
End Sub

I am using windows vista and Excel 2007. When I press the button the
operations performs exactly as I want it to. The file is saved in
compatability mode. When the program is opened with a pc using Excel2002, the
sort functions generates errors when the CommandButton1 is pressed.

I rewrote the code so that it works in Excel 2002 but now it generates
errors when I run the program with Excel 2007.

Can anyone help me with code that will sort a single column in all versions
of excel from 97 to 2007?
Thanks.
Paul

 

[Guest]

Hi Paul,

You say you've written two versions of the code, one that works in each
version of Excel. One way you could get around the problem would be to test
for the version of Excel that is being used and then write your two pieces of
code:

Dim intThisVersion As Integer
intThisVersion = int(Application.Version)
If intThisVersion = 10 Then '2002 version number

'Your 2002 code

ElseIf intThisVersion = 12 Then '2007 version number

'Your 2007 code

Else 'Other versions

End If


I don't have 2007 to hand right now, so I can't test the problem for myself,
sorry.

Sean.

 

[Guest]

Sean,

I will do that as a workaround but without every version of excel to test
and not sure what version my users will be working with I will either need a
version of code for each version of excel or one version that is sure to work
on them all. That's the problem with VB programming in excel isn't it?

Thanks for the suggestion.

Paul

 

[Guest]

It can be a problem when some parts of the functions are amended between
versions. Sometimes it is possible to leave out certain optional criteria so
that the code is OK with all versions, but you would still need to know how
the code works in all versions before you would know what to leave out. 2002
and 2003 are fairly similar, so you may find they act the same. I would
imagine it's unlikely that many people with still be using 2000 or older, but
you never know.

Having two pieces of code in front of you, you should be able to see where
the differences are, and see if the things that are tripping each version up
can be omitted. For instance, using the Find method as it applies to a Range
object in 2003, you can use a SearchFormat parameter, but this will trip up
in 2000. However, as it is an optional parameter, you can omit it, and then
the code works in both versions.

Again, not having 2007 with me, I can't test to see exactly where the
differences lie, so I can't be of much more help. You could always write some
code in the Else part so that anyone that uses your code and doesn't have
2002 or 2007 can notify you. Not an ideal solution though.

Sean.

 

[Guest]

Sean
Your help and advice is very much appreciated.
Paul