Archive for the ‘technical’ Category

GoPro SD card problems

Sunday, 30th April, 2017

Bit random for a spreadsheet blog I know…

I am putting this here because I found a fix, and I know from my own searches other people have exactly the same issue: disc full, no sd, sd err on gopro.

One of the kids deleted some files from the GoPro SD card on the mac, but didn’t empty the ‘trash’ before ejecting the card.

What a fookin pain in the arse!!! (Thanks Apple.)

Not sure exactly what misery Apple inflict on the card in this case but it really upset the gopro, really.

Initially the gopro just kept saying disc full (because macs don’t delete files, they just reclassify them – until you clear the ‘trash’)..

I reformatted the card in disk utility on the mac, then the gopro kept cycling through the above errors, either disk full, no SD or SD error.

I reformatted it in Linux, same crap, tried Windows (normally a good bet) same shit.

Of course the card was fine in other devices – I think the gopro is a bit sensitive, and a bit incapable of formatting the cards for itself. Nomally I clear the cards on a tomputer then put it in the device to format – the gopro wasn’t able.

The SD card seemed to have picked up a second partition somehow, but even after removing that the gopro wasn’t happy, a few resets didn’t help.

Then I saw a post suggesting this. A specialist SD card format tool from the SD Association. Its a free tool that does what it says – formats SD cards – but seemingly better than everyone else. It took about 5 mins, instead of the 30 secs with all the other things I tried. But IT ACTUALLY WORKED! Yay.

The whole episode cost me a good few hours of buggering around, and I was on the verge of just retiring the SD card or saving it for a gadget with better formatting capabilities. I hate it when technology beats me so I was pleased to get it sorted.

So anyone else having SD card problems with their gopro check out the SD card formatter from the SD Association (I am assuming it hasn’t opened up some GCHQ/NSA backdoor on my pc!)

cheers

simon

#import MFC

Sunday, 16th January, 2011

Whoever designed the #imports functionality in VS2008 and the way it interacts with MFC owes me a weekend.

I have been migrating a big VS project to VS2008 and have had some real fun managing all the includes and imports.

When I did the codematic base xll project I was very careful to structure things so as to avoid header hell. The original developer of this project took another route.

I was blocked by a stray windows.h include. So I listed them – all 25,000 includes! there were 20 windows.h ones. And the stray one was early meaning a later MFC check failed. In the end I managed to shuffle all the different parts around to get it to compile. But what a PITA. And its always depressing when you go from one compile error to 941!

The stray one (in OLEDB) was about 9 include levels down so trying a few COM_NO_WINDOWSH type guards actually made things worse. Finally it turned out that I needed to move one of the #imports into stdafx.h and leave the other 2 in their original .h.

So the original problem was that function names were not appearing in Excel 2007, that was an easy fix, involving upgrading the XLL+ version to one that supports 2007. The hard work was in the C++ include dance to get the thing to compile. The actual project is very interesting, messing with the arcane header include rules in C++ less so.

Lucky there isn’t much snow, or I’d be really bitter at missing some decent snowboarding.

What did you get up to this weekend?

Cheers

Simon.

Add-in Express Review

Wednesday, 17th November, 2010

In amongst all my other busy-ness I have been reviewing a few Excel developer tools, or in some cases Office developer tools.

Addin Express is definitely more of a general Office development tool than Excel specific, but it does create xlls which was my main motivation for the tool reviews.

Addin Express (which I will call ADX from now on) has been recommended to me by several of my Office developer buddies, so I was keen to try it out. For anyone who was at the UK Excel developer conf in London in July you might remember that I demoed creating an RTD function using ADX and it is very simple.

As I said my main initial interest was xlls, and so thats where I’ll start.

ADX has a novel drag and drop approach to xlls, but before we get to that, when you create an xll project you get to choose which language from VB, C#, C++, J# (remember J++??), or Delphi. And on the first dialog is where you get the first hint that an ADX xll is not like an XLL+ xll for example, it will need a proper install (setup project).

Next you get a further hint that this xll isn’t just a native dll with some Excel specific exported functions:

There is some serious COM/.net plumbing magic going on in the background. The big point to note here and one of the biggest appeals of ADX (IMO) to an add-in developer is the ‘version-neutral interop assemblies’ choice. That gets you the ADX IA’s which allow your .net code to work with any sensible version of Office. I’ll talk more about this later because, we would expect an xll to work with every version of Excel since 97 anyway.

Click finish and you drop into code view,

double click on the XLLModule.cs in the solution explorer, and drop pane appears where you can drag UDFs

For a hand coder like me its all a bit novel, but for a more normal, visual developer I think it would be far more second nature.

When you open the toolbox (there’s a clue – I never use it, as I never do UI development).

Here you will see a region titled ‘define your UDFs in this section’, which to be fair sounds like a pretty good place to define a function. Inside is a sample function you can uncomment or just copy the principles from. Its pretty easy, none of the C++ data type pain, use standard .net data types and ADX will do all the marshalling for you.

Its a static class so you can either define all your functions as static now or wait for the compiler to tell you to later .

public static double addSome(double d1, double d2)

{

return d1 + d2;

}

That’s C#, without wanting to start a fight (much ;-)), I think VB.net is a lost cause. Nothing wrong with the technology, I just think its future is somewhat less certain than C# or C++.

Anyway from an ADX pov we now need to get this function visible in Excel correctly, and for that they use some drag and drop GUI loveliness. Double click or right click the XLLModule.cs to view its designer surface, then right click that and ‘add an Excel function category’.

You now have a button like thing whose properties you can set as if it were a button, mine is called clever functions. This is the function category you will see if you click the Fx in Excel, so its worth doing something notable.

To this category you then assign your functions, this is a good hierarchical approach to building out the functionality of a non trivial add-in as you may want to categorise your functions into usage groups to make it easier for user to find and use your functionality.

When you right click and select Edit functions you get to fill in this dialog:


You add you description then assign the functionName (from those functions you already coded in the XLLModule class) that you are talking about. If you have a help file then you can connect the function to it here. Hidden and Volatile are best left as false.

You can repeat this process until you have all the functions you want to expose to Excel in the left hand pane of the dialog above. The next step is to explain the detail of the parameter of all these functions. The order you do it is not important, but you right click on a function in the left hand pane and choose Add Function parameter.

You have now defined a function that takes 2 doubles and returns a double. The current implementation in the old C# just adds them but we can change that later without going through all this interface stuff.

When you are happy with your functions and want to try them in Excel you need to build and register the add-in. Build, Fair enough create some binary thingymabob, register? Well, these ADX xlls, as I mentioned before are not ‘pure’ xlls, they need some registry magic to work correctly.

Click Build>>Register ADX Project to set up the registry side of things, from now on you just need to rebuild as normal.

To debug you will need to set Excel as the start up program

When Excel starts your xll is loaded automatically, no security warnings, no searching in \debug to load it manually, and all the functions are ready and available.

(Is your add-in list that clear??)

Actually when you look in the add-ins list in 2007 (assuming you are (un?) lucky enough to working in ‘Excel Vista’, you will see the add-in is actually a dll, although ADX exposes it as if it were an xll/xla(m) one. IE its in Tools>Add-ins rather than COM add-ins.

So the key question for xll johnnies

Performance?

In my very limited testing I reckon that an ADX xll is about 3 times faster than VBA, which is pretty reasonable. It was close to 5 times slower than my test raw C xll which isn’t so brilliant.

I will add reviews of the other feature as I get chance to investigate. I can’t leave though without a little moan about the ‘getting a copy of Addin express’ process. I downloaded the trial, with the plan of installing it and reviewing it at the airport. I was somewhat fed up to find that you can’t install it without a key that you need to download. Without a wifi connection that meant I had to buy a mountain bike magazine to fill the hour wait instead. I’m not sure what chance I’ll get to look at other features as I wrote version one of this review several months ago, and I think my trial will have expired by now.

Summary/conclusions

An ADX xll is a bit more effort to deploy than a native code one, and the performance is not as impressive. But it does offer easy access to the full COM OM, unlike native code xlls targeting the C API only.

The choice is yours though really, ADX is easy to work with, version independent, targets all add-in technologies and all Office products, shows a significant performance improvement over VBA, and is reasonably priced. And offers improved security of VBA, which seems to be a fairly common question I get asked at the moment.

I think its a very valuable tool that every serious Office developer should have in their toolbox. You can download the docs here, they don’t really do a trial version, but you could try asking if you are serious.

I know lots of you are already using Add-in express, what are your views/experiences?

cheers

Simon

The Volatility Virus

Wednesday, 3rd November, 2010

I mentioned in a recent post about some hassles we had with RTD. Here is a bit of background.

In a daily reporting environment it’s pretty common to use =NOW()-1 or better still =TODAY()-1, or even better still (in 2007 anyway) =WORKDAY(TODAY(),-1) to get yesterdays date to run your close of business reporting.
All of these are volatile, and in the grand scheme of things that’s probably not a massive issue. They are volatile because they have no way of checking if their inputs have changed, mainly because they don’t have any. So they have no choice but to calculate each time Excel does.

Unfortunately volatility is like a virus, a bad one, oh I don’t know, like say that flu bug that sir cough-a-lot at the desk next door has. Every function that depends on a cell that contains a volatile function becomes sneezy volatile.

Here is what I did to test that hypothesis:
I created a xll udf, that takes one parameter that it ignores completely, that logs each time its called.
I pointed it a static cell, change an unrelated part of the worksheet and the function is not called. I take this as evidence that my udf is not naturally volatile.
I then entered =NOW() in an unrelated cell, my function is not called
I then enter = NOW() in that precedent cell, now any change anywhere in that excel session causes =NOW() to update, which causes my udf to log another call. My udf argument is declared as an integer (J) so the bit of =NOW() that makes it into the function never changes, but unfortunately Excel is not checking that level of detail and calls my udf anyway.

I also checked about 20 levels of indirection and got the same result – the whole stack becomes volatile. It might only infect udfs, I don’t know, I’m not sure how I could reliably test native functions, any ideas?

None of this is a catastophe until your function return causes a recalc – exactly what RTD does.

XLL PLus to the rescue again.
So we have this common use case where people use a volatile argument to an rtd function. The RTD return will trigger a recalc which will update the volatile function (potentially with the exact same value). That cell update will trigger a recalc of all dependent cells which will trigger an RTD call…
So one solution is to avoid calling RTD if the inputs haven’t materially changed. And this is exactly what you can do with a couple of clicks in XLL+. You just click the ‘cache results’ checkbox and first time through your RTD version will call, next time XLL+ will take the value from the cache without calling RTD, no return, no recalc – problem fixed.

I’m sure there are plenty of other ways around this, but fundamentally I think in Excel 2007 they will all depends on the principle of avoiding that second RTD call by checking nothing important has changed since the last time that function was called with those parameters. Or mandating that users don’t use volatile functions as input parameters (good luck with that…).

Interestingly we started to go that route, and came up with a LastValidAsOfDate(ticker) function. And guess what? we realised we needed to make it volatile to update overnight…

I wonder if a more finely grained volatility is needed? something like the throttle that RTD already has but maybe update every hour or something? But then would you have different levels of volatility, how would you tell Excel the update frequency? Perhaps Excel could always check the first argument of volatile functions as use it like a timer?

I don’t know the answer but as it is in Excel 2007 the RTD implementation is pretty fragile, I think. Unless I’m missing something?

Anyway another big thumbs up for XLL+, the more I use it the more useful I find it.

Have you experienced this issue? Any suggestions? A better way to get some asynchronousicity into Excel udfs?

cheers

Simon

ExcelDNA is mint

Sunday, 10th October, 2010

I was somewhat despondent recently when I realised my big Excel add-in project was going to need some sort of install.
I had picked up a few dependencies that would not be on non dev boxes so it looked a bit like I was going to need a proper set up. Exactly the sort of thing us Excel types hate.
But then I remembered that ExcelDNA can pack everything into the xll, not just my .dll.
a quick edit of the .dna later:
<DnaLibrary>
<ExternalLibrary Path=”my.dll” Pack=”true” />
<ExternalLibrary Path=”dao.dll” Pack=”true” />
<ExternalLibrary Path=”Interop.ADODB.dll” Pack=”true” />
<ExternalLibrary Path=”Interop.ADOX.dll” Pack=”true” />
<ExternalLibrary Path=”Microsoft.Office.Interop.Excel.dll” Pack=”true” />
<ExternalLibrary Path=”Microsoft.Vbe.Interop.dll” Pack=”true” />
<ExternalLibrary Path=”office.dll” Pack=”true” />
</DnaLibrary>

and we are back to a single xll file deployment. youpeee!

I have the following as a post build event:
C:\…\ExcelDna-0.27\Distribution\ExcelDnaPack.exe “C:\…my.dna” /y

I think ExcelDNA packing is mint.

Are you using ExcelDNA? What for?

(Can you get packing to work??)

cheers

simon

Excess

Wednesday, 22nd September, 2010

And the other way:

public void ImportResFromAccess2Excel(xl.Range Dest)
        {
            object oo = Type.Missing;
            //int recCount = 0;

            MakeADOConnection();
            ADODB.Recordset rs = new ADODB.Recordset();

            rs.Open(“qryFinalReportView”, mADOConn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, (int)ADODB.CommandTypeEnum.adCmdStoredProc);
            Dest.CopyFromRecordset(rs, oo, oo);
            rs.MoveLast();
            mDataRowCount = rs.RecordCount;
            mDataColcount = rs.Fields.Count;

            rs.Close();
            CloseADOConnection();
        }

Via the gift of an ado recordset.

the conn string for that is

mADOConn.Open(“Provider=Microsoft.Jet.OLEDB.4.0;” + “Data Source=” + msDBPath + “;”, “”, “”, 0);

Thats against an mdb.

btw I made the mistake of copying that directly from the VS IDE – turns out thats almost as bad as Word for scabby random html. I repasted it through my current fave text editor (Notepad++)

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

Excel DDE

Monday, 26th April, 2010

Just recently we have been having some fun with DDE at work.
For those of you young enough to not know what DDE is – well done.
DDE (Dynamic data Exchange) is a 1980’s/1990’s technology for enabling disparate systems to pass data between themselves. As with most Microsoft technology of that era they favoured ease, and getting the job done over security. Hence they now discourage its use.

Of course that doesn’t stop most of the market data providers using it.

Anyway, long story short, I wrote a server in VB6 so I could play with it in Excel and I wrote about it here.

I had a quick look around and it didn’t seem that obvious how to write one with C#, anyone got a link?

It seems that if you have a bad enough Excel crash you can hang the whole DDE messaging process, well somebody can, in our case we suspect IE.

Anyone else doing working with DDE?

Anyone actually developing a DDE server app?

cheers

Simon

xlls With ExcelDNA

Thursday, 18th February, 2010

Excel DNA is a free open source tool to expose .net UDFs to Excel via the xll interface.

The code is here, the blog is here. The forums and discussions are here.

It has been my destiny to try ExcelDNA for a very long time, 2 or more years I think it’s been on my todo list. The primary blocker was the need to have .net 2.0, and me keeping my main dev machine at 1.1 for client compatibility reasons. Anyway, new year, new machine, new approach. I’m on Office 2007 and Visual Studio 2008. So Excel DNA is suddenly on my radar.

Excel DNA has 2 main modes, text file mode and .net assembly mode.

In the first one of these Excel DNA is effectively 3 components [EDIT: My bad – as Mike and Ross and Govert have pointed out it is 2 Components because you don’t need to distribute first one]

  • The ExcelDNA.Integration.dll assembly to glue the next two bits together
  • The ExcelDNA.xll caller
  • The UDF in a text file.

To work with ExcelDNA in text mode you only need notepad, or a decent text editor (Notepad++ for example). Visual studio might be handy, but is not essential.

You put the 3 2 components in the same folder, rename the .xll to the same name as the text file. Give the text file a .dna extension, double click the .xll and enable macros and your functions from the text file are there. The code in the text file can be written in any .net language. The demos have VB, C# and F#.


Once you open the xll ExcelDNA registers all the functions it finds in the .dna file. Note there is no direct file lock on the .dll. This means you can code and refresh the .dll without restarting Excel – this can be really handy. I guess if you go and change the parameters or return type you’ll be in for some fun, but for changes to the implementation this is a real time and frustration saver.

In the .net assembly mode there are 4 3 components, the 3 2 from above and the .net assembly. But now instead of the .dna text file containing the function, it just contains a pointer to the .dll assembly, that contains a compiled class library. These can be marked threadsafe, as can the text files I think. This approach makes a big difference, in my limited testing a threadsafe .dll calculates in about a quarter of the time of the text file approach, and this puts it about twice as slow as raw excel or a C xll, close to 10 times faster than VBA.

If intellectual property concerns are an issue then you need to go the .net dll approach, and trust in a .net obfuscater.

For the ease of use, the fact you don’t need Visual Studio, or if you do use it to get the fastest performance, you only need a free Express edition, Excel DNA is extremely impressive.

To create a faster than VBA udf you just need to copy your VBA out to a text file, add a couple of flags, make sure its valid VB.net, change the file name and the .xll name, open the xll and you are away. If you have .net code to expose to Excel just compile it and point the text file to it. My results were a little erratic with the class library I was using, so I’ll need to check that again. As the whole thing is open source I can always trace things right through to understand what the issues are. Bear in mind it is still under development…

[Edit: Multi-Threading could be unreliable in certain situations in 0.21, this is fixed in 0.22]

Deployment is straightforward too, there are several files (3 or 4 2 or 3) that need to end up in the same folder, .net 2.0 or later is needed. But there is no registry messing required, no formal install.

ExcelDNA also covers command based stuff, but I didn’t test that for this review as my focus for now is mainly on UDFs.

ExcelDNA is targeted towards Excel VBA devs who want better udf performance, and it delivers that well. I think its something that deserves a bit of investigation for many Excel VBA devs because it is so accessible.

In summary:

  • Simple deployment
  • Good performance
  • Simple development
  • Simple to expose existing .net resources to Excel
  • Free and open source
  • Also has command features, that I did not test yet.
  • Choice of .net language

On the downside:

  • Still under development
  • Multiple file approach may not suit all situations
  • Lifetime management might be a pain, as with most add-ins

Are you using ExcelDNA?

What are your experiences?

cheers

Simon

Xlls with Planatech XLL+

Wednesday, 17th February, 2010

A 30 day trial is available here.

Pricing is here, (about 1,100 USD/700GBP for a single dev license).

XLL+ is a C++ based Visual Studio Add-in for writing xlls. That means you need VS Professional not an Express version. It’s also C++, if that moves it outside your interest area, I would say read on don’t skip the rest of this – perhaps there are features that would make it worth your while to invest in C++.

XLL+ adds a new xll project type.

As you are setting up the project you need to consider which advanced features you may want to include

I’ll discuss some of these below, the defaults are fine for most simple xll projects, but you may need to make changes here for some of the more powerful features. This is a review rather than a tutorial though, so I won’t be covering all. The product comes with good help files, and they can be downloaded separately if you want to check then out before downloading the tool.

In one of these XLL+ projects you can use tools>>New XLL+ Function to get a simple wizard to guide you through creating a udf (or command).

There are some incredibly powerful features here just exposed by a single check box.

Cache results, for example easily allows your function to hold a local copy of its results to save an expensive database round trip for example. The wizard generates the code to check the cache first, only calling your function if needed. I guess you have to decide how the cache gets invalidated.

There are others like asynchronous functions, this will be a new integral part of Excel 2010 and the 2010 SDK, but with XLL+ you can use it in every version of Excel. With an async function the calc sets it off, and its return value is initially ##NO DATA## or something, then at some future point when your function has finished calculating it calls back into Excel to update that value to be the real result. This is very valuable for slow functions.

Once you get through the wizards and drop into the actual code (C++ as mentioned before) the most obvious thing is that XLL+ has generated two versions of your function: a pre 2007 one, and a 2007 one. This saves a major pain if you are targeting multiple Excel versions. It has also sorted all the registration issues to get your function correctly registered in each version on Excel. This can be changed via the wizard for example if you want to add arguments or change data types. Helpfully it wraps it all up in exception handlers too.

XLL+ then provides a bunch of useful classes to interact with the Excel host, some of these are not available in UDFs, but XLL+ can also generate commands (the equivalent of the Sub in VBA). In many cases manipulating the Excel OM through the C API is staggeringly fast compared to the VBA equiv. For example the worksheet password remover here (written without XLL+) is an order of magnitude faster than a VBA or VB6, or .net version that uses the COM OM. I recently did some styles stuff in XLL+, again 10-100 times faster than the VSTO equivalent. XLL+ doesn’t give full coverage, but it allows raw calls to the underlying SDK for anything that is not common enough to be included.

At the end of the day though you are going to need to write your udf in C++, unless….

You decide to make use of the ‘new-in-the-latest-version’ feature to wrap .net functions and pass them through the xll interface. For an enterprise with an existing codebase in C# being able to expose those through the fast xll interface instead of slow automation functions is a massive benefit. Deployment is easy too, just put the xll and the .net assembly dll it wraps in the same folder and you are done, no registry, not path hassles, it just works. There are limitations around the data types the .net component exposes and whether they can be converted effectively to types the xll interface can pass. The end result is a little slower than a pure C/C++ xll, because there is still some marshalling from native to managed, but its much faster than the automation approach because there is no COM layer in the way.

This would also work if you code your udf in VB.net of course

XLL+ also provides logging out of the box so you can keep track of whats going on, and there are basic performance profiling features too. And as it is all written in C++ you can extend as you see fit with your own, or someone elses C++. The other big (and somewhat advanced) feature is handles, XLL+ makes it very simple to create a large object in the xll memory then make various bits of that available to Excel cells.

I would say XLL+ is certainly targeted more at the advanced end of Excel developments, its not really a hobbyist’s toy. If you can see the benefits to the work you do then you really need to set aside something like a week to get to grips with the product and start to understand how you can best get it to work for you.

In the past I have just fired it up and hacked together a UDF that does what I want, roughly. These last few weeks though I have spent much more time reading the help files and trying the samples, even those that do not seem relevant to my current projects. I now have a good sense of what the product can do and how best to make it do it.

I compared XLL+ performance to one of my hand written C UDFs and was shocked to see XLL+ was faster. What’s this I thought? all that clunky C++ faster than lean mean C? then I realised XLL+ had registered its version as thread safe and I hadn’t with mine (yep thats right, I’m in E2007 at the moment). Once I fixed that they were identical, except the XLL+ one was easier to write (and a much bigger file: 500kb v 48kb).

For me killer features are

  • Simple initial deployment
  • Excellent performance
  • Easy to develop compared to raw xlls
  • Ability to wrap .net dlls and pass them through the xll interface for decent performance (way better then VBA for example)
  • Easy Excel version neutrality
  • Automatic help file generation

The biggest downsides are

  • Need strong C++ to get the most from the product (perhaps not if you purely wrap .net assemblies)
  • The way the Excel OM is exposed is quite a leap for a VBA dev.
  • Significant learning curve for Excel devs

In summary I think XLL+ is perhaps aimed more at enterprise type C++ devs needing to expose their powerful C++ libraries to Excel users, rather than Excel power users and developers. The latter will need to invest in some C++ to get the best return on their financial investment if they buy the product. (And good luck getting C++ training!) That said dedicated performance buffs should seriously consider making the investment.

Have you used/do you use XLL+? what do you think?

Next up (in a couple of days) I‘m going to take a look at the opposite end of the cost spectrum.

Cheers
Simon

ps I’m going to do a proper performance summary in a week or so once I have reviewed all the tools in my list.