VBA in Excel
-
I decided that, instead of manually categorising a bunch of values in a spreadsheet I'd write a simple VBA method that would do the hard work for me. After all, doing it manually would take about an hour. Writing a bit of VBA couldn't take more than... well, I think I'm on hour 5 at this point. It's been a long time since I've done VBScript, but even VBScript was generally fairly sensible. VBA? I can't believe it's 2020 and VBA is pretty much the only scripting option available in Excel. Sure, you can write add-ins using Javascript and in-cell formula using Javscript, but no Javascript scripting. No constructor, serious hassles passing user defined types between methods, a limit to the number of times you can use line continuation in a row, the awful experience overall. I can't believe how much of the world lives and breathes this stuff. (but of course I'm going to bash my way through it instead of just getting the job done the old fashioned way)
cheers Chris Maunder
I feel your pain - Welcome to my life! I semi-retired a few years ago but took up a job "in the business" looking after their EUC "solutions" - most of which consisted of recorded macros. Three years later and trying to introduce any form of governance is still like herding cats :sigh: Just today I was yearning for the days when I would just knock up a quick program to automate something - I sometimes even find myself wishing for VB6! :wtf: :-O
-
I decided that, instead of manually categorising a bunch of values in a spreadsheet I'd write a simple VBA method that would do the hard work for me. After all, doing it manually would take about an hour. Writing a bit of VBA couldn't take more than... well, I think I'm on hour 5 at this point. It's been a long time since I've done VBScript, but even VBScript was generally fairly sensible. VBA? I can't believe it's 2020 and VBA is pretty much the only scripting option available in Excel. Sure, you can write add-ins using Javascript and in-cell formula using Javscript, but no Javascript scripting. No constructor, serious hassles passing user defined types between methods, a limit to the number of times you can use line continuation in a row, the awful experience overall. I can't believe how much of the world lives and breathes this stuff. (but of course I'm going to bash my way through it instead of just getting the job done the old fashioned way)
cheers Chris Maunder
This is the point at which I dump a spreadsheet, and create an SQL database and C# app to process it ... "it'll only take me an hour or so, won't it?" I end up with the same time problem as you, but in a much more comfortable language! :laugh:
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony AntiTwitter: @DalekDave is now a follower!
-
I decided that, instead of manually categorising a bunch of values in a spreadsheet I'd write a simple VBA method that would do the hard work for me. After all, doing it manually would take about an hour. Writing a bit of VBA couldn't take more than... well, I think I'm on hour 5 at this point. It's been a long time since I've done VBScript, but even VBScript was generally fairly sensible. VBA? I can't believe it's 2020 and VBA is pretty much the only scripting option available in Excel. Sure, you can write add-ins using Javascript and in-cell formula using Javscript, but no Javascript scripting. No constructor, serious hassles passing user defined types between methods, a limit to the number of times you can use line continuation in a row, the awful experience overall. I can't believe how much of the world lives and breathes this stuff. (but of course I'm going to bash my way through it instead of just getting the job done the old fashioned way)
cheers Chris Maunder
Obligatory XKCD: Automation[^] :-D And yes, VBA, the red-haired illegitimate step-child of VB4, still sucks big fat hairy ones. At this point, I think it's too late for MS to fix it. Maybe it's time for the Ripley option[^]?
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Obligatory XKCD: Automation[^] :-D And yes, VBA, the red-haired illegitimate step-child of VB4, still sucks big fat hairy ones. At this point, I think it's too late for MS to fix it. Maybe it's time for the Ripley option[^]?
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
And I'll Obligatory right back at you: [xkcd: Is It Worth the Time?](https://xkcd.com/1205/)
cheers Chris Maunder
-
And I'll Obligatory right back at you: [xkcd: Is It Worth the Time?](https://xkcd.com/1205/)
cheers Chris Maunder
But did you factor in the time you spend finding the chart to look up what you save? :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I decided that, instead of manually categorising a bunch of values in a spreadsheet I'd write a simple VBA method that would do the hard work for me. After all, doing it manually would take about an hour. Writing a bit of VBA couldn't take more than... well, I think I'm on hour 5 at this point. It's been a long time since I've done VBScript, but even VBScript was generally fairly sensible. VBA? I can't believe it's 2020 and VBA is pretty much the only scripting option available in Excel. Sure, you can write add-ins using Javascript and in-cell formula using Javscript, but no Javascript scripting. No constructor, serious hassles passing user defined types between methods, a limit to the number of times you can use line continuation in a row, the awful experience overall. I can't believe how much of the world lives and breathes this stuff. (but of course I'm going to bash my way through it instead of just getting the job done the old fashioned way)
cheers Chris Maunder
After the first hour, the time it would have taken to do it the old fashioned way, it becomes a matter of pride!
Monday starts Diarrhea awareness week, runs until Friday! JaxCoder.com
-
And I'll Obligatory right back at you: [xkcd: Is It Worth the Time?](https://xkcd.com/1205/)
cheers Chris Maunder
Ah, but once it's automated, how much time do you save explaining (and re-explaining) how to use it to non-techies?
I wanna be a eunuchs developer! Pass me a bread knife!
-
I decided that, instead of manually categorising a bunch of values in a spreadsheet I'd write a simple VBA method that would do the hard work for me. After all, doing it manually would take about an hour. Writing a bit of VBA couldn't take more than... well, I think I'm on hour 5 at this point. It's been a long time since I've done VBScript, but even VBScript was generally fairly sensible. VBA? I can't believe it's 2020 and VBA is pretty much the only scripting option available in Excel. Sure, you can write add-ins using Javascript and in-cell formula using Javscript, but no Javascript scripting. No constructor, serious hassles passing user defined types between methods, a limit to the number of times you can use line continuation in a row, the awful experience overall. I can't believe how much of the world lives and breathes this stuff. (but of course I'm going to bash my way through it instead of just getting the job done the old fashioned way)
cheers Chris Maunder
I guess we need something like Webassembly Excelassembly. Then again, having fiddled with the file format I have to admit I'm quite for the Ripley option.
Wrong is evil and must be defeated. - Jeff Ello
-
I decided that, instead of manually categorising a bunch of values in a spreadsheet I'd write a simple VBA method that would do the hard work for me. After all, doing it manually would take about an hour. Writing a bit of VBA couldn't take more than... well, I think I'm on hour 5 at this point. It's been a long time since I've done VBScript, but even VBScript was generally fairly sensible. VBA? I can't believe it's 2020 and VBA is pretty much the only scripting option available in Excel. Sure, you can write add-ins using Javascript and in-cell formula using Javscript, but no Javascript scripting. No constructor, serious hassles passing user defined types between methods, a limit to the number of times you can use line continuation in a row, the awful experience overall. I can't believe how much of the world lives and breathes this stuff. (but of course I'm going to bash my way through it instead of just getting the job done the old fashioned way)
cheers Chris Maunder
-
I decided that, instead of manually categorising a bunch of values in a spreadsheet I'd write a simple VBA method that would do the hard work for me. After all, doing it manually would take about an hour. Writing a bit of VBA couldn't take more than... well, I think I'm on hour 5 at this point. It's been a long time since I've done VBScript, but even VBScript was generally fairly sensible. VBA? I can't believe it's 2020 and VBA is pretty much the only scripting option available in Excel. Sure, you can write add-ins using Javascript and in-cell formula using Javscript, but no Javascript scripting. No constructor, serious hassles passing user defined types between methods, a limit to the number of times you can use line continuation in a row, the awful experience overall. I can't believe how much of the world lives and breathes this stuff. (but of course I'm going to bash my way through it instead of just getting the job done the old fashioned way)
cheers Chris Maunder
And to make things even "better"; the IDE hasn't been updated in more than 20 years (as far as I know).
My plan is to live forever ... so far so good
-
And to make things even "better"; the IDE hasn't been updated in more than 20 years (as far as I know).
My plan is to live forever ... so far so good
don't moan dear fellow devs, have e.g. a look at Write Office COM add-in, smart tag, RTD server in Visual Studio .NET, VSTO, VCL[^] or, not my cup-of-tea, Office add-in Yeoman Generator demonstration - YouTube[^]
-
And to make things even "better"; the IDE hasn't been updated in more than 20 years (as far as I know).
My plan is to live forever ... so far so good
Have you seen Rubberduck, an addin which uses ANTLR to parse VBA and add more features? Also, there's a proposal on Rubberduck's repo to create a Language Server Protocol implementation for VBA; this would allow any LSP-supporting editor, such as VS Code, to work with VBA.
-
don't moan dear fellow devs, have e.g. a look at Write Office COM add-in, smart tag, RTD server in Visual Studio .NET, VSTO, VCL[^] or, not my cup-of-tea, Office add-in Yeoman Generator demonstration - YouTube[^]
Good Tips '489 ! Writing add-ins seems to be the only way to automate Office 365. It is becoming the corporate office platform in a lot of places so indeed MS has done a Ripley on us corporate software developers. If you do have to work in VBA -there are many toolsets to help write and maintain good code - MZ tools is my preference but Rubberduck · GitHub[^] looks good too. Its an old but stable IDE -little Intellisense, no autocompletion - but that only helps the coding - not the thinking! Export all your code modules, forms, spreadsheet content, formulae, formats to text files. Then you can do version control in mercurial, and inspection in Npp Merging is an issue but you can see what changed and revert or branch.
-
Good Tips '489 ! Writing add-ins seems to be the only way to automate Office 365. It is becoming the corporate office platform in a lot of places so indeed MS has done a Ripley on us corporate software developers. If you do have to work in VBA -there are many toolsets to help write and maintain good code - MZ tools is my preference but Rubberduck · GitHub[^] looks good too. Its an old but stable IDE -little Intellisense, no autocompletion - but that only helps the coding - not the thinking! Export all your code modules, forms, spreadsheet content, formulae, formats to text files. Then you can do version control in mercurial, and inspection in Npp Merging is an issue but you can see what changed and revert or branch.
-
In the Microsoft Tech community, there is a feature request to add support for Python as an Excel scripting language : python for excel application scripting - Microsoft Tech Community - 66113[^]
:doh:
cheers Chris Maunder
-
I decided that, instead of manually categorising a bunch of values in a spreadsheet I'd write a simple VBA method that would do the hard work for me. After all, doing it manually would take about an hour. Writing a bit of VBA couldn't take more than... well, I think I'm on hour 5 at this point. It's been a long time since I've done VBScript, but even VBScript was generally fairly sensible. VBA? I can't believe it's 2020 and VBA is pretty much the only scripting option available in Excel. Sure, you can write add-ins using Javascript and in-cell formula using Javscript, but no Javascript scripting. No constructor, serious hassles passing user defined types between methods, a limit to the number of times you can use line continuation in a row, the awful experience overall. I can't believe how much of the world lives and breathes this stuff. (but of course I'm going to bash my way through it instead of just getting the job done the old fashioned way)
cheers Chris Maunder
Why not generalize it and write a plugin? Then you can play DLL-Hell with every machine that needs it. Of course, if it only modifies and fixes the cells for you, then one machine is all it will take. Pity me, I once opened the xlsx file and looked at modifying the cells directly. I backed down, and did it the old fashioned way!
-
I decided that, instead of manually categorising a bunch of values in a spreadsheet I'd write a simple VBA method that would do the hard work for me. After all, doing it manually would take about an hour. Writing a bit of VBA couldn't take more than... well, I think I'm on hour 5 at this point. It's been a long time since I've done VBScript, but even VBScript was generally fairly sensible. VBA? I can't believe it's 2020 and VBA is pretty much the only scripting option available in Excel. Sure, you can write add-ins using Javascript and in-cell formula using Javscript, but no Javascript scripting. No constructor, serious hassles passing user defined types between methods, a limit to the number of times you can use line continuation in a row, the awful experience overall. I can't believe how much of the world lives and breathes this stuff. (but of course I'm going to bash my way through it instead of just getting the job done the old fashioned way)
cheers Chris Maunder
As a fairly serious Access programmer, I feel your pain! The one complaint that I don't endorse, though, is about the limit on consecutive line continuations, which I must say I have never run into. On the whole, I feel that if you have to continue a logical line over more than a couple of printed lines, you should break up the logic, since it is likely to be difficult to understand if/when you come back to it later, let alone by anyone else!
-
As a fairly serious Access programmer, I feel your pain! The one complaint that I don't endorse, though, is about the limit on consecutive line continuations, which I must say I have never run into. On the whole, I feel that if you have to continue a logical line over more than a couple of printed lines, you should break up the logic, since it is likely to be difficult to understand if/when you come back to it later, let alone by anyone else!
With regards to the line continuation issues, what I'm trying to do is create a an array (an ever growing array!) of terms that can be used to categorise an item. eg
items = Array("item1", _
"item2", _... "itemN")N, for me, has grown beyond the limits of what VBA likes. What I probably should do is just enter the terms in the spreadsheet somewhere and have the method reference the values in those cells rather than hard coding. I've well and truly burned through the 1hr I budgeted on this one
cheers Chris Maunder
-
With regards to the line continuation issues, what I'm trying to do is create a an array (an ever growing array!) of terms that can be used to categorise an item. eg
items = Array("item1", _
"item2", _... "itemN")N, for me, has grown beyond the limits of what VBA likes. What I probably should do is just enter the terms in the spreadsheet somewhere and have the method reference the values in those cells rather than hard coding. I've well and truly burned through the 1hr I budgeted on this one
cheers Chris Maunder
Understandability isn't an issue, then, but I think you are right about what you should do. When I have the same sort of issue in Access VBA, I generally create a new Table (if the 'array' is likely to be needed again) or Recordset (if it isn't), which is pretty much the Access equivalent of entering the terms on the spreadsheet in Excel.
-
With regards to the line continuation issues, what I'm trying to do is create a an array (an ever growing array!) of terms that can be used to categorise an item. eg
items = Array("item1", _
"item2", _... "itemN")N, for me, has grown beyond the limits of what VBA likes. What I probably should do is just enter the terms in the spreadsheet somewhere and have the method reference the values in those cells rather than hard coding. I've well and truly burned through the 1hr I budgeted on this one
cheers Chris Maunder
Yet another case of someone using excel as a quasi database. It should have just been done in Access in the first place. I, too, have done a lot in Access VBA - easy when you know how. The problem I've found in Excel VBA is that they named most 'things' differently. I haven't done any VBA for Winword, but the same issues probably arise. No doubt that different teams did the initial design for each program.
