Creating application for others to use in excel

[hungledink]

Hi everybody.

Does anyone have any guidelines on steps they follow for excel based projects they create?

I occasionally make workbooks for others to use at my work. I've begun to get better at writing VBA code to make these workbooks effective, although l find it difficult to structure the creation side.

I often lose track of where I'm up to with things and things take longer than they should to complete. And I never seem to finalise a sheet, something is always breaking that I forgot to account for.

Any guidance would be appreciated. I've looked online but can't really find any tips.

 

[Logit]

" something is always breaking that I forgot to account for. "

Can you describe that in different words ?

 

[hungledink]

I'm essentially looking for a step by step process, so you can create a workbook and have a plan to follow. Everything I do currently is just what occurs to me at the time I'm doing it which coupled with my limited vb knowledge, causes things to 'break' (which I know cant be avoided) when its being used.

Some examples of how things 'break' are:
There will be vb errors that users will encounter where I should have put error handling in to catch.
There will be functionality that I've forgotten to put in.
There will be something that I've added late in the project that causes another element to stop working.

I'd like to know how other people structure working on a VB/excel project. Whats the best way to approach it.

 

[Logit]

.
I'll assume your overall VBA experience is limited / new based on the total number of posts you have on this Forum (total is 75 at this writing). So .. as you gain more experience which is built on time and hands on creations, the "building process" will be easier and more productive without having to rectify errors.

Suggestions:

If you find the errors you commit are being repeated, make a list of those for you to review - when you think your project is finished. This will help you avoid those embarrassing moments realizing you left something out.

In similar fashion, make a list of other items that should always be in your projects and review those periodically as you progress in the building process.

Some coders write a 'blue print' of what the project is and how it should be approached step by step. Then they follow that blue print. I would presume that to be helpful in the beginning but as you gain more experience in coding, that blue print will become more of a hindrance.

#####################################

Right or wrong, I think of the project in overall terms first. Sometimes I'll write down reminders, most times I don't. Personal preference.

I will concentrate my efforts on a single aspect of the project ... making certain that single portion function as desired ... then I move on to another part of the project.

Throughout the process and even when I believe the project is complete ... I test, test, test and test some more .. all parts of the projects. More often than not it is during this segment of my efforts that I identify errors that did not appear earlier.

Before and after coding I think of the project from the User's viewpoint. If I have to use this project day in and day out, repeatedly ... how can I make it more enjoyable to use ? More visually pleasing and
easier to navigate with the fewest steps ? Always ... ALWAYS leave out the juvenile colorings, excessive buttons, 8 steps to a goal when 2 steps is possible, etc. What you are producing is a professional tool, not a video game.

If you are the sole coder / producer of projects ... then you are responsible for creating the best you can which requires forethought, perseverance and repeated testing before the product is delivered.

Experience will make thing easier, faster and less prone to errors.

Hang in there. Hope this helps.

 

[Legacy 456155]

There will be vb errors that users will encounter where I should have put error handling in to catch.
There will be functionality that I've forgotten to put in.
There will be something that I've added late in the project that causes another element to stop working.

Besides the first one, that sounds like the project I'm working on now and also one I worked on 20 years ago. That's just part of development. There are methods, guidelines, tomes, and entire philosophies available to glean from to help make the list of the issues you posted more manageable. There are none that will remove all of them because none of them can take the human out of you. Here are a few basic guidelines in no particular order.

• Have fun. Development is an enjoyable, creative process if you have the attitude to see it in that light.
• Keep your methods/procedures as small as possible. Most procedures should accomplish ONE thing and take up a screen or less. Packing multiple tasks into a single procedure is a debugging and maintenance nightmare that is common with beginners.
• Whatever development tool or language you are using - get to know it by reading through the documentation methodically. Else you will find yourself learning in response to crises instead of avoiding many of them to begin with. Sort of like a carpenter who runs to Lowes half a dozen times a week instead of taking the time to plan ahead and know what the job takes. You will also find yourself with multiple, creative approaches to solving problems and reusable code. Developing according to learn and live alone is a bad idea. Learn first and as you go. Then your code will live much longer. Determine a naming convention ahead of time and stick with it for the scope of your entire project. How you name your variables, controls, and other objects is up to you though you should stick with one of the standards that are common with most developers. Nevertheless, it's a naming convention, not a life or death decision or a religious ceremony. To some, it's sin to vary from their way. I just avoid people like that because they suck.
• Determine the basic purpose of your application without the bells and whistles. Scrap paper usually works for me, but there are fancy tools out there for larger projects. If you develop systematically, adding the bling later won't bust everything.
• Learn to code in such a way that your code describes your intentions. Use variables that are descriptive. Name procedures in such a way that you know what the primary purpose is. Of course, this is not always possible or practical. In that case, if there's any ambiguity, use comments. That's what they are for. If you fail to invest the time now to communicate what the code is supposed to accomplish, I promise you that you will spend plenty of time later trying to figure it out.
• Handle errors systematically and thoroughly before they happen. You are not even at beta until you have handled every method. Unless you are certain of the call stack for a specific procedure, it should contain its own handler. Log your errors. Add line numbers for more complex code if necessary, logging the offending line. There are third-party tools out there though I don't use any of them for VBA except RubberDuck. MZ Tools is a popular one it seems.
• Down the road, if you continue, learn how to build and consume classes, user-defined types, and operating system API functions (there are thousands of them).
• Of course, there are many more suggestion. These are just the ones that came to the forefront from my experience.

Before and after coding I think of the project from the User's viewpoint. If I have to use this project day in and day out, repeatedly ... how can I make it more enjoyable to use ? More visually pleasing and
easier to navigate with the fewest steps ? Always ... ALWAYS leave out the juvenile colorings, excessive buttons, 8 steps to a goal when 2 steps is possible, etc. What you are producing is a professional tool, not a video game.

I appreciate your perspective. My favorite part of the entire process is bringing users and the business model together in the most agreeable way. Users are not obstacles, but a source of new ideas and fresh ways of seeing things. A friendly developer knows the difference between "idiot proofing" and "user friendly." Anyway, that's another topic.

Have a good one!