Results 1 to 7 of 7
  1. #1
    BigNasty is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Posts
    3

    Help with NPV Macro Code in VBA


    Hello, has anyone written code in VBA to run a macro to calculate net present value in Access? With the expression builder, present value is a calculation available but net present value only seems to be available through VBA. Any help would be appreciated!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    NPV is a vba function, but can be called in a query - see this link

    https://support.office.com/en-us/art...ad=US&fromAR=1

    and can be found in the expression builder - built in functions>>Financial>>NPV
    Click image for larger version. 

Name:	Capture.JPG 
Views:	28 
Size:	50.1 KB 
ID:	26379

  3. #3
    BigNasty is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Posts
    3
    Actually the built-in function does not work for some reason. I've tried a million different ways. Does anyone have a good code example to plug this into my forms?

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not knowing what you tried or what exactly "does not work" means, as a start, see if this runs for you
    Code:
    Public Sub testNPV()
        Dim npvArray(1) As Double
        npvArray(0) = 10000
        npvArray(1) = 5000
        MsgBox (NPV(0.04, npvArray()))
    End Sub
    If it does and we want to pursue something you can use, you'll have to explain where the factors are coming from. I presume form controls.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    according to the documentation here

    https://support.office.com/en-us/art...ad=US&fromAR=1

    the array must contain at least one negative value

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm with you on that. Just trying to determine if the function call works for OP.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Looks like this is a dead thread - probably solved elsewhere. Given that for the heckofit I wanted to see what this might/could look like, I went ahead and designed something that would accept inputs and do basic validation as opposed to simply provide an evaluation expression (I'm trying to keep the old "Access" noodle soft). Take a look if you care to. If you're a financial wiz, that would be even better since you probably could check some results against other methods that have known accuracy. This is in 2007 accdb format, is not split or protected, and what little error checking there is was added during this posting so that is not thoroughly tested. One might notice a blip or two when interacting with the subform - if anyone has any constructive criticism on any part of it, feel free to either pm me (since this post seems dead) or add to this post. I guess if it was polished a bit, you could make this an add-in if it was good enough.

    NetPresentValue.zip
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Macro Code Clean up
    By lonesoac0 in forum Macros
    Replies: 0
    Last Post: 08-01-2016, 07:37 AM
  2. VBA code or Access Macro
    By CSGabriel in forum Access
    Replies: 1
    Last Post: 07-27-2012, 09:08 AM
  3. VBA code/Macro help please.
    By Davidyam in forum Access
    Replies: 1
    Last Post: 02-26-2012, 09:59 PM
  4. Question about some code in a macro
    By AudiA4_20T in forum Programming
    Replies: 2
    Last Post: 07-11-2011, 08:16 AM
  5. how to create a macro or code to...
    By Eaglezinha in forum Access
    Replies: 1
    Last Post: 10-20-2008, 04:01 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums