PDA

View Full Version : 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

mdmackillop
08-23-2006, 06:10 AM
LOL. YOu are keen toget me into Access, Malcolm
Not really, just keeping your mind open to the possibility! Size of workbook is not a problem. The one I'm currently working on is 18Mb, but it's not too Relational, and need a different functionality. If you're record keeping, as opposed to mainly calculating, that is when the use really changes.

Ken Puls
08-23-2006, 09:15 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 ... 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
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.

mdmackillop
08-25-2006, 10:50 AM
Hi Ted,
Your talk of complex workbooks and relationships made me suggest Access. For recording timesheets, I agree that Excel templates will serve, and that's what I made up for my office staff. I did however create an export module to write the data to a database for analysis.

malbec
02-29-2016, 04:13 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.

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?