O
onedaywhen
If Excel's limits (e.g. 65536 rows) inspire you to check out using a
database, go straight to Access, the default choice because all Excel
users have it. Other databases are not available so don't try looking,
you won't find any. And don't just use Access as a data store: ensure
you completely re-write your spreadsheet as an Access application.
Access (don't call it MS Access because doing so prevents the helpful
'Access data' and 'access data' confusion) is a relational database:
pay no attention to the Jet engine behind the curtain. You need the
Access application installed on your machine to be able to access
Access databases; you can access the data from with Excel by
automating Access in VBA code.
The term 'named range' is preferred because that's what Lotus 1-2-3
(RIP) called them; 123 was the granddaddy of spreadsheets meaning most
Excel users have 123 experience. The term 'defined Name' (note the
pedantic capitalization) is too technical, overly Excelish and
ultimately misleading because a Name can only ever refer to a Range
object.
Use public variables in standard modules liberally. It saves a lot of
learning e.g. the concept of scope, passing arguments to functions
ByVal or ByRef, what a class module is for, etc. Other handy things to
check out are Goto and GoSub which have long been considered harmless.
Feel free to rely on the implicit default behavior of a feature:
everyone is familiar with it and its behavior is guaranteed never to
change, even when you move to the highly dubious VB.NET (note C# is
not suitable for experienced VBA users). For example, don't specify an
object's default property e.g. use Range("A1") in place of
Range("A1").Value, especially when assigning it to a public variable
of type Variant (but don't declare it as Variant, use the implicit Dim
MyVar1), and do not qualify the object - Excel will work out what you
mean - and never, repeat never, qualify the object with the class name
'Excel' as in Excel.Application.ActiveWindow. Naturally there are
exceptions where default beavior is not to be trusted e.g. never
merely let a local object variable go out of scope but instead
explicitly set it to Nothing, e.g. Set MyObject = Nothing, because VB
won't release it unless told to do so (it only cleans up those
variables to which you don't have access to e.g. where you've used
With MyObject..End With).
If you choose to go down the road less travelled and use a class e.g.
by creating a userform, don't worry about the class not being aware of
its own members. This is common practice in object oriented
programming because you always get a handy collection to loop through
at run-time e.g. For Each ctl In Me.Controls to check that the
controls you put on at design-time are still there.
I have discovered a truly remarkable way of programmatically clearing
the Immediate Window (without using SendKeys) which this margin is too
small to contain.
Never try to take the credit / We'll all assume Chip Pearson said it.
That's it. See ya.
--
database, go straight to Access, the default choice because all Excel
users have it. Other databases are not available so don't try looking,
you won't find any. And don't just use Access as a data store: ensure
you completely re-write your spreadsheet as an Access application.
Access (don't call it MS Access because doing so prevents the helpful
'Access data' and 'access data' confusion) is a relational database:
pay no attention to the Jet engine behind the curtain. You need the
Access application installed on your machine to be able to access
Access databases; you can access the data from with Excel by
automating Access in VBA code.
The term 'named range' is preferred because that's what Lotus 1-2-3
(RIP) called them; 123 was the granddaddy of spreadsheets meaning most
Excel users have 123 experience. The term 'defined Name' (note the
pedantic capitalization) is too technical, overly Excelish and
ultimately misleading because a Name can only ever refer to a Range
object.
Use public variables in standard modules liberally. It saves a lot of
learning e.g. the concept of scope, passing arguments to functions
ByVal or ByRef, what a class module is for, etc. Other handy things to
check out are Goto and GoSub which have long been considered harmless.
Feel free to rely on the implicit default behavior of a feature:
everyone is familiar with it and its behavior is guaranteed never to
change, even when you move to the highly dubious VB.NET (note C# is
not suitable for experienced VBA users). For example, don't specify an
object's default property e.g. use Range("A1") in place of
Range("A1").Value, especially when assigning it to a public variable
of type Variant (but don't declare it as Variant, use the implicit Dim
MyVar1), and do not qualify the object - Excel will work out what you
mean - and never, repeat never, qualify the object with the class name
'Excel' as in Excel.Application.ActiveWindow. Naturally there are
exceptions where default beavior is not to be trusted e.g. never
merely let a local object variable go out of scope but instead
explicitly set it to Nothing, e.g. Set MyObject = Nothing, because VB
won't release it unless told to do so (it only cleans up those
variables to which you don't have access to e.g. where you've used
With MyObject..End With).
If you choose to go down the road less travelled and use a class e.g.
by creating a userform, don't worry about the class not being aware of
its own members. This is common practice in object oriented
programming because you always get a handy collection to loop through
at run-time e.g. For Each ctl In Me.Controls to check that the
controls you put on at design-time are still there.
I have discovered a truly remarkable way of programmatically clearing
the Immediate Window (without using SendKeys) which this margin is too
small to contain.
Never try to take the credit / We'll all assume Chip Pearson said it.
That's it. See ya.
--