Solved: A better method?

**Aussiebear** · 08-23-2006, 01:45 AM

Given that workbooks can become quite complex, it it better practise to breakdown workbooks into groups of sheets that have a direct relationship to one another and allow reference to other workbooks?

I'm assuming that the benefits here are quicker to load, less memory intensive, and possibly easier to maintain.

Is there a general opinion about a reasonable size to a workbook/ worksheet?

And... does referencing a closed workbook cause many problems?

Ted

**mdmackillop** · 08-23-2006, 03:48 AM

Hi Ted,
If you need this structure, I think you need a Database!
Regards
MD

**Aussiebear** · 08-23-2006, 05:23 AM

LOL. YOu are keen toget me into Access, Malcolm

**Ken Puls** · 08-23-2006, 09:15 AM

Originally Posted by Aussiebear

Given that workbooks can become quite complex, it it better practise to breakdown workbooks into groups of sheets that have a direct relationship to one another and allow reference to other workbooks?

I'm assuming that the benefits here ... easier to maintain.

Not in my mind. We have a budgeting deal here that has been segregated into several workbooks. (It was not designed by me.) It is the most cumbersome thing to maintain, as there are links everywhere. If you want to change something in one, you need to check all the others to make sure they were unaffected.

I'm not saying that the design on our was bad, but I wouldn't approach things as thinking this will make it easier to maintain. The fact is that to me, it usually makes it harder. Things aren't in one place anymore.

**Aussiebear** · 08-24-2006, 01:50 AM

Thank you Ken. I shall bear your advice in mind.

**Bob Phillips** · 08-24-2006, 02:29 AM

Ken's advice is very sage, but I would just add that if you are going to use Excel for the data, it is a good idea to separate the data and logic (code) into separate workbooks, maybe using an XLA.

**Aussiebear** · 08-24-2006, 02:30 AM

Bob, you are out to confuse me!

**Bob Phillips** · 08-24-2006, 02:35 AM

No, I am trying to help, honest!

What did I say that is confusing you?

**Ken Puls** · 08-24-2006, 08:19 AM

LOL!

I agree with Bob. Basically what he's saying is that if you are using code to manipulate your data in any way, it's sometimes best to hold all your code in an Excel Add-in (XLA). This means that you can easily port it a different data set.

**Aussiebear** · 08-25-2006, 07:46 AM

Ken, Bob's XLA bit. I'm in over my head just with VBA let alone building and adding an XLA.

**Aussiebear** · 08-25-2006, 08:01 AM

Bob, It doesn't take much to confuse me. 12 months ago I didn't know vba even existed, then one day something clicked and its a whole new ball game out there.

Just in the last couple of days, I was asked at work if I could come up with a better method of recording timesheets and some of the subsequent calculations that arise from this data. Given Malcolm's earlier advice, now I'm torn between record keeping (Access) and Calculations (Excel).

And just for the record... I am tryng to dodge the Access bit... for now. (Sad case aren't I?)

**Bob Phillips** · 08-25-2006, 08:15 AM

Originally Posted by Aussiebear

Bob, It doesn't take much to confuse me. 12 months ago I didn't know vba even existed, then one day something clicked and its a whole new ball game out there.

Just in the last couple of days, I was asked at work if I could come up with a better method of recording timesheets and some of the subsequent calculations that arise from this data. Given Malcolm's earlier advice, now I'm torn between record keeping (Access) and Calculations (Excel).

And just for the record... I am tryng to dodge the Access bit... for now. (Sad case aren't I?)

Personally, my opinion is that Access is a waste of time for timesheets. I would create a timesheet template, and and basically just use formulae. If you need VBA then either bind it into the timesheet template, or create an xla (probably overkill for timesheets).

**Ken Puls** · 08-25-2006, 08:26 AM

Totally agree with Bob.

The nice thing about going the template route is that you can always add the XLA later if you need to. As your coding skills become better, you will take that plunge.

Templates are a wonderful utility in Excel though. And for timesheets, they are perfectly suited.

**Aussiebear** · 08-25-2006, 10:05 AM

But if I wanted to record timesheets for a number of employees, apart from some calculations, is this not a record keeping function?

I have started a new thread in relation to this issue and attached an example of what I have been asked to do.

**malbec** · 02-29-2016, 04:13 AM

Originally Posted by Ken Puls

LOL!

I agree with Bob. Basically what he's saying is that if you are using code to manipulate your data in any way, it's sometimes best to hold all your code in an Excel Add-in (XLA). This means that you can easily port it a different data set.

Yes, I agree too! I think it makes maintaining much easier and, above all, distributing new versions to users a breeze...
What advice and recommendation would you give to achieve a development with this goal in mind?

Thank you for your help!

**Bob Phillips** · 03-06-2016, 07:23 AM

I don't understand, f you agree with Ken and me, that suggests that you understand the approach, so what advice are you seeking?

Thread: Solved: A better method?

Thread Tools

Display

Solved: A better method?

How to separate code and data

Posting Permissions