3 ms·

knowing Excel better than most (hardly coding I know) and a little bit of VBA There's no question that spreadsheets are programs, albeit not written in a gener

by gruseom 15y ago

knowing Excel better than most (hardly coding I know) and a little bit of VBA There's no question that spreadsheets are programs, albeit not written in a general-purpose language. Question for you: what do you do in VBA? i.e. in what areas does the spreadsheet alone not suffice?

kapilkale 15y ago
Most of the time it is because the existing functions are inadequate, so people end up adding new ones in VBA. StringConcat, for example, is far more powerful than the regular concatenate function. http://www.cpearson.com/excel/stringconcatenation.aspx http://www.cpearson.com/excel/stringconcatenation.aspx
- gruseom 15y ago
  When you say "function" you mean something that just does computation, right? As opposed to code that accesses the Excel object model to create interactive effects, like, say, "click this button to change the background color over there"? How much VBA could be replaced if Excel gave you the ability to make functions just out of spreadsheet cells? That is: build any calculation you like in a spreadsheet, tell Excel "make this calculation be a function called FOO", then use it wherever you want like a normal function: "=FOO(123,A1)" or what have you.
  - kapilkale 15y ago
    Yes on the definition of function. I did also use VBA to make certain visual effects possible (the one I remember was some advanced conditional formatting). On VBA being replaced-- I don't know. It depends who the users you're talking about are. Most people I worked with didn't know any VBA because our models weren't that complicated. I in particular was working on a project that was computationally heavy.
    - gruseom 15y ago
      Can you describe the computationally heavy spreadsheets you made? How many cells, how many distinct formulas (i.e. distinct except for relative references), how many worksheets, how long it took to recalculate? Were you pushing any limits of Excel? Were there annoyances? (My email's in my profile if you'd rather discuss it offline.)
Gustomaximus 15y ago
I use it for automating repetitive or recurring tasks where formulas can't cut it or they would make the spreadsheet overly heavy to work with. So a lot of data cutting and organising especially when I pull data from systems that don't integrate well with Excel.

by gruseom 15y ago

kapilkale 15y ago

Most of the time it is because the existing functions are inadequate, so people end up adding new ones in VBA. StringConcat, for example, is far more powerful than the regular concatenate function. http://www.cpearson.com/excel/stringconcatenation.aspx http://www.cpearson.com/excel/stringconcatenation.aspx

gruseom 15y ago
When you say "function" you mean something that just does computation, right? As opposed to code that accesses the Excel object model to create interactive effects, like, say, "click this button to change the background color over there"? How much VBA could be replaced if Excel gave you the ability to make functions just out of spreadsheet cells? That is: build any calculation you like in a spreadsheet, tell Excel "make this calculation be a function called FOO", then use it wherever you want like a normal function: "=FOO(123,A1)" or what have you.
- kapilkale 15y ago
  Yes on the definition of function. I did also use VBA to make certain visual effects possible (the one I remember was some advanced conditional formatting). On VBA being replaced-- I don't know. It depends who the users you're talking about are. Most people I worked with didn't know any VBA because our models weren't that complicated. I in particular was working on a project that was computationally heavy.
  - gruseom 15y ago
    Can you describe the computationally heavy spreadsheets you made? How many cells, how many distinct formulas (i.e. distinct except for relative references), how many worksheets, how long it took to recalculate? Were you pushing any limits of Excel? Were there annoyances? (My email's in my profile if you'd rather discuss it offline.)

Gustomaximus 15y ago

I use it for automating repetitive or recurring tasks where formulas can't cut it or they would make the spreadsheet overly heavy to work with. So a lot of data cutting and organising especially when I pull data from systems that don't integrate well with Excel.