When I sort an array it becomes filled with numbers instead of text
Article contributed by Dave Rado
Dim your array as a string; if you don't dimension, it or of you dimension it as a variant, you'll get the results described in the title of theis article.
Almost1 all variables should be dimensioned as whatever they are (Dim MyRange As Range, Dim MyString As String, etc.), for several reasons:
|
1. |
Otherwise you can get unexpected results, as in this case |
|
2. |
Your code will run much faster and use less memory |
|
3. |
You'll then have access to intellisense – for instance, if you type MyRange, and then type a dot, a list of all properties and methods supported by the range object will pop up – provided that MyRange was Dim'd as a Range. You can then cursor to the one you want and press Return (or select it with the mouse) to insert it. |
|
4. |
It makes debugging far easier, because the VBA Editor will then be able to “spot” errors it would otherwise miss. |
To force yourself to explicitly dimension all variables, your code window should have the line “Option Explicit” at the top of it. Select Tools + Options; and tick the “Require variable declaration” checkbox. This will have the effect of insertng “Option Explicit” at the top of all new modules you create. Doing this will give you all of the above benfits plus one more:
|
5. |
It makes debugging far easier, because if you accidentally misspell a variable name somewhere, this will be picked up as an undeclared variable when you compile.. |
__________________
|
1. |
There are a very small number of exceptions. One is that for some weird reason, the following doesn't work:
Dim FmField As FormField
You get a message "The Requested Member of the Collection Doesn't Exist"! Instead you have to use:
Dim FmField As Variant
|