# Tell me why I don't like Tuesdays.



## Patience (Mar 3, 2009)

We get reports from our finance department - downloadable from the internet. Straight to Excel. 'Great!' I thought. But then I realised they were formatted in the most ridiculous way, with random blank lines, irritating subtotals in odd places, blank columns, and titles in stupid places.

With the help of some here, I wrote a long piece of code that cut these horrid reports about and stuck them back together into such a format that I could put into a yummy pivot table. And so I felt good about myself.

Until today.

Tuesday 3rd March 2009.

Today I am asked to do the same sort of analysis for a different account code, with income that comes from a different source. 'Easy,' I thought. 'I'll just run that handy piece of code that I created, with the help of those nice people over at Mr Excel.'

Tell me, just why would our accounts department see fit to format the reports for a different funding source so *COMPLETELY DIFFERENTLY* from the ones I have already done??? Why now put in only one blank line between rows, rather than your ususal annoying two? WHy would you now choose column J to put your meaningless titles in, rather than the equally pointless column K???

Now I have to start *ALL OVER AGAIN!!!* 

Lesson I have learnt - I need to learn more about making code flexible. So, every cloud...


----------



## ShaunD30 (Mar 3, 2009)

Patience...I feel your pain..

Shaun


----------



## cornflakegirl (Mar 3, 2009)

[sends Bryony virtual gin]


----------



## Patience (Mar 3, 2009)

Thanks guys. Support is appreciated!

I am realising that it is a seriously good idea labelling my code to tell me exactly what it is doing, as I can at least copy and paste chunks of it - and I have been having a good laugh at some of the labels. Clearly I was quite frustrated the first time round...


```
'Remove all the irritating formatting

'Deletes all the annoying Totals

'Move the analysis codes somewhere useful

'Get rid of those pig awful blocks of blank rows
```


----------



## cornflakegirl (Mar 3, 2009)

Patience said:


> ```
> 'Get rid of those pig awful blocks of blank rows
> ```



I particularly like that one!


----------



## Jon von der Heyden (Mar 3, 2009)

You should put those comments in the status bar


----------



## Norie (Mar 3, 2009)

Bryony

Your finance department posts the data on the Internet.

Isn't that a bit risky?

Maybe you should have a word with them and ask them to send an email with one of those new-fangled attachment thingies.

Probably won't make any difference to the formatting problem, but you might also ask them to have a look into that at the same time.


----------



## Patience (Mar 3, 2009)

Sorry - yes, they are internal browser based things that come with accounts packages. Not posted for all to see!


Please can someone with the power to edit please edit the fist line of my post? Thanks!


----------



## SydneyGeek (Mar 3, 2009)

> Please can someone with the power to edit please edit the fist line of my post? Thanks!


Is that like fist, scond, hird? 

Denis


----------



## Patience (Mar 4, 2009)

Yeah, yeah!  Anyways, I checked with them, and all is fine! I had a huge hit of paranoia yesterday afternoon, and all is fine.


----------



## hatman (Mar 4, 2009)

Is it paranoia when they are REALLY out to get you?


----------



## arkusM (Mar 4, 2009)

Patience said:


> Thanks guys. Support is appreciated!
> 
> 
> 
> ...


 .


I too feel you pain...... 

Oh the stories that could be told over a pint. (but then would I feel like such a "nerd")


----------



## Long Nose (Mar 4, 2009)

Is the silicon chip inside your head switched to overload?

Why not just tell finance how you want to see the reports and in a consistent format?  Is it possible that they just don't know how easy it is to turn a "good as gold" girl into a code killer.


----------



## hatman (Mar 4, 2009)

Long Nose said:


> Is the silicon chip inside your head switched to overload?
> 
> Why not just tell finance how you want to see the reports and in a consistent format? Is it possible that they just don't know how easy it is to turn a "good as gold" girl into a code killer.


 
You know, they may not even realize that anyone is trying to use the output for anything useful.  I'm sure that somewhere there is a software requirements spec that simply states the output must be available in a format exportable to Excel... and since they don't use it themselves, they just built something as fast as they could, with default formatting.  You could be a hero if you offerred to fix it FOR them, so all of the users have something useful...


----------



## Michael M (Mar 4, 2009)

Bryony
It's really quite simple.
They sent a report, you fixed it !
You told someone you had fixed it, they told finance !!
Finance changed it!!

Remember Petronious ??

Regards
Michael M


----------



## Greg Truby (Mar 5, 2009)

hatman said:


> You know, they may not even realize that anyone is trying to use the output for anything useful.


Of course, that implies that the output itself ain't useful 'til Bryony get a hold of it.  But Paul's correct, it is entirely possible that those who spec &/or code the output are oblivious to downstream usage. There exists a possibility that you might be able to avoid future snafus by talking to them. In theory if they recycle the code, the output would be almost identical.


Patience said:


> Tell me, just why would our accounts department see fit to format the reports for a different funding source so COMPLETELY DIFFERENTLY from the ones I have already done??? Why now put in only one blank line between rows, rather than your usual annoying two? Why would you now choose column J to put your meaningless titles in, rather than the equally pointless column K???


Don't take it personally. It was simply your turn. There is a worldwide conspiracy by the IT pro's to irritate us "VBA hobbyists" by doing exactly this sort of thing. I believe their targets are to exasperate approximately 250,000 VBA coders per week or about 1 MM per month give or take a bit.


Patience said:


> Lesson I have learnt - I need to learn more about making code flexible. So, every cloud...


And see, now you'll believe us when we tell you not to embed "magic numbers" down deep in your code and try to corral them up top as constants. :wink:


----------



## Patience (Mar 9, 2009)

Well that is all very good advice. I know for a fact that my department has the most sophisticated finance people (by which I mean me and my boss - which is me and my boss more than any other department) so maybe they don't realise. I think maybe till now people merely ran the reports and printed them off as PDFs (which is an option). But that was before me! I think all departments will have financey type people by the end of the year, so with any luck there will be more need for consistent reports like these. I need to plan my strategy!

Greg - I am glad it wasn't personal. I guess it won't be y turn again for a while, now, eh? (If only!)


----------



## Patience (Mar 9, 2009)

arkusM said:


> .
> 
> 
> I too feel you pain......
> ...



You see, it doesn't matter if you are all nerds together.

When someone calls me a 'nerd' I look at them, and very patronising say "My people prefer to be called 'geeks'".

I find there is no answer to that.


----------



## cornflakegirl (Mar 9, 2009)

Fab line Bryony - I'm definitely stealing that one!


----------



## Greg Truby (Mar 9, 2009)

Patience said:


> When someone calls me a 'nerd' I look at them, and very patronising say "My people prefer to be called 'geeks'".


I rather like that. 

Next February or so, send Richard &/or Rory a PM and see if they will pick up a new "geeks rule" coffee mug or thermos from Microsoft for you at the next MVP summit. :wink:


----------



## gardnertoo (Mar 9, 2009)

> "My people prefer to be called 'geeks'"



A new Mr Excel t-shirt / mug logo perhaps?


----------

