Archive for the ‘VBA’ Category

VBA third most hated language

Thursday, 2nd November, 2017

amongst Stack Overflow devs anyway…

On SO devs can mention tech they would rather avoid. Perl was top at being avoid worthy, then Delphi (is that even still alive??) followed closely by our own VBA.

This is developers so its not really a surprise they prefer to avoid VBA. They want to be playing with new shit not adding business value.

Interestingly SO reported that VBA is still an actively tagged language so still popular in use, just not by devs.

In fact, of those languages disliked by over 3% of mentions, VBA is the only one not shrinking in tag mentions, in fact its growing slightly.

Advertisements

VBA course hosting

Friday, 27th October, 2017

I think I might have mentioned that I am thinking of making an online video VBA course?

Well anyway, I haven’t exactly finished the content yet…

But I have been poking around for a ‘platform’.

I was initially thinking of Udemy, and I still might go that way initially, but I have heard a few not so positive things about them from an instructor POV. Although I am not sure if that is all old stuff or recent.

Cost wise I imagine the course being around 50 quid/dollars/francs.

I had a look at this place and they have that price as an example.

If the student pays 50 quid the instructor can get as little as 3.15!!!!!

That is the content creator, the author, gets just over 5% of the price the student pays!!

I just can’t quite believe it!

I think I might look into creating a ‘platform’ instead. Its only hosting with a few bells and whistles, most of which I won’t use.

Maybe I’ll have to do youtube with ads after all.

Is VBA a dead end ?

Thursday, 28th September, 2017

This topic came up on Excel-l, I got shouted down on there, so I thought I would elaborate on my views here. (its mainly an Excel view but I don’t think the other MS Office apps are much different.)

I see two sides to the VBA story – the technology and the career.

Technology wise a VBA solution is a dead end – it can’t be run on a server (officially), it can’t be run in a browser, it can’t be run in a high performance cluster, it can’t be run on mobile devices. This means it can’t be easily scaled if more users or geographies must be served. You can’t really even have multiple simultaneous users of an Excel VBA app. With the relentless march to cloud and mobile, VBA is left behind.

Of course MS won’t remove support for VBA, the world would fall apart, well the finance, pharma, and energy industries at a minimum. But the editor is pitiful by the standards of this century. It is testament to the work of those MS guys in the ’90’s that the debug Edit/continue is still better than nearly all mainstream IDEs. But its still out of date and hard to extend.

MS can’t invest in VBA because their strategy is .net/cloud/browser blah blah. They had the chance to fight on their strengths – rich client, but instead they tried to be google. And lost. Now we are all struggling with crap web sites in bloated browsers with slow Win3.11 style interactions from 1995.

VBA hasn’t suddenly become bad, its still very good at what it does, its just that we are often now looking for more from our solutions. More users, more device types, more threads, more whatever.

So its not dead but it is basically limited to single user apps on their individual workstations. But I am sure it will continue to work for a long time.

From a career point of view, what else is like VBA? nothing really, so expertise in VBA doesn’t easily lead onto anything else, hence its a bit of a dead end.

VBA is not really object oriented so it doesn’t lead to the sort of object based designs that C# and Java are suitable for. Its not functional so it doesn’t lead to F#/Scala.

Of course you can write VBA in classes, but lack of implementation inheritance hobbles your solution. By the same token you can write VB style ad-hoc procedural code in C#/VB.net, but you would be missing out on many of the elegant OO features of .net. You would struggle to implement most modern design patterns in VBA, and the latest CV must-have MVC is also all but impossible.

From a functional point of view (probably more useful than OO these days) VBA can’t treat functions as data smoothly (although CallByName does better than many languages). And a little toot of Application.Run can ease the friction too.

In Excel VBA especially we can get an awful long way without worrying about user interface/user experience or data structure. This is great for your current users (fast development/deployment) not so good for your career development – no other system is so well integrated as Excel/VBA. No worrying about Restful APIs, onClickListeners, Asynchronous callbacks, all very powerful, all generally avoided in VBA land.

If you love VBA, and why not, then continue on, you are probably adding untold business value with the work you are doing (and perhaps a little future maintenance pressure for someone). But I’m not sure it will take your development career too far, business career possibly but IMO it won’t help you much as a developer.

If you are doing VBA now and thinking of progressing to other development technologies then consider carefully how you will make your next steps. Adding Access and SQL to VBA is completely doable, adding modern scripting, web technologies or .net/Java is much tougher.

Getting into .net via VB.net is a possibility, but, I think it would be easier to go straight to C#, although the .net/Excel story is still, in 2017 very ugly.

ExcelDNA might be a good bet if you want to inject a bit of .net into your Excel life. I found this approach much more viable than VSTO infrastructure hell. Somebody somewhere is maintaining a lot of both that I developed. I bet the ExcelDNA maintainer is having more fun.

I also managed to blag my way into writing a load of custom Java on Essbase, that route probably isn’t open for many though.

Which led to my new focus on Android which is both challenging and rewarding. Although stay posted for news on that…

btw this is my 1,000th post on SOS, I assume WordPress will give me some superficial badge of honour, reward or ‘achievement unlocked’ bollocks, in the modern way.

cheers

simon

 

Job hunt

Wednesday, 20th September, 2017

I’m back on the hunt for a contract.

Looking for easy work, high pay, and ability to work from home in my pyjamas.

I am just caught mid hop really, my Android mastery has not quite reached the level to dazzle, and my Excel/VBA/trading experience is starting to look a little distant.

That said I do have a few mini projects on the go both in VBA and Android, but a big juicy contract would be handy enough too

I am pimping myself out on all the usual job sites and I have noticed a pattern.

An agency posts a job, I apply with my cv, and then… tumbleweed.

This is not new of course, and perhaps my ‘varied’ cv is not what they are looking for but…

A couple of agents did get back to me either with a no thanks or a discussion or whatever. And you know what?

The next time a role comes on with multiple agencies, I apply to the ones that bothered their arse to get back to me. Those tumbleweed guys? I don’t bother.

So pimps of the world, if you can’t even be arsed to ping back a quick no thanks to your applicants, you may be losing out on candidates in the future. just sayin.

10 year birthday

Sunday, 12th February, 2017

I missed it obviously but one of the kids pointed out I have been blogging here for 10 years! ken ell!

(as of January just gone)

I can’t imagine dragging it out for another 10, unless some spreadsheet miracle happens and I am sucked back in. I still think spreadsheets are brilliant at a great many things, I have just given up waiting for numb nuts management to realise.

I built a random mental maths test generator for the kids the other day. I looked at doing it as an app, as a web thing,  but in the end I did it in a spreadsheet in about 10 minutes. The kids hate it!

I love it!

(it even has a bit of VBA in it – woo hoo)

cheers

simon

secure security

Wednesday, 6th February, 2013

Got this recently:

disable

Surprise surprise I chose… Disable!

If you are running 2010 and have VBA in password to open protected .xlsms then they should probably be in a trusted location if you want the VBA to run. (I didn’t try very hard but there didn’t seem to be an easy way to trust the doc, maybe temporarily taking the pw off it, trusting it, then redo pw, would work).

Unless you have AV that can scan them (which products do?).

cheers

Simon

Nearly VBA

Tuesday, 26th June, 2012

I saw this security article about a recent infection.

It was an infected AutoCad template used to send a stack load of technical drawings to its creator for supposed industrial espionage reasons.

The technology it used is partly AutoCad scripting (which I thought is/was VBA?), but mainly plain old VBS run by the Windows Scripting Host.

It could so easily have been an Office related template. But then Excel is mainly used for shopping lists, Access for CD collections, and Powerpoint for manager technobabble, so I guess AutoCad probably is a better target.

Have you seen any sign of Office related malware recently? (I had an Excel VBA virus in 1998 or something, took about 10 minutes to disable and delete.)

chers

simon

Last few hours of conference discount

Friday, 6th January, 2012

The Early bird discount for the 2012 Excel Developer conference expires tonight at midnight (ish). (European time, CET not GMT) (11pm for the Brits).

Please visit the booking page here ASAP and get yourself booked on.

After tonight, the price goes up to 250 GBP, still a staggering bargain actually.

Hope to see you there,

cheers

Simon

SortAndDelete

Monday, 12th December, 2011

On reviewing a colleagues’ code recently I suggested he might want to change the name of his SortAndDelete sub to something that more accurately described what it did.

My proposal was FuckEverythingRightUp!

His response? That was meant to be a one time use system, the user had reloaded it with data of a very different structure.

My reply? if it was one time use then he should have run it and given the user the results only. Sods Law dictates that everything you give to your users(/customers) will be brutally abused and destroyed with no mercy.

He then pointed out he was trying to fix something I wrote a while ago that was initially quick and dirty fix, but was at that moment just dirty, and broken.

I asked what the comments said (comments are vital to quality code). He confirmed there was a

‘// sm 2010 quick and dirty bodge

comment, which I believe is better than a get out of gaol (jail) free card.

He also pointed out another of my comments from a while ago on a different system, he was now handholding:

‘// sm 2009 please god don’t let this bag of shite still be live in 2011.

Like I said comments are vital to back covering.

What’s the funniest, most inappropriate comment you have seen/written?

(I’m not sure if I should mention the dictionary object one of my colleagues used, using correct reddick tlp:

Dim dicHead as Scripting.Dictionary)

cheers

Simon

Excel Developer Conference London Jan 2012

Thursday, 3rd November, 2011

Well folks there has been a really positive response so we are game on for an event towards the end of Jan in Londonium.

I will sort out a more detailed draft agenda as things settle down with the speakers, but to give you some idea…

The following topic areas are likely to make it into the agenda

  • Excel Business Intelligence
  • PowerPivot and DAX
  • Migrating from VBA to ExcelDNA with VB.net
  • Aerobics
  • Effective Excel Add-ins with VSTO
  • Technology independent software development
  • Beer and chips (and gravy)
  • Practical advice on commercialising your add-ins/workbooks.
  • Various approaches to XLL development
  • Hairdressing
  • Excel and XML
  • Chess boxing
  • Quick tips for effective VBA use.
  • Programming paradigms and how they can help or hinder, a history lesson
  • My snowboarding holiday
  • A smart way to build spreadsheets
  • Beer
  • Curry

I am hoping the broad agenda will attract a diverse set of delegates…

Please start spreading the word: UK Excel conference Jan 2012 London…

I still need to sort a venue, but I am aiming for one day the week ending Fri 27th, probably the Tue or Wed.

Our friends at Microsoft have generously donated some gifts which we will give away on the day in return for cleverest question, dumbest question, nicest biscuits, most beer drunk, that sort of thing.

This event is specifically targeted at the more technical audience. The plan is to have a follow up event in July in Madchester. That event will be more user oriented and less developer oriented.

If you have any other suggestions for topics please let us know below.

cheers

simon