G Challenge II – Roman Numerals

Ok, in quick sucession here is another G Challenge.  I think this one might be a bit of a stinker though!

II  (2) ;p   Functions that can be called from Excel to convert a number to its Roman equivalent, and also back from Roman to Normal.

So :

2010 -> MMX

Too easy… ?

231241 -> ccxxxMCCXLI

I have yet to attempt this myself as I normally set challenges from something I have had to do in the past I have found tricky.  This came from me choosing to put the challenge number as II not 2, and it popped into my head!

Edit:  I *know* that ROMAN() exists but that only goes up to 3999, and I am interpreting this as ‘classic’ numerals.    I think having looked at it that this is a massive challenge for a learner, but I have posted it now and we can always come back to it.   I’ll need something more sympathetic for GC III

G Challenge I

Ok, my friend is trying to learn Excel VBA and get into RAD development.   I am going to set a number of challenges which I am going to call a ‘G Challenge’  😛

So the first.  Take a string in the format :

20101124_170615_dd20101124_EOD.g

where the format is :

yyyymmdd_hhmmss_ddyyyymmdd_EOD.g

In VBA I would like 2 things. 

1. The string back in the format dd-mmm-yy hh:mm
2. An actual date serial

Example above would go to 

1. 24-Nov-2010 17:06:15
2. 40506.7126736111

Go!   I would use 2 seperate functions to start off with.

Answers will be forthcoming in a post G Answer I

New Financial Product Today – PRD & Callable PRD

After all my interviewing ( more on that to come ) I have a new contract. As such, a new bank and a new lot of things to learn. So while watching a training video I cam across a mention of PRD’s and callable PRD’s. I just had to look it up. Google didn’t yield as much, but trusty ol’ Wikipedia did the job as per usual.

Power Reverse Dual – there we go.

http://en.wikipedia.org/wiki/Power_reverse_dual_currency_note

http://giddy.org/sf/articles/structured_notes.pdf

Further Excel VBA RAD Interview Questions

I had an interview today.  I didn’t do all that well at all and I got a little bit schooled.

This always seems to happen on my first technical interview with any serious technical ‘harrasment’.  I also have a load of knowledge tucked away in my brain, none of which the guy wanted to know about sadly.

Here are the things he did want to know about that I really got flustered and couldn’t answer.  Embarrasingly I gave myself an 8 out of 10, when I actually performed like a 5.5.  I think he rated himself as an 8, and then quite easily proceeded to destroy me. 😦  boo.

So… back to the hard core technical stuff for me as I spent too long this week on the finance side and not enough on the actual RAD side, silly me.

Ranges:  How do you create a range that will grow as and when data is enetered into it.

• Name the 5 types of join on a database
• how can I get a range to grow when data is added ( VBA & Excel)
• How can I change the Last Used cell in Excel.
• C++ pointers
• Basic C# questions.

Ooh, and make sure that you can eloquently discuss the previous projects you have had on your CV.  Especially be prepared to get right into the nitty gritty.  I was silly enough to let information like the usings and data types etc I was using in one of my projects I only stopped working on about 8 months ago.  Not great either.

If I have a column with data in it.

ab

cd

ef

and I want to add in another item of data, but have the range grow.  What can I do.

5 Types of Join – I was going to write the answer in here, but now I realise that what I need to do… is have a post dediected to SQL.  Any decent RAD developer is going to have to dig around in a database from time to time.  I think the problem I had is that I let the info go too stale, and didn’t keep on top of it.  I mean sure I seriously believe 100% I could do the job.  I would merely look in w3schools / Wikipedia for a few minutes and then be away and fine.  However… interviews just aren’t like that.  You need a decent answer, confident and concise right there, at the time to give the person that warm fuzzy glow that gets one hired.

Snippet :

Here are the first few things I selected to look at.   A simple glance over these for 30-90 mins before the interview would  have given me a massive boost in the interview:

http://www.w3schools.com/sql/sql_join.asp

http://www.tomjewett.com/dbdesign/dbdesign.php?page=jointypes.php

http://en.wikipedia.org/wiki/Join_%28SQL%29

Summary ( for my own revision )

Dynamic Ranges:

http://www.ozgrid.com/Excel/DynamicRanges.htm

Last Used Cell:

Run a small macro.. ActiveSheet.UsedRange.   Or you can do this in the Immediate window if you don’t want it to be anywhere in the code.

Essential material for Excel VBA RAD Development

I have been doing Excel VBA RAD ( Rapid Application Development) for over 4 years now, and I thought it would be useful to list all of the most useful resources that I have found over the years in one post.   I guess as much for my own benefit as for other readers.

Print :

Excel Development:

Professional Excel Development:

This has to be “The Bible” of all professional Excel developers.  I am being quite bold in this claim. 

Excel VBA Interview Questions (aka Things every *good* Excel VBA developer should know)

I thought that I would compile a list of things that *I* think every good Excel developer should know.   I don’t mean techniques in particular, just things that people who work with Excel VBA intensely each day should know.

Edit: I think a lot of people will be searching for Excel VBA Interview questions and answers so I have renamed the post and changed the format slightly.  I will include all the answers on a separate post so people can try these for themselves and use it to check, or just go there immediately and cheat ;-P

I’ll illustrate :   Alt+F11.  If you don’t use this, or don’t know when asked what it does, I would say you know Jack about VBA.    This is only my own opinion, but a lot of these things do come up as interview questions as they are quite esoteric and won’t really be something a pretender would, or come to think about it, could Google for when cramming for the interview.

I’ll start simple and group things up so they should all make sense.

Excel Interview Questions

Easy

• What does Shift+D do in this case?
• What does Ctrl+D do in this case?

• What will happen if I pull down on the corner using the plus symbol making the 1×3 cell deep selection grow to 1×10?

Excel VBA WTF 002

I have to say that poor old VBA and Excel gets blamed for a lot of things.   I guess its just easier for people who should never be allowed near a computer let alone develop on one to start messing about with it and produce something truly ghastly.

This story here is a great example of such evil being conducted in the good name of VBA.  ;P  THe DailyWTF is a great site and one of only 3 I can say I visit basically every working day for a quick check when I need a cheeky break.   Alex, if you are reading this I must get a sticker in snail mail soon.  I’ll collect up some stuff and send it over!

I must try to alternate these posts with something really positive or quirky that has been done in Excel VBA and has warmed a developers heart, or perhaps made the world a cooler place.