I wish I knew VBA!!

[joey122]

I come from a Java background and it seems a ton of jobs with Aston Carter are now advertising for people with VBA experience.

I guess companies are trying to build software of the cheap

Has anyone else noticed this shift? Or is just me?

[NotAllThere]

If you can do Java, then you can do .net. And if you can do .net you can do vba. Anyone can do vba... just recording macros, ffs.

[BrilloPad]

I come from a Java background and it seems a ton of jobs with Aston Carter are now advertising for people with VBA experience.

I guess companies are trying to build software of the cheap

Has anyone else noticed this shift? Or is just me?

Its just you

HTH

[joey122]

If you can do Java, then you can do .net. And if you can do .net you can do vba. Anyone can do vba... just recording macros, ffs.

I looked into this yesterday and its not just recording macros - There s a pretty complex object model that you need to get a handle on and the way that Excel deals with updates and calcs is not always straighforward

[BrilloPad]

I looked into this yesterday and its not just recording macros - There s a pretty complex object model that you need to get a handle on and the way that Excel deals with updates and calcs is not always straighforward

[Lockhouse]

WHS

[BoredBloke]

I'm working at the moment rewriting a load of Excel VBA and much of it was written by the clients proper .net developers and it's bollocks. We had one instance where a guy wrote a function to pass variables from the sheet to a SQl string which went off and passed a query to a database. That bit worked fine, what wasn't so good was that he used his function 2000 times in the spreadsheet. So each time you made any modification excel would tell all the cells to recalculate and his function setup the link, searched the database and then closed the link. This would lock excel up for 15 mins at a time - and he passed it on in that state.

My change was to setup the link once, import all the data once and then close it. Then Excel functions could do the rest. The result is my copy takes about 15 secs to update while his took 15 mins. On mine you can make changes while on his you had to turn off the calculation before doing anything (which can be dangerous) or it was a 15 min per change wait.

[DimPrawn]

I'm working at the moment rewriting a load of Excel VBA and much of it was written by the clients proper .net developers and it's bollocks. We had one instance where a guy wrote a function to pass variables from the sheet to a SQl string which went off and passed a query to a database. That bit worked fine, what wasn't so good was that he used his function 2000 times in the spreadsheet. So each time you made any modification excel would tell all the cells to recalculate and his function setup the link, searched the database and then closed the link. This would lock excel up for 15 mins at a time - and he passed it on in that state.

My change was to setup the link once, import all the data once and then close it. Then Excel functions could do the rest. The result is my copy takes about 15 secs to update while his took 15 mins. On mine you can make changes while on his you had to turn off the calculation before doing anything (which can be dangerous) or it was a 15 min per change wait.

Now the client has 14m 45s in which to spend thinking about how to save the company money by shedding more staff.

Well done.

[lilelvis2000]

I looked into this yesterday and its not just recording macros - There s a pretty complex object model that you need to get a handle on and the way that Excel deals with updates and calcs is not always straighforward

Ummm...errr..I do a lot of VBA and it is anything BUT complex.
Sure it can get a bit confusing in Excel with looping over ranges, but nothing a good programmer can't figure out.
Easiest way to learn is to record a macro.then look at the code. . Then just go in and modify the bits you need. Heck! I build some of my code that way..saves a lot of time.

Is this not a technical discussion and is sitting in the wrong forum?

[lilelvis2000]

I'm working at the moment rewriting a load of Excel VBA and much of it was written by the clients proper .net developers and it's bollocks. We had one instance where a guy wrote a function to pass variables from the sheet to a SQl string which went off and passed a query to a database. That bit worked fine, what wasn't so good was that he used his function 2000 times in the spreadsheet. So each time you made any modification excel would tell all the cells to recalculate and his function setup the link, searched the database and then closed the link. This would lock excel up for 15 mins at a time - and he passed it on in that state.

My change was to setup the link once, import all the data once and then close it. Then Excel functions could do the rest. The result is my copy takes about 15 secs to update while his took 15 mins. On mine you can make changes while on his you had to turn off the calculation before doing anything (which can be dangerous) or it was a 15 min per change wait.

Which just proves that you can still write crap code in any language.