Your best Macro/VBA spreadsheet

[gordonf35]

I was wondering how far can Macro/VBA do as I find it is very powerful.

Does anyone have any example/ masterpiece of there own and don't mind sharing?

I am also struggling when to use Macro, when to use a proper Visual Basic. Personally, I think I only use Macro when there is a need to format a list of date and print it in a table format for user to edit it afterwords. It is more user friendly as most people knows how to use excel. Does anyone have any opinion on it?

Thanks,

Gordon

[venkat1926]

there is nothing as best macro. some are routine problems for which one's memory has templaes. some are new problems and you may have to spend some time on it.
my opinion is that many many problems can be solved by a macro provided the logic is clear.
best way to learn macro is to RECORD MACRO (DOING THE STEPS REQUIRED) AND look at the macro and get a feel of it;. sometime these RECORDED macros need to be edited so that the solution can be of general use.

you can also learn a lot from following the answers in this and other newsgroups or forums.

[mirassol]

On Error Resume Next

[Jacc]

My best macros are the ones that I stop updating after a while because they evolved into something really good.
Also, if people use them alot with little or no instructions long after you are gone I consider them good.

I understand you are fishing for examples of powerful monster macros and preferably code samples but I don't have much to contribute with. I am however curious about what have been created all around the world. For instance, has anyone used Shapes in a serious macro?
Most likely we will never see or hear about the real good stuff as it is considered sensitive company info but I'll make an effort and describe what I have accomplished.

I printed one of my biggest creations and it was 12 pages of code, many of them less than half full of course.
It compares data from one source, imported from a text file, with data from another source imported from a completely different textfile. Some of the data could be edited and saved back to the text file and imported back to where it came from. The application took up 7 sheets. In the final comparison sheet I wrote Excel formulas with VBA which gave an accurate description of how the data differed. It doesn't sound like much but it saves a huge amount of time and is very user friendly.

How far can macros go? Pretty far. Speed can be a limiting factor of course. As it grows bigger you come to a point where it just makes more sense to make a proper application in C++ or whatever. Or maybe it is something that should really be implemented as a database instead, I think that is quite common.

[gordonf35]

Thanks Jacc, I agreed on your point, the best macro are the ones that people does not need any instruction and they figure it out quite quickly. And I think it should also covers all possible error by people.

Yes, indeed, I wish to find someone to show me how can Macro goes. However it is really slow especially in web data extraction but it is brilliant on data base management. You don't have to build your own data base systems like pure program (e.g. C++) and people can instantly work on the data using excel.

[jayinthe813]

That's assuming you have the tools in front of you to work in VS/VB.net. Most businesses undoubtedly can get by using solely VBA, and it still is very powerful (especially units that do not have programmers on the call). Most of the macros I have ended up doing are just data manipulation (I mean it all is) but like rearranging and sorting through case numbers, etc. I am still in the process of learning the DOM object, which basically surfs the web for you and grabs whatever data you tell it to. It can also grab data from excel, input it into say, a company intranet, and save it. I like to automate redundant tasks using it.

Learning VBA isnt obsolete in business terms, but maybe so in programming terms. I have found juggling between VBA and .net to be confusing with the different syntax, and .net pretty unforgiving concerning objects lol

[Gabriel Star Co Uk]

Hi All, I'm a long time user of these threads and they have taught myself and my colleagues a lot.

Just to add a little - I've built some monster macros and an example of a complete scheme appraisal project model can be found on my website gabriel-star dot co do uk. (www.gabriel-star.co.uk)

It searches for average rents from a website and provides valuations of the land based on a post code put into a userform.

C.14,000 lines. 99.5% errors caught but it took weeks of simply de-bugging and catching exceptions.

Good luck to everyone - and if you work in the UK please do get in touch.

[gordonf35]

Hi All, I'm a long time user of these threads and they have taught myself and my colleagues a lot.

Just to add a little - I've built some monster macros and an example of a complete scheme appraisal project model can be found on my website gabriel-star dot co do uk. (www.gabriel-star.co.uk)

It searches for average rents from a website and provides valuations of the land based on a post code put into a userform.

C.14,000 lines. 99.5% errors caught but it took weeks of simply de-bugging and catching exceptions.

Good luck to everyone - and if you work in the UK please do get in touch.

Thanks. I had a look of your website and your videos. The format of your spreadsheet is truly amazing. I can see a monster systems is driving behind the scene. The use of excel allows a more user friendly system when compare with C++ /C# and Java. I wrote a stock trading macro with around 4000 lines. It took me ages to debug because too much combinations can be existed. I have to try different combination and limit users input to avoid the error. Still, I am not 100% confident to be bug free. Once again, thanks for sharing your work.

Gordon

[Gabriel Star Co Uk]

Thanks. I had a look of your website and your videos. The format of your spreadsheet is truly amazing. I can see a monster systems is driving behind the scene. The use of excel allows a more user friendly system when compare with C++ /C# and Java. I wrote a stock trading macro with around 4000 lines. It took me ages to debug because too much combinations can be existed. I have to try different combination and limit users input to avoid the error. Still, I am not 100% confident to be bug free. Once again, thanks for sharing your work.

Gordon

Hi All, the site has been updated. The video can be reached on you tube by searching for gabriel star housing association model demo 1