Visual Basic for mAsochists (VBA)
-
loctrice wrote:
Horrible. Whenever possible, use .Net and/or Java to program for excel.
If it is possible to do your automation solution with OOo, I'd say give it a try. It's scriptable with both Python and Java, not just a VBA-like language (which it does allow, in order to be able to import MSO files containing macros).
-
Now before I start this rant, I mean no insult to any VBA programmers out there reading this. I could not have greater admiration for you for succeeding where I have failed in actually accomplishing anything in VBA for Excel without lines and lines of hacks and botched, messy code. I want to get the general consensus on this, as it has been bugging me no end. My first gripe is that the VBA programming environment that comes with Office (2007) is to my eyes just utter tat. It is currently removing and inserting random whitespace here there and everywhere in what seems to be an overtly malicious attempt to mess with my head. The 'intellisense' and 'syntax highlighting' leave much to be desired, but I could whine about that all day, so I'll stop there. In my opinion however, the absolute worst thing about the VBA editor that shipped with Excel 2007 is... The environment alerts you to coding errors with - get this - a MODAL messagebox that totally interrupts what you're trying to accomplish by screaming in your face until you click the stupid 'OK' button. Secondly it seems to be to be impossible, or at least highly impractical, to accomplish anything approaching an elegant solution to a problem using VBA in Excel. So far this project, I have been repeatedly flummoxed by the arcane, esoteric knowledge of all the peculiarities of VBA that seems to be required to do anything at all in the language with any degree of efficiency. While I am far from being a VB guru, VB.NET is my main programming language alongside Java and also my main source of income. I was astonished to find that my half a decade of VB.NET experience did not help one jot with VBA. Is this just me? Please tell me, because I'm not sure if the problem is on my end (very possible) or with whichever dark ritual was used to create this 'programming language' in the first place.
A programming language is to a programmer what a fine hat is to one who is fond of fancy garden parties. Just don't try wearing any .NET language on your head. Some of them are sharp.
I think you just got off to a bad start and it'll gel for you. I've done VBA in excel (and some in Access) for years, mostly in Office 2003 though. I think it is great. The spreadsheet, when automated and a UI built up around it, is quite powerful in some business segments. You've already heard of the way to turn off the syntax check, yes, that is a huge pain when it is on. Programming in Excel with VBA has many of the same principles as VB.NET. . .you have objects (the workbook, each sheet, cells, forms you can create, msgboxes) that have the typical events you'd expect, just like .NET. Not sure why you have the intellisense problem, it's usually pretty good. "Secondly it seems to be to be impossible, or at least highly impractical, to accomplish anything approaching an elegant solution to a problem using VBA in Excel." To this I'd say that the approach typically is to have a button on a worksheet to invoke a task or you make a UI form (window) with controls on it that do work, often using spreadsheets in the background as data stores where you can sort, filter, arrange, do mathematical functions upon, the data just as you would yourself when using a spreadsheet. Good luck!
-
Ah VBA! I love it, love it, love it! Before you all dismiss me as completely and utterly mad, imagine the scenario ... 1. In the dark and distant past a user discovers Excel macros and starts to "automate" certain daily functions 2. It soon becomes all too much for them to handle so the macros get passed to an "IT contractor" to be brought up to production strength (an oxymoron if ever there was). Note that at this point the IT department still know nothing about this 3. In the meantime these spreadsheets somehow manage to become business critical "applications" 4. Eventually someone in the user department has an epiphany - or it might have been an audit finding - and said piece of s**t is passed over to the IT department to support. 5. By now the business-critical excel "application" consists of more than 120 spreadsheets mashed together like a web built by a hundred drunken spiders 6. Enter yours truly ... the re-engineering of various parts of this mess (when allowed to by the red-tape afficiandos) has kept me in gainful employment for some considerable time!! :-D p.s. around here "VBA" is known as "Visual Basic for Amateurs"
CHill60 wrote:
Ah VBA! I love it, love it, love it!
LOL! Yeah, sometimes the technology that some people hate can be a gold mine. CB
-
Now before I start this rant, I mean no insult to any VBA programmers out there reading this. I could not have greater admiration for you for succeeding where I have failed in actually accomplishing anything in VBA for Excel without lines and lines of hacks and botched, messy code. I want to get the general consensus on this, as it has been bugging me no end. My first gripe is that the VBA programming environment that comes with Office (2007) is to my eyes just utter tat. It is currently removing and inserting random whitespace here there and everywhere in what seems to be an overtly malicious attempt to mess with my head. The 'intellisense' and 'syntax highlighting' leave much to be desired, but I could whine about that all day, so I'll stop there. In my opinion however, the absolute worst thing about the VBA editor that shipped with Excel 2007 is... The environment alerts you to coding errors with - get this - a MODAL messagebox that totally interrupts what you're trying to accomplish by screaming in your face until you click the stupid 'OK' button. Secondly it seems to be to be impossible, or at least highly impractical, to accomplish anything approaching an elegant solution to a problem using VBA in Excel. So far this project, I have been repeatedly flummoxed by the arcane, esoteric knowledge of all the peculiarities of VBA that seems to be required to do anything at all in the language with any degree of efficiency. While I am far from being a VB guru, VB.NET is my main programming language alongside Java and also my main source of income. I was astonished to find that my half a decade of VB.NET experience did not help one jot with VBA. Is this just me? Please tell me, because I'm not sure if the problem is on my end (very possible) or with whichever dark ritual was used to create this 'programming language' in the first place.
A programming language is to a programmer what a fine hat is to one who is fond of fancy garden parties. Just don't try wearing any .NET language on your head. Some of them are sharp.
Past success is not guarantee of future sucess... ;P As far as i'm aware, VB6 an VB.NET are different languages, just superficially similar on some syntax and features, so your knowledge on VB.NET is almost moot on VB6, probably the other way around could be better, but i won't be betting on it.
CEO at: - Rafaga Systems - Para Facturas - Modern Components for the moment...
-
Ah VBA! I love it, love it, love it! Before you all dismiss me as completely and utterly mad, imagine the scenario ... 1. In the dark and distant past a user discovers Excel macros and starts to "automate" certain daily functions 2. It soon becomes all too much for them to handle so the macros get passed to an "IT contractor" to be brought up to production strength (an oxymoron if ever there was). Note that at this point the IT department still know nothing about this 3. In the meantime these spreadsheets somehow manage to become business critical "applications" 4. Eventually someone in the user department has an epiphany - or it might have been an audit finding - and said piece of s**t is passed over to the IT department to support. 5. By now the business-critical excel "application" consists of more than 120 spreadsheets mashed together like a web built by a hundred drunken spiders 6. Enter yours truly ... the re-engineering of various parts of this mess (when allowed to by the red-tape afficiandos) has kept me in gainful employment for some considerable time!! :-D p.s. around here "VBA" is known as "Visual Basic for Amateurs"
I have profited by the scenario you describe many times but regardless of how well you are being paid, maintaining those business critical Excel "applications" is often like playing Twister with a bunch of long unwashed dudes who reek of garlic and stale sweat. You have to run home for a long shower with the stereo blasting in an attempt to purge the memory of the experience from your body and soul.
-
I have profited by the scenario you describe many times but regardless of how well you are being paid, maintaining those business critical Excel "applications" is often like playing Twister with a bunch of long unwashed dudes who reek of garlic and stale sweat. You have to run home for a long shower with the stereo blasting in an attempt to purge the memory of the experience from your body and soul.
-
I normally use vbscript - probably more archaic than VB6 but the great thing is you can copy stuff from odbc databases to create word, excel and powerpoint documents. If you get fed up of vbscript, you can use javascript to achieve the same result.
-
I have profited by the scenario you describe many times but regardless of how well you are being paid, maintaining those business critical Excel "applications" is often like playing Twister with a bunch of long unwashed dudes who reek of garlic and stale sweat. You have to run home for a long shower with the stereo blasting in an attempt to purge the memory of the experience from your body and soul.
Most janitorial jobs are like that. If it doesn't work, blame the guy before (crap coding, not well thought out, doesn't know what he/she's doing etc). If it does work, you get the credit. You won't find any other job with a win-win situation. Remember the moral of the story of the non-conforming sparrow - when you are warm and happy in a pile of shit, keep your mouth shut.
-
Ah VBA! I love it, love it, love it! Before you all dismiss me as completely and utterly mad, imagine the scenario ... 1. In the dark and distant past a user discovers Excel macros and starts to "automate" certain daily functions 2. It soon becomes all too much for them to handle so the macros get passed to an "IT contractor" to be brought up to production strength (an oxymoron if ever there was). Note that at this point the IT department still know nothing about this 3. In the meantime these spreadsheets somehow manage to become business critical "applications" 4. Eventually someone in the user department has an epiphany - or it might have been an audit finding - and said piece of s**t is passed over to the IT department to support. 5. By now the business-critical excel "application" consists of more than 120 spreadsheets mashed together like a web built by a hundred drunken spiders 6. Enter yours truly ... the re-engineering of various parts of this mess (when allowed to by the red-tape afficiandos) has kept me in gainful employment for some considerable time!! :-D p.s. around here "VBA" is known as "Visual Basic for Amateurs"
I've lived through this "process" too over the past 16 years. It will keep me in productive employment for the remaining years (both of them) until I retire. I'm currently maintaining an Access database front-end for a SQL Server 2012 database which uses a large amount of VBA code that is used to run the whole business. Having written applications in VB6 for 12 years before this job means that I'm used to the whole thing.
-
Now before I start this rant, I mean no insult to any VBA programmers out there reading this. I could not have greater admiration for you for succeeding where I have failed in actually accomplishing anything in VBA for Excel without lines and lines of hacks and botched, messy code. I want to get the general consensus on this, as it has been bugging me no end. My first gripe is that the VBA programming environment that comes with Office (2007) is to my eyes just utter tat. It is currently removing and inserting random whitespace here there and everywhere in what seems to be an overtly malicious attempt to mess with my head. The 'intellisense' and 'syntax highlighting' leave much to be desired, but I could whine about that all day, so I'll stop there. In my opinion however, the absolute worst thing about the VBA editor that shipped with Excel 2007 is... The environment alerts you to coding errors with - get this - a MODAL messagebox that totally interrupts what you're trying to accomplish by screaming in your face until you click the stupid 'OK' button. Secondly it seems to be to be impossible, or at least highly impractical, to accomplish anything approaching an elegant solution to a problem using VBA in Excel. So far this project, I have been repeatedly flummoxed by the arcane, esoteric knowledge of all the peculiarities of VBA that seems to be required to do anything at all in the language with any degree of efficiency. While I am far from being a VB guru, VB.NET is my main programming language alongside Java and also my main source of income. I was astonished to find that my half a decade of VB.NET experience did not help one jot with VBA. Is this just me? Please tell me, because I'm not sure if the problem is on my end (very possible) or with whichever dark ritual was used to create this 'programming language' in the first place.
A programming language is to a programmer what a fine hat is to one who is fond of fancy garden parties. Just don't try wearing any .NET language on your head. Some of them are sharp.
Wait until you try conditional formatting from inside macros. Here's something I wish somebody had told me: Don't use formulas with the "A1"/"AD12"/etc. type of cell addresses. At least in Excel 2003, using the A1 style, I'd print out what I was about to put into the formulas, then print out what Excel claimed was in the formulas - they'd match exactly what I expected, but at the end of the process, when you went back to see why your conditional formatting wasn't working, the formulas were garbled. Eventually I tried using the R1C1 type (and also the R[#]C[#] type) cell addressing and everything worked just fine. I sure wish I could get that month of my life back...
-
Now before I start this rant, I mean no insult to any VBA programmers out there reading this. I could not have greater admiration for you for succeeding where I have failed in actually accomplishing anything in VBA for Excel without lines and lines of hacks and botched, messy code. I want to get the general consensus on this, as it has been bugging me no end. My first gripe is that the VBA programming environment that comes with Office (2007) is to my eyes just utter tat. It is currently removing and inserting random whitespace here there and everywhere in what seems to be an overtly malicious attempt to mess with my head. The 'intellisense' and 'syntax highlighting' leave much to be desired, but I could whine about that all day, so I'll stop there. In my opinion however, the absolute worst thing about the VBA editor that shipped with Excel 2007 is... The environment alerts you to coding errors with - get this - a MODAL messagebox that totally interrupts what you're trying to accomplish by screaming in your face until you click the stupid 'OK' button. Secondly it seems to be to be impossible, or at least highly impractical, to accomplish anything approaching an elegant solution to a problem using VBA in Excel. So far this project, I have been repeatedly flummoxed by the arcane, esoteric knowledge of all the peculiarities of VBA that seems to be required to do anything at all in the language with any degree of efficiency. While I am far from being a VB guru, VB.NET is my main programming language alongside Java and also my main source of income. I was astonished to find that my half a decade of VB.NET experience did not help one jot with VBA. Is this just me? Please tell me, because I'm not sure if the problem is on my end (very possible) or with whichever dark ritual was used to create this 'programming language' in the first place.
A programming language is to a programmer what a fine hat is to one who is fond of fancy garden parties. Just don't try wearing any .NET language on your head. Some of them are sharp.
What? OK, it is an OLDER platform. But apart from not having parameterized constructors (which it inherits from COM, of no fault of its own) I don't get the complaint. VBA is great, like all things, for the initiated. (Try writing a significant system in the WordPerfect 5.1 macro language!) To make it more robust, I built an error control system in VBA itself that allows discriminated error logging, all integrates into the language, and handles even recursive functions. Keeps the call stack, etc., and has purred away along with the main programming it supports in an Excel-based end-of-day system at several retail locations for years now. I show up once a year, look at the log, and generally just delete it. VBA is martial arts. I built a little column picker that lets the user create several different views of a spreadsheet and pick the desired view, or use it as a basis for a custom view on the spot. All just one form that plugs right in to any spreadsheet or XLAM. And a little programming will let you lock down spreadsheets, remove gridlines, etc., so they look like big windows forms, all automatically. Where else do you get juicy windows forms that have the ability to do math like that! Wrote a serialized inventory spreadsheet that took direct input from inventory guns, for cellular phones. Get a call they needed it for cellular identification modules...had it out that afternoon, all in a turn-key system. It just takes having a handful of tools that you put together into your XLAM. The only things I'm hateful about are messing with charting from 2003 to 2007, before which I had interactive zoomable mathematical charts, etc., all in VBA itself, and after which it crawled to a stop, and the Ribbon UI, which is painful and silly in its implementation, even if the end result is pretty nice (and when your clients see their special little tab just for them, they think you must really be a wizard). If you want any tools, visit Chip Pearson's website. Want the error system (which implements as a singleton offering several additions to your XLAM) or column picker, just let me know. I've got a sad github page up, but haven't really done it justice yet.
JonShops -- Fun really begins with the words, "So what in the World do I do now?"
-
Now before I start this rant, I mean no insult to any VBA programmers out there reading this. I could not have greater admiration for you for succeeding where I have failed in actually accomplishing anything in VBA for Excel without lines and lines of hacks and botched, messy code. I want to get the general consensus on this, as it has been bugging me no end. My first gripe is that the VBA programming environment that comes with Office (2007) is to my eyes just utter tat. It is currently removing and inserting random whitespace here there and everywhere in what seems to be an overtly malicious attempt to mess with my head. The 'intellisense' and 'syntax highlighting' leave much to be desired, but I could whine about that all day, so I'll stop there. In my opinion however, the absolute worst thing about the VBA editor that shipped with Excel 2007 is... The environment alerts you to coding errors with - get this - a MODAL messagebox that totally interrupts what you're trying to accomplish by screaming in your face until you click the stupid 'OK' button. Secondly it seems to be to be impossible, or at least highly impractical, to accomplish anything approaching an elegant solution to a problem using VBA in Excel. So far this project, I have been repeatedly flummoxed by the arcane, esoteric knowledge of all the peculiarities of VBA that seems to be required to do anything at all in the language with any degree of efficiency. While I am far from being a VB guru, VB.NET is my main programming language alongside Java and also my main source of income. I was astonished to find that my half a decade of VB.NET experience did not help one jot with VBA. Is this just me? Please tell me, because I'm not sure if the problem is on my end (very possible) or with whichever dark ritual was used to create this 'programming language' in the first place.
A programming language is to a programmer what a fine hat is to one who is fond of fancy garden parties. Just don't try wearing any .NET language on your head. Some of them are sharp.
Meh - I've programmed in (MUCH) worse - the DXL language used by the DOORS requirements management system, for one... That takes more of a WordBASIC (anyone remember that gem?) approach... And yes, I've programmed some reasonable systems in VBA - amazing what you can do with a few Win32 DLL calls :)
Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p CodeProject MVP for 2010 - who'd'a thunk it!
-
I don't think Microsoft want you to use VBA any more - you can now use .Net to do exactly the same stuff[^]. You still need to know the ghastly innards of Office COM automation but it should be that little less frustrating. Jake Ginnivan also has a pretty nice library[^] that should help you with your COM headaches. Also remember, if you want to distribute your spreadsheet, that VBA is now an optional installation component (turned off by default from what I remember), where VSTO is always installed (from what I remember :D). It is putrid though, if you want worse try writing extensive Visual Studio plugins (project systems, debuggers, etc.).
He who asks a question is a fool for five minutes. He who does not ask a question remains a fool forever. [Chinese Proverb] Jonathan C Dickinson (C# Software Engineer)
Nope, you can't do Access programming in .Net (other than Add-Ins). I just read your link and it seems to confirm that for Excel, Word, etc. You can create Add-Ins, but that's only one aspect of what you can create using VBA for those apps. VBA (a.k.a. Active Content) is disabled by default in Office apps when the document, spreadsheet, etc. is from an unknown source. A security message bar appears at the top of the document warning you about this; clicking the Enable Content button on it allows any included VBA code to run. VBA, like the VB6 it was derived from, is actually quite powerful. You can call Windows APIs directly from it to perform tasks that aren't already handled by the multitude (literally hundreds provided by default by Microsoft alone - not to mention 3rd party) of ActiveX objects and DLLs that are available. It allows the creation of class modules to so it can be as OO as anything.
Da Bomb
-
It's not just you. :beer:
SixOfTheClock wrote:
a MODAL messagebox
Yes, that is the VERY worst thing about VBA and it's been like that for decades. My only solace when I had to do VB.net was that VS doesn't do that. As to the rest, yes, finding out how to do anything is very difficult.