4 ms·

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

• 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.)