Thanks to rorya I have worked through Chip Pearson's tutorial on creating COM AddIn with VB6. It's a good example of Automation Add In.
I'd need to go a little further. I read somewhere that you can create VBA code for Excel from VB6 itself. That is, so I can debug and (hopefully) have Intellisense also. Is this possible? If so can someone direct me to a learning source please?
Info: So far, I have copied and pasted VBA code from the VBE to the Class in VB6. I can make simple things like 'show a message box' work. For data access with ADO etc. (which I can do in Excel VBA) I need to be able to write my code in VB6 just like I do in VBA, test etc. (This is so that I can make a COM AddIn to work with Excel 2010)