Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
In need of advice?
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 sto 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 featu 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. -- |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
any advice pls.. | Excel Discussion (Misc queries) | |||
Need advice | Excel Worksheet Functions | |||
Advice Please | Excel Worksheet Functions | |||
Almost got it !! but need advice | Excel Worksheet Functions | |||
Advice please | New Users to Excel |