What's The Best Way To Setup A Spreadsheet

[WorkMC]

My boss at work seems to think I'm an expert with excel. I've said that I have limited knowledge and even so, he has asked me to setup a spreadsheet, to record data and manipulate the results, which will be easy to use by other people.

I'm willing to give this a go but thought I'd seek advise on the best route to take from the REAL experts at MrExcel. No doubt I will be asking further questions once I start the work.

Unfortunately I'm not allowed to use Access so this will not be an option even though I know it probably be better suited.

The workbook will have about 20 pages with the main sheet containing almost 65000 rows of data. Some of the other pages will be used to produce reports and tempory archieve bits of old data.

Now the questions:-

1. In the past for the start page (menu) I've used command buttons on a worksheet but have toyed with the idea of a Userform that is displayed at startup. Is there any difference between the two or can a Userform cause problems later on.

2. Data entry will be made by another Userform which I'm happy at setting up. The only problem is that various calcualtions need to run once data is entered. Is it best to run these as data is entered or at the end when all of the data has been recorded? The reason for getting this part is so that I don't slow the overall opertion of the spreadsheet down.

3. When using a Userform does the associated worksheet with the form have to be visable? It's just that in the past I've always hidden the background worksheets and only made them visable when a particular userform has been selected.

4. Occasionaly I use Application.Screenupdating in my macro's. If I use a command button to do a few things which includes this and also call another macro, do I need Application.ScreenUpdating in the macro that is being called?

5. Is it best to use a combo or list box on a userform?

6. When inserting a Table and defining the range does it make any difference to the performance of Excel if =Sheet1!$A:$C is used rather than say =Sheet1!$A$1:$C$31 The reason for asking is that the number of rows that are in these Tables will vary quite a bit, but again I'm concerned with the speed of the operation.

Any guidence on the above would be appreciated as I'd rather spend a bit of time setting things up correctly rather than tinker around later on trying to change things.

 

[BrianB]

Just my 2 cents opinion ...
a. main sheet containing almost 65000 rows of data.
putting all data into a *single table* is good. Saves huge amounts of time.
Over 65,536 and you will have to use Access. However, users need not see the table. Can be manipulated from Excel using CreateObject. Keeps the workbook size small too.

1. Userform that is displayed at startup/buttons.
Depends if users expect to be able to open/close the userform. Could do both.

2. calculations need to run once data is entered.
Probably best.

3. worksheet with the form have to be visible?
No. But don't use Activate/Select. Use something like :-
Workbooks("Book1.xls").Worksheets("Sheet1").Cells(1,1).Value =Listbox1.Value

4. Application.Screenupdating in my macro's.
Only needs True if users need to see things happening.
Setting Application.Calculation to xlManual saves lots of time.

5. Is it best to use a combo or list box on a userform?
I generally prefer Combo for short lists, Listbox for longer ones.

6. =Sheet1!$A:$C rather than =Sheet1!$A$1:$C$31.
Generally not. Excel can waste time looking in the other 65,000 odd rows.
Probably better is a dynamic named range.
Insert/Name/Define and use a formula like this instead of a range
= OFFSET(Mysheet!$A$1,0,0,COUNTA(Mysheet!$A:$A),10)
Choose a column without blank cells. This one has 10 columns.
Adding data to the bottom of the table automatically adjusts the range.


Hope this helps.

 

[WorkMC]

Thanks BrianB, hopefully your suggestions will keep me on the right track. One question if you don't mind. Can you expand a bit on "create object" as I'm unsure what you mean by this?

I'll be asking more questions once I've started the spreadsheet. If anyone has any further tips that may benefit me I'd like to hear them.

Thanks again.

 

[BrianB]

Ref 'CreateObject'
Just check VB Editor Help & do an internet search. Loads of code samples around.