+ Reply to Thread
Results 1 to 7 of 7

Cell formulas v VBA

  1. #1
    Forum Contributor
    Join Date
    02-08-2006
    Location
    UK
    MS-Off Ver
    2013
    Posts
    204

    Cell formulas v VBA

    Following an audit, it has been suggested that a database I'm working on use VBA for all calculations, name ranges etc etc, the rationale being that it is easier to follow.

    I'm not sure whether it's worth doing or not - it may hinder accreditation that is desirable but not necessary.

    Is it generally easier to write / audit using VBA as opposed to using the cells themselves?

    Experiences / thoughts appreciated

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Cell formulas v VBA

    I would say if possible most excel functions are easier than using VBA to calculate
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Cell formulas v VBA

    I would say it's easier to audit a spreadsheet that uses standard functionality than VBA. You can at least use Excel's built in formula auditing tools.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Cell formulas v VBA

    Of course to an extent it also depends on core competencies of the auditor.

    Most users are more comfortable with Formulae than VBA for all those reasons previously mentioned, however, there are exceptions to that rule.

    VBA might be suggested if the requisite/persitent formulae are making the file too big and/or prone to error.
    Where that is the case one could argue Excel is being misused (ie shoud be using purpose built repository and SQL to query etc...)

  5. #5
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Cell formulas v VBA

    Quote Originally Posted by DonkeyOte View Post
    Of course to an extent it also depends on core competencies of the auditor.
    Don't you mean incompetencies?

    Dom

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Cell formulas v VBA

    I find the design and structure of the worksheets is the most important feature to make it "easier to follow".
    Secondly is logically named columns in tables of data.

    Excel has amazing features with Table, Autosort, Calculations and Pivot Tables ONLY if the structure of the data allows it.

    I've used VBA many times to get data into Excel-friendly tables to then use normal formulas on it.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Cell formulas v VBA

    Are we talking about financial audits? FDA Audits? GAMP (Good Automated Manufacturing Practices) differentiates between spreadsheets containing Excel Functions and those containing VBA, the VBA ones requiring more stringent controls and validation. If we're talking about a properly validated locked down spreadsheet, the auditor shouldn't need to worry about how the formulas/VBA are doing what they are doing, just that it was validated and secured properly. That being said, I guess the answer, IMHO, is "it depends."

    I think for most straight forward formula's, the formulas are easier to follow than VBA (with error catching and turning alerts on and off, etc).
    However, with some more complex array formulas like this one

    {=IF(COLUMNS($C3:C3)>COUNTIF(Sheet1!$G$3:$R$155,"="&$A3),"",INDEX(Sheet1!$D$3:$D$155,SMALL((Sheet1!$G$3:$R$155=$A3)*ROW($A$1:$A$153),COLUMNS($C$2:C$2)+COUNTIF(Sheet1!$G$3:$R$155,"<>"&$A3))))}

    the equivalent VBA code might be easier for the auditor to follow.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1