I wish I knew VBA!!

[xoggoth]

Yeh, VBA is generally about as easy as it gets. I tend to do every little utility in Excel/VBA, compare files, bulk file rename, file backup, check the lottery, read old mail archives, alarm system to tell me when The Simpsons is on...

In professional use it's not bad for testing either when the client does not have proper testing software. All the scripts/HTML files I use in small business software are generated from excel. Have demo progs in excel we use at shows that display screen shots from programs.

[msubhan]

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?

It doesn't matter (generally) what language the code is written in...if the developer doesn't have any logic skills then they'll write crap code all day long.

[xchaotic]

Not really - because

1 - often a mistake was spotted after the update had been done and had to be rectified meaning another 15 min delay. Even opening the sheet with the calculation on would cause a 15 min wait while Excel checked that the values hadn't changed
2 - often they would forget to switch the calculation on again which ment subsequent spreadsheets were saved with the calculation off.
3 - my solution was simple and easily auditable. The developer solution was not. There was no visability as to where the data had come from.

But then I'm not a proper developer!

I'm just guessing wildly here, so please bear with me, but perhaps this is not an ACID transaction anymore and you're working on locally cached copies of values stored in the DB which will yield the same results 99% of the sime, but just sometimes it will just be wrong?

[BoredBloke]

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

Well done.

Not really - because

1 - often a mistake was spotted after the update had been done and had to be rectified meaning another 15 min delay. Even opening the sheet with the calculation on would cause a 15 min wait while Excel checked that the values hadn't changed
2 - often they would forget to switch the calculation on again which ment subsequent spreadsheets were saved with the calculation off.
3 - my solution was simple and easily auditable. The developer solution was not. There was no visability as to where the data had come from.

But then I'm not a proper developer!

[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.

[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?

[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.

[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.

[Lockhouse]

WHS

[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

[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 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

[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.

[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?