secure security

Got this recently:

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

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

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

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

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

why why?

For Each yy In Worksheets(ws).Range(Cells(8, 1), Cells(8, 160))
If yy.Value = “Stop” Then
yy.Select
jj = yy.Column
End If
Next yy

Why would you use yy ? (nervous twitch??) (or jj)

Also note how I have generously indented it for your delight, unlike the original.

cheers

Simon

Linguistic gymnastics

As I’m sure many of you know I am a fully fledged European, living it up in a multi-country, multi-culture, multi-language, (limited understanding ;-)) kind of a way.

At my current role I am running the multi language version of Office in English – which is pretty good (apart from Outlook obviously, that does not respect language settings properly). I have Windows set to English interface too, and that works well too.

Google and its spooky spammy ad network seem completely unable to respect my language choice. I would never notice this as I usually run an ad-blocker but here at work we get the full pointless hit. I had no idea the internet was so infested. Even on my blog here I have seen ads, I didn’t think it was popular enough to warrant it.

But anyway I just wasted a couple of hours of my life trying to understand why some VBA wouldn’t work.

Its something I have done 13 1/3 times before with no problem, but here, today, no go. writing a formula from VBA to a cell – no bigee.

ActiveCell.Formula = “=CONCATENATE(….”

I built up my string carefully using an in cell version as a model. I copy pasted it into a cell no bother. And yet when I tried to run it, it errored. With the ‘oh so helpful’ ‘Application or object defined error’. It would have been easier and more honest for that to read ‘Error, tough shit’

So anyway, I wondered if it was an R1C1 thing, I have been burnt by that a few times, so I changed all my code to be R1C1 stylee, and tried to set ac.formulaR1C1, still no go.

I noticed it was a long formula, so I cut out a big chunk of stuff to get it in under 256 chars, still no joy.

Then I thought maybe I had too many arguments, so I changed my code to just do 3, still no joy. In the immediate pane (pain) I tried activecell.formula = “=A1″, worked fine no problem.

So then I asked another dev to help, then, as is often the case as soon as you ask someone else, it finally it hit me: with nl settings the argument separator is a semi colon ; whereas in en it is a comma ,. Edit replace ; with , and job done, my formula worked. VBA always talks to Excel with en culture. So my simple immediate window test formula worked fine, but anything with multi-arguments failed. there is a point there about making tests realistic I guess.

So there it is that’s what I did the other morning morning, I relearnt that I need to build formulas in VBA using commas not semi colons. Of course I could ‘just’ change the windows regional settings, but anyone who has done that will know what a can of worms that opens. for example VSTO add-ins wont install, CSVs wont import, and a million other things I have not been burnt by recently, no, regional settings is best left alone.

As a matter of interest I find it very difficult to get used to entering formulas via the UI with ;’s instead of ,’s too. nearly every time I get a slap for using a comma.

Have you had any multi lingual challenges recently?

cheers

Simon

Excel 2010 TP review

[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

Smurf on 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

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