Archive for the ‘VBA’ Category

Excel 2010 TP review

Thursday, 31st March, 2011

[I wrote this some time ago (pre release), but I thought I would stick out there as its still relevant, and I havent done much more on 2010 since]

I’ve been playing around with twenty ten, and reading around some of the features. Bear in mind though this is pre release, so it may be missing some polish etc.

Here are those features I consider most important.

  1. You can still insert and write XLM. (So what? – XLM UDFs can be way faster than VBA ones, XLM used to cover a few thins you could not do in VBA (they are addressing these VBA gaps). But also XLM is the best guidance we have for VBA’s lifetime – and its looking like 2023 minimum) (2010 + 2010 – 1997))
  2. VBA still primary development environment, no obvious changes from 1997 IDE.
  3. VBA got ported to 64 bit – so its going to be around for some time.
  4. There is a new xll SDK – suggesting this will still be the add-in technology of choice.
  5. UI Customisation is back, programmability will probably reappear in V next +1, then we will just need to able able to adjust the ribbon size and orientate it at the side, and maybe drag and drop, and we will be back at 1995 (or earlier?), minus a few bazillion dollars of course.
  6. Base UI appears consistent with 2007 which is a relief
  7. Massive re-write of key stats functions
  8. High performance cluster stuff sounds like they are serious about Excel as a calc engine for serious application, especially with the new massive file size potential of 64 bit.

So in summary 2010 is reassuring to me, the 2007 noddy UI worried me that MS think Excel is for numpties and kids. 2010 suggests they see very serious applications.

Killer features to persuade you to migrate? Dunno, I’m not sure the real value proposition is in the client alone anymore. But I will move I think.

Are you live on 2010? what do you think? (some people are calling it O2007 SP3)

cheers

simon

Advertisements

Smurf on 2011

Saturday, 1st January, 2011

My 2010 predictions were a little tame I think, so I’m going to go more out on a limb this time around. I’ve covered a broader area this time too. I normally limit myself to spreadsheets and software, I’ve had a pop at some more general stuff this time.

IT Market

  1. Google will overtake Microsoft in market capitalisation (190 v 240 currently)
  2. MS won’t bid for Adobe.
  3. MS is fading fast as a general brand, especially with consumers, this will continue and probably accelerate
  4. Apple will pick up most of that consumer mind share in computing and software.
  5. I think someone might bid for HP this year, possibly Oracle if they can digest Sun in time. I don’t see too much anti competitive hassle from this and Larry has been softening them up with body blows for a few months
  6. Yahoo will surely disappear, I’m not sure how they survived this long
  7. Once people get bored of facebooking about facebook surely it will do a myspace?
  8. Linux won’t do much in 2011, I really thought netbooks would do it 2009/2010, but I was wrong. I don’t see a better chance in 2011. Tablets? I’m not convinced this time around.

Software

  1. Phone and tablet software will be massive in consumerland. iOS and Android (and Blackberry), not Windows phone.
  2. PC consumer software will be a non story (in the general media)
  3. MS Office is in a death spin where no one understands the value it can generate so no one invests so no one discovers. MS will continue to fail dismally to market Office. They will cut marketing spend so they will send less of the wrong messages to the wrong people.
  4. Quite a few corps will start to migrate to O2010 in 2011 as many skipped 2007 and 2003 will be 2 years out of mainstream support. They won’t leverage many of the new features though, as its ‘just Office’ not an integrated part of strategic IT infrastructure like it should be. (message/people…)
  5. I think Windows 7 may be due for deployment in a lots of companies too.

Office development

  1. We will still be undervalued.
  2. We will still be loved by users and loathed by IT, who will continue to prevent us from using the best tools for the job.
  3. Office will continue to be userland so VBA still key, although job ads will request a knowledge of C#, but then not let you have Visual Studio.
  4. Microsoft will still not have a plausible .net/Excel development story. VSTO isn’t it
  5. .net developers will continue to abuse Excel as they don’t understand the object model or native code.
  6. Access will still be looked down on as a zero credibility toy – an image which hampers SharePoint uptake as Access is a million times better for managing lists than the 1970’s web UI. I’m currently calling my Access development Jet development to avoid raising the Access Alert.
  7. Office 15 Beta 1 will probably make it out of the door before the end of the year. Expect the ribbon to be nearly as good as 2003 toolbars, lots of unusable lock-in to server components that corps won’t deploy. Beefed up power features like cluster udfs in 2010 (perhaps performant .net udfs???). More eye candy cabbage/inappropriate intra suite standardisation. Closer Excel/Access integration may be on the cards, even as many corps seek to ban Access altogether, it may cheat death once again.
  8. Plenty more vacuous ‘spreadsheet control’ projects will start in 2011, although most will be tick box half hearted affairs. The crash knocked a bit of the wind out of the compliance gravy train sails. (can I mix methaphors like that?)
  9. there will be plenty of opportunities in financial services for folks with Excel/VBA/business – this market is hotting up after being depressed for 18 months or so – so many devs will have moved on.
  10. LibreOffice will continue where OpenOffice once went as the leading Office competitor, before Oracle alienated the whole dev community. Still won’t be much of a competitor though, sadly. OpenOffice will be gone by the end of 2011 imo, at least ‘gone’ like StarOffice.

General development

  1. Java will lose some light as it has been scuttled by Oracle, I think this will cause more of a general splinter rather than a mass migration to .net for example. Ruby, Python etc will likely be winners on the web/server, but maybe C++ will find some love, it has done with MS in VS2010.
  2. Or maybe there will be an unbreakable Oracle Java?
  3. Silverlight is doomed, so VS2012 might be useable. Silverlight is the Access of UI. MS just don’t know what to do with it. Smart devs will steer clear till they decide. Gone by 2012 imo.
  4. Objective C will probably be a worthwhile skill in 2011 (not a great synergy with VBA though :))
  5. F# will get plenty of buzz, but not a lot of actual traction, just because OO is inappropriate for whole swathes of software doesn’t mean it isn’t deeply engrained (as the ‘correct/best/professional’ way of developing anything)

Hardware

  1. Well derr – tablets will be a big news story in 2010
  2. Netbooks will be replaced by tablets, in news, if not physically.
  3. The march to smartphones will make iPhone/Android/Blackberry development a very viable business model, especially compared to banging your head against the office dev wall.
  4. If the Ubuntu Tablet appears I’ll buy one ASAP.

I try not to be a ‘Microsoft watcher’ blogger partly because lots of people already do that, and partly because its hard to hit the right tone between sycophant (I earn my living in their tech after all) and irrelevant moaner.  But…

Microsoft

  1. MS is in a cost control phase at the moment. That means every investment/spend needs justifying.
  2. Plenty of possibly viable techs have been axed after massive investment over the past few years
  3. some of those have been brave (or mad – depends your pov) decisions
  4. This means very little is unquestionably safe – its perceived cost benefits, financially, ruthlessly.
  5. I think MS have lost the consumer space so they will focus more on the enterprise (which seems to work well for Oracle for example).
  6. Tight Office/.net integration? cost? benefit in increased unit sales income? – unlikely then…
  7. Windows phone? If it goes well in the next 6-12m then maybe, if not its out. Microsoft could exit this whole market with no obvious income loss (short term at least). (imo gone by 2012)
  8. Silverlight? compelling benefits or the next VB script? (imo gone by 2012)
  9. VSTO? essential .net/Office bridge? unfinishable unloved bodge? (imo VSTO will survive for a while as it joins a couple of cash cows)
  10. VBA? trusty workhorse? or poor implementation of a bad design? (imo safe as houses, there will be nothing that could be construed as a hint of retirement plans for this utterly vital (to MS) tech. Unless you take the complete lack of investment and development of the IDE as a hint of future plans of course)
  11. VSTA? remember that? VS editor in Office. Hmmm… I really don’t know if better .net/Office is coming to Office, If you pushed me I would say no, I don’t think O15 will have an integrated C# IDE.

Economics/etc

  1. No double dip recession (if anyone thinks we are out of the last one yet) just a slow (er than reported) creeping recovery.
  2. 2011 will, in line with solar physicist predictions (again), be colder than 2010 by any reasonable measure.
  3. The Urban Heat Island effect will get some attention to try to explain the gaping void between what global warming ‘scientists’ say and what normal people see and feel.
  4. We will see more climate comedy – where a council or org has made a big business decision based on promises of global warming, only to be totally shafted by actual weather. Step forward Geneva canton and Lytham council – both allegedly sold their snow clearing equipment in recent years then struggled last winter and this winter. (could we include Heathrow?)(we can take UK road salt supplies as a consistent snafu.)
  5. Financial market volatility will increase, especially on the down side as algorithmic trading gets more aggressive safety cut outs.
  6. 2011 will be even more fad-tastic than 2010, game shows, reality tv, shit music, phone apps, blankets with sleeves…
  7. No big UK bank failures
  8. Another couple of Euro zone rescue plans will be required, someone might even notice the state of the UK economy.
  9. A Euro will almost certainly be worth more than a pound, although they are both in a race to the bottom at the moment.
  10. More intrusive security pantomime mischief will make air travel even more unpleasant but no more secure.
  11. Did I mention that spreadsheet development will still not be cool?

That’s about it for now, I need to go out mountain biking now the snow has melted. Sorry this is so long!

What do you see as the big news stories of the next 12m?

All the best for 2011

cheers

Simon

2010 looking back

Wednesday, 29th December, 2010

I just re-read my predictions for 2010 and I note I hardly went out on a limb, with most of them being either blindingly obvious or un-falsifiable. Here they were.

The big (IT) 2010 story for me is how Oracle and Microsoft fell in love. I totally didn’t see that coming at all. By destroying first the OpenOffice dev community, and secondly the Java community Oracle has hamstrung two of the few viable competitors Microsoft had. I’m not clear what Oracle get in return, MS maybe promised to keep Steve Ballmer in charge.

I’ll just skim over the predictions I made:

Microsoft/Office/Excel

All the new stuff got released as expected, Office 2010 seems ok, they fixed some of the most obvious badness of 2007. VS2010 is the slowest, most bloated piece of pap I have ever used. I have 2008 and 2010 on my works machine and use 2008 wherever possible because you need a Cray super computer just to load VS2010 in under 3 minutes, never mind compile something.

I don’t think VBA went into freefall, it is still the user automation tool of choice (its a choice limited to one after all). But most financial services jobs are now looking for some C# in addition. Many seem to be trying VSTO and rejecting it though, and a lot of people I have spoken to recently are going the ExcelDNA route instead.

Sharepoint does seem to be hotting up, spreadsheets are ever more the scourge, Microsoft is becoming less relevant. There were quite a few spreadsheet risk/quality projects going on in 2010, they will be sharepoint clean ups in 2015.

Apple

Apple had a brilliant 2010 and that looks set to continue. The ipad still has no viable competition and they are flying off the shelves. Apple are reliving Microsoft from the 90’s before MS forgot that apps sell platforms. I can’t imagine Apple being too scared by the arrival of the Windows phone. I won’t get an iPad, but I could be tempted by the 11″ Air as a decent netbook to run Linux. Although rumours of an Ubuntu tablet in 2011 piqued my interest the other day.

Hardware

Consumers are certainly living their lives more and more away from a pc on their ipads and iphones, not so sure about the corps? Blackberry still doing well.

Software/General/Other

I really hadn’t expected that you would have to be sexually abused in order to get on a plane, but I did forsee an increase in regulation. Luckily (?) with the change (?) in government in the UK the rate and intrusiveness of regulation seems to have slowed down, a little. Glad I’m not a banker though.

OLAP/BI seemed to stand still in 2010 as a lot of the column inches went on security and cloud fluff. Whilst we can argue about whether user requests could get a lower priority, I don’t think anyone would say they got higher in 2010.

Android seems to have picked up some credibility, although the Motorola Droid I have is the worst electrical device I ever owned.

Santa brought one of the kids a netbook for xmas but he gave up trying to find a Linux one and just got a windas 7 one. WTF happened to Linux on Netbooks?? They are as rare as rocking horse poop suddenly. Its pretty cool here though as all the kids want Ubuntu because it has the best games, so I’ll be replacing Microsofts’ finest with NBR 10.10 (no silly name this time?), obviously without caring about Ubuntu One.

So overall, many things seem to have turned out roughly as expected, a few haven’t (yet), some might never.

I’ll do my predictions for 2011 within the next few days, although from a spreadsheet point of view its hardly exciting.

What were your standout moments/trends/events from 2010?

cheers

Simon

Test harnesses in production

Tuesday, 30th November, 2010

You wouldn’t believe how the cold dark winters evening simply fly by here (well at least I managed to get home  even though 4″ (100mm for the metricians) of snow fell this afty, the first November neige in Geneva since 1980 allegedly). And its still dumping it down – might have to throw a sickie tomorrow and go sledging with the kids.

Anyway the big argument today was test code.

Should you or should you not put your VBA test code into production?

Should you strip down your project to the absolute minimum clean prod only code?

Or should you leave in the code you used to test your production code? (assuming you are one of the 3 VBA devs worldwide who bothers to test of course)

My vote is to leave it in, and even though I was in the minority at school today that doesn’t mean I’m wrong, yet.

If your test code is crappy and distracting then yep take it out, no probs (in fact take out all the crappy distracting code), but why I think decent test harness code should be left in:

  1. It helps show what the code is meant to do
  2. It helps when you are trying to fix something later in production at a users’ desk.
  3. It shows that someone did some testing sometime
  4. If you make significant edits to your code you should retest, if that edit was removing what you thought was the test code how will you check you haven’t broken something? add a test harness???

What do you think? what am I missing that these clean code freaks can see? Remember I’m not for a minute suggesting leaving in a load of random junk scattered throughout a project. I’m thinking of separate modules or at least sections with a bunch of meaningful tests that exercise the main functionality of the system in a controlled way.

What do you do?

cheers

Simon

Excel VBA .net developer available

Monday, 22nd November, 2010

My current contract is drawing to a close leaving me available for new challenges. A casual scan of this blog and the codematic site should give an idea of the sort of work I do.

In summary: Advanced Excel development with VBA, C# and C++, Relational and OLAP databases

I normally work in and around financial services organisations, most recently commodities trading.

Happy to consider any location and remote work, currently I’m in Geneva. Contract or consulting. Available end Dec/start Jan 2011.

Drop me a line via here.

cheers

Simon

 

Performance

Sunday, 21st November, 2010

In my ADX review I mentioned that the XLL UDF performance was not as good as some other technologies. But I also mentioned that some of the other features probably more than made up for that for many people.

The reality is for Excel/Office development we have a wide range of choices, some Microsoft, some not. Some open source, some commercial.

There is not one single technology that is best at everything. Sorry if you want an easy ‘Excel is the answer, now what’s the question’ (Or VBA etc) type scenario.

I currently have live add-in projects using VB6, VBA, XLM, VSTO, ExcelDNA and XLL+, and raw C, and I guess I have VS2008 and VS2010 C# stuff going on too. And I hope to add ADX to that list soon. Within all of those I would say each one uses the right technology given the requirements. No VB.net – sorry, the improved interop in C# 2010 would put another nail in its coffin – if we could be bothered to dig it up.

The add-in tech choice is not easy, and is possible through something that a few manager types don’t really seem to understand – real world experience. Books and forums can help but its only when you try and deliver real solutions to real users with real needs that you see how the techs really work. For example I moved one of my projects off VSTO because I didn’t need the click-once goodness or the start up delay.

I’ll write more over time on the criteria I have applied when selecting the most appropriate technology, and how to spot when you are going down a dead end and how to swap. I’ll also write more detail on my xll performance tests because I have done a fairy extensive shootout.

Do I have a favourite add-in tech? No, not one, its not that easy. But if performance is critical then you have to use the C API not the automation interface. And if an attractive interface is important then you are going to need something a bit richer than XLM 4.0 dialogs. Then there is the whole pre-requisites excitement…

Do you have an overall favourite add-in tech? What other techs are you working with? Anyone doing any Java/Excel stuff?

cheers

Simon

Spreadsheet Control Projects

Thursday, 9th September, 2010

Any experienced developers out there looking for a role on a spreadsheet control project?

I have sniffs of a couple of roles here in sunny Geneva and also one in Luxembourg.

All for large financial institutions, all trading/banking, all looking at similar stuff.

  • Assess the current spreadsheet mess
  • propose solutions on a workbook by workbook basis, (either leave as is, tidy up, or migrate basically.)
  • Potentially assist/do or lead that work

Rates are market rate, between 3-600 quid depending on skill and experience. All are full time, on site, you’ll have to sort out your own travel and accom if you don’t live local to the roles. All are 3 months to start, with a good chance of an extension if you know what you are doing.

You’ll need:

  • good Excel skills and experience (min 5 yrs prob)
  • good VBA (min 3 yrs approx)
  • some none spreadsheet development experience (either relational db or .net or java or other lang)
  • full SDLC experience
  • Good financial services experience
  • Good analysis experience

If you are interested and think you meet the requirements then drop me a line with your CV and I will pass it on to the powers that be.

cheers

Simon

(send your cv to info@codematic.net)(dont send spam or viagra or ED offers – thnx)

which add-ins?

Tuesday, 13th July, 2010

I got a little fed up poking around trying to work out which version of which addins are loaded where.

I found some great starter code here (thanks guys). And added some stuff.

Its here so anyone else can use it (and I can find it wherever I happen to be), and if you have any suggestions then please leave a comment.

Sub ListAddins()
Dim oAddin As AddIn
Dim oCOMAddin As COMAddIn
Dim wb As Workbook
Dim sAddins As String
Dim RegisteredFunctions As Variant
Dim i As Integer
Dim j As Integer
Dim sd As String: sd = “|”
Dim p As Object
sAddins = “Standard Add-ins” & vbCrLf & “================” & vbCrLf
For Each oAddin In Application.AddIns
sAddins = sAddins & oAddin.Name & sd & oAddin.FullName & sd & oAddin.Installed & vbCrLf
Next oAddin

sAddins = sAddins & vbCrLf & “Standard wb Add-ins” & vbCrLf & “================” & vbCrLf
For Each wb In Application.Workbooks
‘If wb.IsAddin Then
sAddins = sAddins & wb.Name & sd & wb.FullName & sd & vbCrLf
‘End If
Next wb

sAddins = sAddins & vbCrLf & “COM Add-ins” & vbCrLf & “============” & vbCrLf
For Each oCOMAddin In Application.COMAddIns
sAddins = sAddins & oCOMAddin.progID & sd & oCOMAddin.Description & sd & oCOMAddin.Connect & vbCrLf
Next oCOMAddin

sAddins = sAddins & vbCrLf & “Xll Add-ins” & vbCrLf & “============” & vbCrLf
RegisteredFunctions = Application.RegisteredFunctions

If Not IsNull(RegisteredFunctions) Then
For i = LBound(RegisteredFunctions) To UBound(RegisteredFunctions)
For j = 1 To 3
sAddins = sAddins & RegisteredFunctions(i, j) & sd
Next j
sAddins = sAddins & vbCrLf
Next i
Else
‘do nowt
End If

sAddins = sAddins & vbCrLf & “VBA Projects Add-ins/wbs” & vbCrLf & “============” & vbCrLf

For Each p In Application.VBE.VBProjects
sAddins = sAddins & p.FileName & vbCrLf
Next

‘MsgBox sAddins
Debug.Print sAddins

End Sub

I can’t imagine for a minute that will copy out right, but I’ll load a proper version on Codematic when I have incorporated all your suggestions.

I know for example if you have a few market data apps loaded the xll reg functions list will exceed the immediate window capacity – so I just comment that out.

Improvement suggestions welcome, especially with code…

cheers
simon

Excel Dev conf details

Saturday, 12th June, 2010

The draft (detailed) agenda is here.

The event location is here.

You can get yersen booked on here. Its currently 150 quid for the day (with early bird discount), that will go up to 200 at the start of July, if there are any places left (all plus vat). I didn’t work out how to do a declining stock thingy with the codematic shopping cart so I’ll just have to warn people as we get near the room limit and then disable the shop item.

Please tell everyone you know who might be interested. I think this will be an excellent event, and the first one I’m aware of that actually addresses the business of selling Excel stuff. Add in a bunch of technical content on the latest trends in corporate development, and plenty of time for questions and chat, an informal atmosphere, and I reckon we have an excellent well rounded event coming up.

If there is something you specifically want to be covered let us know. If you have questions for the panel let us know that too.

Accommodation-wise I suspect many of us will be booking into the nearest/cheapest travelodge.

cheers

Simon

UK Excel Dev Conf Venue

Wednesday, 9th June, 2010

D’oh

so I forgot to mention where this magnificent gathering of the great and the good on Jul 12 is occuring!

Or the timings…

The game plan is for a meet and greet between 8:30 and 9:00, at 9:00 the first session will start. The sessions will be about half an hour long with additional time for questions and overruns.

The last scheduled session will finish somewhere between 17:30 and 18:00. The pubs starts at 18:01 prompt, last one in buys a round.

The venue is here. You will see they have wifi etc so if you have to answer emails to pretend you are ‘working from home’ that should be ok. (No guarantees mind, if its anything like the wifi nightmare I’ve been having recently you might be better bringing homing pigeons.)

We will be stopping for tea and coffee, and for dinner (or lunch) of course. If you want to bring cakes and/or biscuits feel free. Ross likes Wagon Wheels, I like all biscuits, even bourbons, no idea which is Charles’ fave, Charles?

We will make the resources available at the event somehow, if you want to bring a laptop to follow along that’s fine, in fact if you want to bring a desktop that’s fine too, as long as it fits on your knees…

Anymore questions, or blindingly obvious oversights let me know.

I will publish a proper timetable in a couple of days when the presenters and schedule have firmed up.

cheers

Simon