Excel is already exciting, and then you add macros or VBA (Visual Basic for Applications) into the mix and WOAH! When I started using Excel VBA with regularity, it seemed like the right VBA code would allow me to see into the future.
I’ve used VBA for
- Web-scraping
- Parsing data in ways that wouldn’t have been possible through worksheet functions
- Automating many types of reports and processes for clients
- Building interfaces to ease data entry
- Creating forms
The power that’s available is almost frightening. I’ve used VBA to break the passwords on other spreadsheets. It’s seductive to start thinking in VBA and head straight to the VBA editor with every task. But wait a second!
There are good reasons to avoid (or at least be cautious about) using VBA. Here are 6
| 6 | Sometimes writing, testing and debugging the script will take longer than using worksheet features. |
| 5 | VBA does not adjust in the way that formulae do when you move data from one worksheet to another, insert a column, delete rows, etc. Example: you have a sheet called February. Now you want to rename it Feb. A formula will adjust. VBA will not. The VBA code has to be manually modified, or you just live with the sheet being called February. |
| 4 | Some users freak out when they open the workbook and see the security warning about enabling macros. |
| 3 | There is no UN-DO! This bears repeating: There is no UN-DO! If the macro is designed to delete information and you run the macro at the wrong time or, the code is wrong and deletes work you spent hours creating … [insert expletive]! You’ll look up and see the UN-DO arrow grayed out. |
| 2 | If you don’t use good comments in your code, you’ll struggle to understand your own work if you have to revisit it weeks or months later. |
| 1 |  Truly, the Numero Uno reason for avoiding (or limiting) use of VBA:THINK ABOUT THE USERS Many MANY users aren’t aware of VBA or they don’t understand it. So, if the VBA-happy developer quits to go chase other dreams in a foreign land, it’s going to be an added challenge for the users to get the document modified or repaired. Most folks do know an Excel wiz in their life. But their Excel wiz may not be familiar with VBA AND have the wherewithal to do the forensics on someone else’s code. Thus, some users can feel like they’ve been abandoned to poke around in a dark alley called The |
Excel VBA is splendid. I set it to run a task that took 48 hours, while I went on with my life and occasionally checked to ensure that the job was still running. My only warning is that VBA is like sriracha: you can over-do it. (Well … it’s harder to over-do sriracha, but that’s another discussion.)
Does anyone else have comments, questions, warnings or stories to share about VBA?
UPDATE (3MAR14) Check out 3 Reasons To Use VBA. I am actually a VBA fan
@kassie Kassie, thanks for the reply.
The point in the blogpost is to just be cautious. VBA is a wonderful tool. And it sounds like you built a great tool for a user who’s comfortable with it. I’ve done the same, and continue to do it. I’m starting a project this week that’s going to be heavy in VBA.
There are just warnings that go along with VBA–especially for developers who are new to VBA. The main point is to consider the user and if they are going to try making tweaks to the document. If so, there’s risk that they’ll break the code. The developer then needs to build in as much validation as possible in anticipation of possible corruption.
I know this is a very old thread, but Oz, This was a great post. I agree with you completely from a user point of view. VBA developers are often completely different from the users that their code will benefit. I am exploring the option of using VBA to streamline a filtering process for administrative duties and the one thing that keeps popping up is the idea that once I have moved on from this short project, the administrators will not have the ability to fix contingent issues or recover the code if it is lost. Sure, they will save a LOT of time using my solution, but I am not going to be here for long. I am reluctant to use a VBA solution, even though I am not rendering it as a “product” or “service” in my employment. I am not even part of the IT department, but they have yielded no solutions to the problems faced by the department I am working in. So, it remains to be seen whether the guidance I author entails a tutorial on building my code again (for non VBA users) or simply instructs on the use multiple XL functions in sequence to achieve results.
All of these warnings besides reason one is dumb…I could even argue number one is dumb…
6. Either the task is simple AKA doesn’t require a macro…or it is repetitive, in which case a macro would save time in the long run.
5. You shouldn’t be using the sheet name, you should be using the vba sheet name “Sheet2” instead of “Sheets(“February”)
4. Tell them to get over it or get a security clearance for your macros.
3. You should have warnings when a macro is about to delete something.
2. Legit – but comment your code correctly
1. Even if a macro breaks after the guy leaves, think about all the time saved when it was working!
@RStadther Quite brazen of you to come onto someone’s site and call something “dumb.” Any way …
It sounds like you see my points and you offer excellent solutions. But did you see my statement about “OR AT LEAST BE CAUTIOUS”?
This post is just a warning, particularly for people new to VBA. It’s also a warning for certain developers who go straight to VBA code and make it difficult for anyone who needs to use or understand the document.
As a freelance developer myself, I don’t think it’s cool to tell a client or collaborator to “get over it.” I can review a project and conclude that there is no other solution without VBA or, a VBA solution is more efficient. But “get over it” isn’t an option.
You aren’t saying anything incorrect, and neither have I. I do appreciate the conversation. However, you’re certainly unnecessarily rude, and missed that this blog post is about CAUTION.
I am sorry to pull out things from the past. But I am with your points OZ. As I am also a VBA FAN but using VBA as a full-time tool is not a good deal. You can use it as a time saver tool but not as a task saver.
Can you explain more on what you mean?
So it is like “be cautious while driving, you may get hit or get run over”. It doesn’t mean to stop driving altogether.
YES YES YES!!! Thank you.
Then you should have titled your blog correctly to something like “Precautions to doing macro/VBA…”. Don’t say “Not to use” those have two separate meanings.
Guess what. You’re right, and here’s why.
Lol, I wouldn’t say dumb, but all the items to be cautious about are valid and good guidance but are no deterrent to me.
I think the statement about warning of overusing vba is true though. My grandpa once told me when I was a kid, “simplicity is the enemy of the best”. And with that in mind, in just the same way that a simple in-house vba solution will negate the need to pay exorbitantly expensive SAP consultants, sometimes too, a normal spreadsheet is just plain simpler than a vba workbook, no matter how much you love vba.
Good article
Ah!~ MAN! if there are 6 reasons you don’t like VBA, there are hundreds… maybe billions of reasons why you should love VBA. I’m still using my very first Macro I created 10 years ago (simple shortcut for paste value only – yeah, simpler than ALT+ES) It must have saved me hundreds… maybe billions of hours. Oh Oz of the Sun, you have hurt my feelings
Samii
Samii Samii Samii, I didn’t mean to hurt your feelings. And I think you’re quite accurate. With 6 reasons to be cautious about VBA, there are indeed billions of reasons to love it. I regularly use VBA just to clear forms so that the user can start fresh. VBA can definitely be a time saver. Actually, more people should be experienced in VBA.
The blogpost was just a caution. Just some things to think about before we open that VBA editor. Excel power users are a small bunch. VBA developers are even a smaller crowd. I’ve seen pedestrian users in bad situations because the were given a VBA solution that either stopped working or needed to be modified, and the developer was long gone.
Sometimes the solution could have been non-VBA, sometimes not. I just want us to be cautious.
Thanks for the response. I am content now
being the Oz of the Sun, what do you think the future if hiding for VBA? I have heard that MS is going to discontinue VBA and replace it with VSTO… I feel like a milk man, where the only programming skill I have is VB
Samii
Bruce McPherson (http://www.mcpher.com/) is a great one to look to. He, and others, suggest that JavaScript (JS) is next.
And I think Rob Collie (http://www.powerpivotpro.com/) is one who sees Visual Studio as a direction we should be headed.
With Bruce, JS is already in use in efforts to truly integrate Excel with the web. ExcelWebApp is an excellent start from Microsoft but it’s still lacking. My point being, JS isn’t being thought of from a worry that VBA is going away but as a way to “liberate Excel from the desktop” because so much is web-based. VBA or no VBA, the fear is having your work/apps isolated on your own little desktop/laptop island.
Is VBA going away? There have been warnings about that for a while. In fact, Excel for Mac 2008 didn’t have VBA and people were pissed. So, it was returned in Excel for Mac 2011.
So, what do you do with Excel/VBA? How’d you get into this?
hirandesilva please share your thoughts? You’re an Excel visionary. How would you reply to Samii?
Hi Oz,
You know I love you man but I’m with http://www.livefyre.com/profile/10159818/ on this one. I agree there are times when VBA should not be used so I’ll start with that:
#1 Don’t re-invent the wheel.
The normal VBA novice starts out doing everything in VBA. Later they learn how to incorporate native XL functions in VBA (application.worksheetfunction.whatever). Lastly, they learn that if native XL can do it, it’s probably best to not use VBA at all.
Hmm… pretty much, if VBA coders learn rule #1, we stop doing all other unnecessary things – including the intent of your list. For example
#6 goes away completely.
#5 RSadther addressed this – VBA should not use the worksheet’s name but its codename – and then users can rename the sheet whatever they want
#4 If it couldn’t be done in native XL and VBA was used, who cares if users freak out. It was necessary. Deal with it. But if they can’t, store it in a trusted location and users won’t see that warning again.
#3 See http://www.jkp-ads.com/Articles/UndoWithVBA00.asp, Jan Karel Pieterse wrote an UNDO. Besides – UNDO is for PEOPLE who do unintended things. When people do unintended things, we call it “being human”. When code does unintended things, we call it “defective”. Can’t call people “defective” these days though I know some. Fortunately, I can fix defective code. Defective code doesn’t get all twitchy when I tell it “I’m gonna fix ya”. But I digress…
#2 Huh? Not sure that one belongs in the list of why not to use VBA. Good advice though.
#1 Precisely. Think about the users. Once again, if native XL couldn’t do it then the only way to meet the users’ needs is with VBA. In that situation, users are always BETTER OFF for the developer’s efforts.
That argument “What if they leave?” has always rubbed me wrong. It should NEVER be used for ANYTHING. Nobody is so good that no other developer can figure it out or recreate the solution. And if a developer is producing solutions so valuable that the company can’t work without him/her, then the company should offer the developer stock options to entice them to stay – or maybe say thank you once or twice.
Stay thirsty my friend and code responsibly. Remember, friends don’t let friends code decaffeinated.
CHatmaker Thanks so much for jumping into the conversation. I haven’t heard form you in a while.
You see that even I agreed with RStadther–excel for coming onto someone else’s turf and calling something “dumb.”
Referring back to the “or be careful” piece in the blogpost title, I think we all agree with each other. There are a lot of reasons to be careful about using VBA. In no way do I suggest VBA is the devil.
#3 is a warning because there’s no UNDO. Yup. We’re only human. And every time I get ready to test some VBA I get everything set up in case the VBA code is wrong, as a last resort, I can close the document, “do not save”, reopen, and be back where I was before running the wonky code.
As you, I and RStadther agree, there are ways of dealing with all the #2 thru #5 things that I mention. There is “responsible coding.” And there’s a helluva learning curve to get there.
With #1. I fully agree that we shouldn’t refuse to do something just because someone might leave. My warning is that freelance VBA coders don’t grow on trees. I’ve seen people whose spreadsheets have been broken for years because the VBA coder is long gone, something broke, and the spreadsheet skill kinda works but not like it did. And there are 2 problems:
1. Finding another VBA coder who can take on the project of fixing someone else’s code.
2. Coders all write in their own different styles (not only in VBA, but any kind of code: CSS, Java, Python, etc.). So, a developer following behind another developer means following someone else’s code, or re-writing in one’s own style. It’s not the end of the world, but it’s an added challenge, and unnecessary grief if a native solution was possible.
I agree with you 100%: if native XL can’t do the work, definitely go the VBA route. New VBA developers, need to be careful, though. And I think that some VBA developers overuse VBA and make things unnecessarily hard for users.
i challenge VBA User
i using excel for Data processing from last 7 years and i dont know anything about VBA macros
i never felt any need to use those macros during my work as i always uses excel built-in-functions and formulas for my work
and i am happy to use those functions
excel built-in-functions are much sufficient and useful to complete any excel task but only if you have a cleaver mind to use of them.
I appreciate the sentiment, as I know I am guilty of using VBA at times when I don’t strictly have to – when you work with excel for a living, you do what you know works and works the first time.
BUT I would appreciate any references to books (or whatever) on the subject of using native Excel functions for “Data Processing”.
Thanks for commenting. I can identify with going with the tool that you’re strongest with using.
One resource I’d suggest for that type of overview you describe, is my book Guerrilla Data Analysis 2nd Ed. The book is written in bite-sized chunks covering a lot of Excel functions and features.
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336/
YakshChauhan Thanks so much for commenting.
This blogpost was meant to be cautionary. There are good reasons to use VBA. But that also depends on a person’s needs. I wrote a blogpost in support of using VBA.
https://datascopic.net/vba-tames-beasts/
Some things just can’t be done using native Excel. For example, I used Excel and VBA to scrape 29,000 web pages. Excel ran for 4 days by itself. Previously, my client was manually getting just 30 pages’ worth of data per day, with no end in sight.
Still, I like your attitude that there’s so much to be used in native Excel before going to VBA.
kassie, I replied to you 3 years ago, and you’re back with with a comment that suggests you didn’t read my response.
In many cases, the lack of Undo can be obviated by simply closing all other files and saving the file(s) to be operated on by the macro, before launching it. I always do this. If I get a weird result, I just close without saving. If a macro involves changing and saving a file, save it somewhere else first. If it involves changing and saving many files, try to keep them each small and noncritical.
Gotcha! That’s an excellent solution, Rob.
Ok Oz.
So I should avoid using VBA.
Then what should I use instead, if I’m avoiding it? All the automation and scripting that gets the job done?
If you have a better solution that is preferable, instead of using VBA, please share.
Thank you
The article is just 6 reasons not to use VBA. I also wrote an article on 3 Reasons to Use VBA.
There are plenty of reasons to use VBA. As I responded to other people who’ve complained about the title, it was just a provocative warning. That’s all.
So are you ready for a comment six years later? I actually found this blog subject while searching for a more insidious undo problem. I’ve got a simple macro that highlights a cell, using conditional format, when the cell is selected. Excel doesn’t do a good job of that, so sometimes it’s tough to find a cell selected by the Find function. Anyway, I found that using a Worksheet_SelectionChange macro destroys the Undo stack for that worksheet. So I can’t undo even simple mistakes made manually. If, for example, I accidentally delete the contents of a cell, unless I have a good photographic memory of what was there before I made it disappear, that change is lost forever. I can maybe restore from the previous save, but that’s my only recourse.
I’ve got a http://www.slipstick.com macro that saves an Outlook item(s) to docx, I changed the pdf output source as instructed. I have added 3 error handlers. 1st to trim the email From and Subject in the filename if too long. I have added two more secondly to reduce embeded image resize to margin width and height (Macro s). And thirdly to change the output docx to A4. Strangely these macros are available in Undo, as the Output is opened,if Word App is open. So you can get undo in an output file.
In my opinion, what’s wrong with you is that you haven’t gone deep enough. The problems you have had, I have faced and overcome; that is, these for me do not exist. So look for other pretexts to stop using Excel VBA.
What’s wrong with ME? C’mon. As I’ve said over and over and over. This was a cautionary warning. I wrote a blogpost on reasons to use VBA.
>6 Sometimes writing, testing and debugging the script will take longer than using worksheet features.
Usually the point of using VBA is to automate worksheet features, not do something INSTEAD OF them. Of course it’s true that you could waste time making a complex solution when there is already a built in tool to do the job, but that comes down to the person’s knowledge of Excel (or whichever app you would be automating with VBA). That’s a call that has to be made for ANY software development: whether enough time/effort can be saved by writing code to justify the effort. That said, the more experienced you are in VBA or coding, the quicker you can turn out a reliable solution. So this really depends on the problem and the judgment/skills/experience of the people involved.
>5 VBA does not adjust in the way that formulae do when you move data from one worksheet to another, insert a column, delete rows, etc.
If someone just records a macro and expects it to work the same way on differently formatted sheets, etc., then sure, it won’t adjust. But it’s not hard to go in and edit your macro to be smart enough to work more universally, I do it all the time. (Also, recording the macro with relative references can help.)
>4 Some users freak out when they open the workbook and see the security warning about enabling macros.
Just give your users a heads up about what they’ll see! Simple communication or documentation can’t hurt.
>3 There is no UN-DO! This bears repeating: There is no UN-DO!
First rule of computers, save your work often! If I’m working on something it’s not uncommon to save “my workbook (ver 1-00).xlsm”, then 5 minutes later “my workbook (ver 1-01).xlsm”, etc. At the end of the day I might have 50 versions. Until I’m comfortably at a stopping point I can keep them around, then zip them up or delete when no longer needed. How many times have we had Excel/Word/etc. blow up and autorecovery fail to resurrect your changes? Sadly in this day and age computers still crash, what can you do?
Second, good programming design is to non-destructively edit the data. If you want to delete rows and have some method of rollback, maybe instead of deleting, have your macro create a copy of the worksheet with the rows you want to keep or make whatever changes you want there. Just plan ahead, and design in such a way where you don’t lose data. The macro could automatically save a backup copy of the workbook before proceeding. The macro could automatically make a backup copy of the sheet it’s changing. There are a million ways around this problem.
> 2 If you don’t use good comments in your code, you’ll struggle to understand your own work if you have to revisit it weeks or months later.
That’s true of ANY programming language. That’s true of Excel itself – if you choose cryptic names for your column headers, named ranges or sheets, you’re just going to regret it later. This holds for anything involving computers, processes, or even paper files – if you don’t document what you did, or label it clearly, you’re going to end up confusing someone (maybe yourself)!
> 1 Truly, the Numero Uno reason for avoiding (or limiting) use of VBA:
> THINK ABOUT THE USERS
> Many MANY users aren’t aware of VBA or they don’t understand it.
This one is silly. That’s like saying, many users don’t understand C++ or assembly language, so don’t write them software applications? Of course, when designing ANY software or process, we should consider the users and user experience. A well designed application or macro comprehensively prompts and displays status to the user, catches errors gracefully, and manages their experience overall. Most users (including VBA programmers) aren’t aware of all the junk that makes Excel or Windows tick under the hood, but that doesn’t stop us from being productive with them. Sure, we all have had nightmare experiences with poorly designed software that isn’t user friendly and ends up wasting our time. The job of a programmer is to foresee scenarios like this and design for it.
For all the bad press it’s gotten from people who use other languages, VBA is still a great language for rapid development.
If anything, I’d like Microsoft to support VBA more vigorously – keep updating the IDE to keep up with the advances in Visual Studio (and provide some way to handle versioning of the modules/code/forms), and make it available for writing general system scripts (and GUI applications!) for Windows.
As is, the VBE editor certainly isn’t any worse than say, Idle for Python. Little things like the freeform Immediate Window are really very useful, more so than the “command line” format of Python and Powerhell.
VBA is like any other language in that programmers’ bad habits or cutting corners (like not commenting code, writing spaghetti code, choosing cryptic variable names, etc.) can cause headaches down the line.
But at the other end of the spectrum, programming using extreme OOP design patterns can cause headaches too. Sometimes simple procedural code is better for simple tasks, and unravelling the equivalent “correctly designed” but cumbersome and complex object-oriented solution can be more difficult than working with novice’s spaghetti code.
One feature that MS should add to the IDE is a simple “programming language” dropdown, to change syntax between VBA, JavaScript, Python, maybe even AutoHotkey, and it will just change the syntax to whichever language you are most comfortable with (preserving comments & variable names of course). Both Python and JavaScript support the multi-paradigm (part OOP, part procedural) code that VBA uses, so it’s not like converting the syntax on the fly would require any heavy AI refactoring or anything.
Have you read my responses to this?
Especially my Medium article.
#1 is not silly if you consider the point I’m making.
Often we build spreadsheets for other people, and Excel is unique in the way that users often need to make modifications to calculations, the layout, etc. Also, most Excel users aren’t programmers. So, it helps to be in their world.
If there’s a non-VBA solution for something, that will empower more users. And there are times when there is no avoiding VBA. In those cases, fine. Perfect. Use VBA.
VBA has grown to be a considerable addition to the Excel and indeed the Office Suite. I’ve utilized many of the Dot Net Framework via Excel that has added not only functionality into an Excel solution but considerable safety measures that would simply not exist utilizing Excels built-in native worksheet functions.
That said, the entire point of “the babysitting” mentality that is stripped from VBA is its beauty. Once you ‘break-out’ into VBA you are responsible for ensuring you follow the necessary programming and testing guidelines much like any other programming language. You cannot simply call this a ‘problem’ – by nature it’s an advantage. Tanks are harder to drive in cities comparatively to a small car, it doesn’t make the small car ‘better’.
I’ve responded to this in a Medium article.
You big dopies. You always always make a copy of the master sheet and then apply the macro on the copy so that you can compare the original to the modifield sheets. That way the undo issue is a nonissue. Also you can have a prompt box to open at the beginning of the macro to ask the user what sheet name they wish to apply the macro towards so if you change the name it doesn’t matter. Sheesh vba excel is the best.
Did you read the article and the responses I’ve made over the past FIVE years?
Hi Oz, you are one the few from the few. Lol, I mean, VBA is like feelings of being the software engineer without being. Well, I already developed a high-end application on Excel VBA Framework. here is the link to the visual presentation.
https://www.youtube.com/watch?v=12Z2dbB2pd0
I have an Idea to develop larger applications from Excel VBA frameworks without any glitches and flaws.
According to me, whatever the concept I have in my mind regarding software development is following.
~ The software is a combination of following three things.
1. Database (Where Tables and Data Actually Exist)
2. Printing formats of Reports, (Like, Invoice Voucher, Ledger Reports, Balance Sheet, Monthly Sales Report etc…)
3. Programming (Where Modules, Class Modules, Userforms, and Worksheets Coding actually exists )
So, I’m planning to make an ERP application for the general purpose of creating three Workbooks and each of will have above-mentioned responsibilities.
One Workbook will be named as DB or DATABASE_APPLICATION_NAME
Other workbooks will be named as RD orREPORT_DATABASE_APPLICATION_NAME
and the Last one will be named as PM orPROGRAMMING_MODULES_APPLICATION_NAME
ADVANTAGES OF ABOVE SETUPS:
1. The user interface quite remains undisturbed because of the cache size of the native file.
2. We are pulling data from the other workbook, which makes it more classified.
3. If we want to update an application anytime in the future… We just need to replace the one or two files. No need to setup everything.
4. Else if we make exe of the application, the user can able to save only working file. Not data.
5. If we put that data file in the cloud, the entire application will be real-time.
Please let me. know what do you think on this.
This is an interesting thread.
Once you get past the deliberately provocative headline (click-bait LOL) it has generated some insightful comments (as well as some less insightful from people who appear to have missed the point of the OP). I would say it is fairly representative of any internet arena where visitors are able to get involved: the signal-to-noise ratios seem to be universally low, but if you look hard enough you may be rewarded with a nugget or two.
Much of what has already been posted – including the original post – is common sense and good advice, and doesn’t need me to rehash it.
What I would add is this: in evaluating whether a certain situation lends itself to a VBA solution, development time, code maintenance and usability are some of the considerations of course, but so is accuracy and time-saving.
Recently I have implemented two VBA scripts for the company I work for: the first is a button that PDFs a purchase order, creates an email with the PDF attached and the body text, ready to send to the supplier. Not rocket science but the users LOVE it! Secondly, I spent a morning writing some code that allowed a user to highlight several rows of data and combine them into one (doing some calculations along the way). While this would not have been viable on a small dataset, the dataset involved in this case made it viable, and while the time saving overall was possibly only a few hours, the accuracy of the output made it more than worthwhile.
Anyway, great thread and keep up the good work!
BEAUTIFUL!!!! There are great uses for VBA and you cite 2. Overall time-saving and increased accuracy. YES!! And users do love it when you can strip out a lot of manual work, and then re-work to go back and clean up. VBA, yes!
I have to admit when I first read this I thought “who does he think he is / obviously doesn’t know what he’s talking about” – #5 made me wonder why you wouldn’t use the sheet codename to avoid sheet name changes. Having said that I can see you’ve written a book with Mr Excel so there’s no doubt you know what you’re talking about (and were just click-baiting us).
I definitely agree with #1 – think about the users.
With about 20 years of VBA experience under my belt I think/hope I can predict any random act the user may decide upon – still haven’t figured out the “copy over Excel file with blank workbook” problem though.
Is there any way to get the users to think about the coders? I’ve lost count of how many times I’ve been asked to create “what I think’s best”, or had to update my specs from “this column only has numbers” to “this column only has numbers except when the user decides otherwise”.
Darren, hello!
Yes. This has been a wild ride with this blogpost. I’ve learned my lesson about click-bait. LOL!
You ask an interesting question about getting users to think about the coders. I had a situation with a massive VBA project where I’d be working on it, and I’ve got all these pieces and I’m testing if they’re ready to be put together. Then I’d get a call, “Can you give us a working model so that we can demo it tomorrow afternoon?”
I learned how to say “no.” After I pulled overnighters to give them working models, and then spend the next day pulling things apart so that I could to the real work … no. No.
I think it helps when someone on their team has even a vague sense of the developer’s world. That can be the liaison or insider who can help with expectations and requests.
I learned that when I was working on a project for a small company and the person who had some sense of data was laid off. Suddenly, I had to deal directly with the people who hated data and didn’t understand certain nuances. For example:
There’s a difference between NY, New York City and NYC. Pick ONE. But if my solution has to check for all these variations, then it’s going to take longer and be more expensive.
Summarizing: «You need to be good at it to use it professionally».
That’s an interesting summary. I can go with that. LOL!
For what it is worth I have written a lengthy VBA routine that produces a Daily Report for my company. If I were to produce it without VBA it would probably take a couple of solid days’ work which is clearly unworkable. To this extent it is essential to be able use VBA if this report is to be available on a daily basis.
The downside is that I am well over retirement age and I have pleaded with a number of younger colleagues to allow me to train them so that they can continue to support this and loads of other VBA routines that I have written – but thus far the interest or time availability to take up my offer is, to say the least, limited. I am not keen on being such a key person in my company but I am very fearful as to what will happen when I finally retire. I feel this sense of responsibility that keeps me from retiring but the time is bound to come when I can no longer be in their employment – nature does eventually take its course..
Hopefully you’ll be able to teach the youngsters the VBA and retire. A lot of Excel gurus hate being in that role–as you describe.
LOL! I’m 68 and the VBA guru in my department. Yes, the youngsters are in awe. Yes, they love my work. Yes, they are degreed chemical engineers. BUT, their eyes glaze over when I challenge them to learn a little bit of code.
I found this thread because I’m worried that I might become irrelevant, being stuck in the VBA world. I’m writing highly complex process simulations in Excel/VBA, with VBA-generated graphics (flow diagrams and Gantt charts) as well as Excel charts (with VBA-modifed parameters). VBA is, perhaps, 10% of my job in terms of my time and contributes 60% of my job satisfaction. But, as pointed out in the thread, there’s a danger that my work will be obsoleted if a) VBA is supplanted, or b) I can’t find anyone to train to take it over.
So I’m thinking of learning a different language – Java or C# perhaps – with handshakes to an Excel I/O model. I want to leave the workforce due to poor (i.e., non-existent) health in 10-15 years or more, not because my knowledge is no longer valued.
Sorry for twisting this thread – and I see that I’m already 10 years late to the party which already might say something about VBA’s durability so maybe I shouldn’t be concerned – but I wouldn’t mind some coaching on this topic.
Started programming VBA 20 years ago and developed all kind of commercial and engineering software including complex reservoir operation models, etc. It is a great language, particularly if one needs a quick answer, much easier to program, execute and manage than let say FORTRAN. But the results are at times not to the accuracy one desires. I am about to switch to Python for XL through XLWings. Any comment from someone? Thanks
haha, I’m too late here. I just wanted to say I had a bad experience with python. It’s too complicated for me and no one seems to be patient enough to explain his codes to me. So I went back to the basics (lol) and I’m still doing great stuff with VBA.
Lots of great things can be done with VBA. Keep using it. 😎
Very insightful discussion . We have VB Macro based XLSM files that execute manual steps in a BPCS ERP. The challenge we are facing now is that we are upgrading to have all installations moved to the latest ACS Client which is Java Based. This move does not support the old VB Macro files any longer. Anyone perhaps have or know of a tool on how we can convert these Macro’s to be Java based ? Any good advice will be appreciated.
Why is microsoft not thinking about this , can they come up with program converters from VBA to python or c or java or whatever it takes to make the codes more widely supported . VBA is a great blessing especially when we cannot get dedicated IT support due to their priorities for valid reasons , however if Microsoft can come up with a solution on this , imagine the number of users who will turn towards VBA knowing that the tool has the power to convert it to a more supported language . In this age of machine learning , I am hoping it is possible , maybe some of the power users should come together and address this with Microsoft. Let me know if you concur?
I just say this chain over several years has been a great read !!!
A VBA converter is a good idea.
To my knowledge, when we apply VBA macro to small (in terms of data content) workbooks, it works fine, but when we apply the same macro to a workbook with a large volume of data (as an example workbook with 30000 rows and 30 columns of data) it does not work correctly. I have tried this many times and the result is all the same. Though I use WPS Spread-sheet (not the MS Office product) but I think VBA works the same way in MS Excel as well.
VBA was invented during the Basic era. Basic was a crappy language same goes for VBA. Compared to languages such as C,C++,Python,Javascript, and even powershell; VBA is garbage. I’m glad Microsoft did a good job with powershell and C# is alright.
Apple does a good job of just stopping support for legacy stuff in their code. Microsoft should have abandoned Macros and VBA a long time ago. It is confusing for even developers. The Microsoft Office suite has been notorious for being a path for hackers to embed malicious stuff in there. Also, it has so much legacy features that has made the product a nightmare to debug. Office will also compile the macros, which makes it so hackers can hide macros within another macro layer, making it very difficult for security experts to analyze Office malware (Macro stomping technique).
Microsoft needs to redesign Office and add a better safer language if they still want people to do stuff like Macros do. Personally, I would get rid of any Macro like support, and have business people learn more modern ways to do their work.
Great article. I’ve done an Excel data manipulation macro 15 years ago to make insane traffic count data useable, as our contractor provided it in weekly tables on one CSV sheet. Madness. Took a while to get the VBA just right to do all the cut and pastes so that the data was in one continuous table, so charts could be created. This was in the days when you got a memory fault for one module! This is one of very few macros I have done on Excel. Most of the time you need to check colleagues sheets for statistical significance, and there are no short cuts with this. But locking the table column descriptions is useful and adding filters to them all (delete blank rows as filters won’t work if table is not continuous); Also hide helper columns with formulas, or grey them out with fill colouring.
I returned to Macros last August for a repetitive work stream, and as some of your other replies have stated, their simple as well as complex macros have saved many weekly hours. The Macros I created are:
Slipstick OUTLOOK Macros:
“Es” save Email to Word docx
Macro s is added to force High Res embedded images to the page margin
“A” save all Email attachments to C:\
Macro deletes the previous attachments at start of macro
“e” save email as *.txt
WORD Macros:
“B” http://www.gregmaxey.com page / section / header & footer unlink
‘IS” import images from Macro A into Macro Es docx
“RS” gregmaxey.com rotate image UserForm for selection or all images by 90,180, or 270
“s” (lowercase s) reduces oversized images to page margins.
“S” (uppercase S) forces images smaller than A4 after being moved to fill A4 to the margin edges. Pixel widths or heights of original orientation have to be respected; I have added extra code to correct if the image is panoramic as the narrower dimension was oversizing these and the longer length needs to be reinstated.
“P” Prints an opened Macro A doc / docx/ rtf /odt file to pdf with same name in the same location. It is quicker to rotate these pdfs and copy the full screen in landscape and paste into the Macro Es docx.
“C” this brings up the Picture Compression sub menu, by 3 VBA lines undeclared: if doc inlineshapes count >0 then _ select 1st inline shape _ can’t remember this bit before mso “picture compress”
I may need to finally code this for 150ppi compression by adding a helper file at this compression as the resolution in the sub menu is already selected if this is the 1st file, then apply and delete the helper file and save the file. Not sure if I can reset the two ticksble options to unticked at the top of this sub menu. Unfortunately we have a 4MB limit for our client account system. Some times going down to 96ppi is necessary so I will attempt to create this as well.
“X” This closes the active document, sounds simple but complicated by protected view documents that are downloaded from the client account system on a browser window. I had to make the protected view document editable. Trial and Error to get this trapped and working, as counting protected view is not possible on the active window. Proud of this one. Saves me about 15secs for each use, if I use 53 times a day it saves 2% of work hours. Collectively I doubled daily productivity with all these Macros, which as standalone ones are useful as well.
Also working on a macro found that will open a pdf at a particular page and zoom. If I can get this to work, then I should be able to automate the insertion of Macro A pdfs into the Macro Es (if less than 11 pages in total, as PDFs become unreadable of Macro C is used to 96ppi) Would use Adobe manipulation macro, to use CutePdfWriter for pdf compression to 72dpi, this is fiddly to set up – my employer won’t pay for Adobe Pro License.
Oz, hi; New boy on the block 😶🌫️. I have been tinkering with vba, on and off, for about 7 years.
I hear you! Your six points, which I found today whilst searching, have put my question of ‘should I code all my very complex and very long formulas into vba,’ right into context. But, can you suggest any innovative ways that I can use to protect those formulas from the user. Passwords are one thing, but they can be broken with simple code. I was thinking of developing some code that changes worksheet passwords based on the day of the month or something similar. Or maybe linking access to a question/ response routine. Any ideas?
Thanks
D.