+ Reply to Thread
Results 1 to 6 of 6

[Solved] VBA Function not running

Hybrid View

wassili [Solved] VBA Function not... 08-18-2015, 04:17 PM
TMS Re: VBA Function not running 08-18-2015, 04:44 PM
MrShorty Re: VBA Function not running 08-18-2015, 05:08 PM
wassili Thanks a lot, makes sense.... 08-19-2015, 01:19 AM
wassili Re: VBA Function not running 08-19-2015, 03:50 AM
MrShorty Re: VBA Function not running 08-19-2015, 09:05 AM
  1. #1
    Registered User
    Join Date
    08-18-2015
    Location
    Netherlands
    MS-Off Ver
    2010
    Posts
    10

    [Solved] VBA Function not running

    Hello all, I started using VBA about 2 days ago and have no prior programming experience. I was programming the Newton Raphson method, an iterative procedure, in order to calculate data for a chemical process. Whenever I hit the green Run button, nothing happens other than the Macros menu opening. If I hit F8 to do the code step by step, nothing happens as well. I'm obviously missing something obvious, could someone please help me out? I would greatly appreciate it

    Here is the code:

    Function Iteration(v As Double)
    
    Dim z1, z2, z3, z4, z5, k1, k2, k3, k4, k5, v, v_next, inum  As Double
    
    z1 = 0.43
    z2 = 0.42
    z3 = 0.079
    z4 = 0.001
    z5 = 0.07
    k1 = 1.75034457
    k2 = 0.28420735
    k3 = 0.07519503
    k4 = 0.02978122
    k5 = 5.3320266
    v = 0.5
    inum = 0
    
    Do
    v_next = v - f(v) / fdash(v)
    v = v_next
    inum = inum + 1
    
    Loop Until (inum = 100)
    
    
    End Function
    
    Function f(v As Double)
    
    f = (z1 * (k1 - 1) / (1 + v * (k1 - 1))) + (z2 * (k2 - 1) / (1 + v * (k2 - 1))) + (z3 * (k3 - 1) / (1 + v * (k3 - 1))) + (z4 * (k4 - 1) / (1 + v * (k4 - 1))) + (z5 * (k5 - 1) / (1 + v * (k5 - 1)))
    
    End Function
    
    Function fdash(v As Double)
    
    fdash = ((z1 * (k1 - 1) ^ 2) / (1 + v * (k1 - 1)) ^ 2) + ((z2 * (k2 - 1) ^ 2) / (1 + v * (k2 - 1)) ^ 2) + ((z3 * (k3 - 1) ^ 2) / (1 + v * (k3 - 1)) ^ 2) + ((z4 * (k4 - 1) ^ 2) / (1 + v * (k4 - 1)) ^ 2) + ((z5 * (k5 - 1) ^ 2) / (1 + v * (k5 - 1)) ^ 2)
    
    End Function
    Last edited by wassili; 08-19-2015 at 09:07 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,521

    Re: VBA Function not running

    You have created Functions and I suspect you need Subroutines. That's why a) they don't appear in the macro list and b) you can't step through them in the VBA Editor.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,460

    Re: VBA Function not running

    Whenever I hit the green Run button, nothing happens other than the Macros menu opening. If I hit F8 to do the code step by step, nothing happens as well. I'm obviously missing something obvious, could someone please help me out?
    These methods of calling a procedure do not allow you to pass an argument to the procedure. You have written all of these functions to require an argument. As TMS suggests, you need a master Sub() procedure to call these functions with an appropriate argument, or you need to call these functions from a spreadsheet cell to pass an appropriate argument. Examples
    Sub test()
    myv=24
    myoutput=iteration(myv)
    end sub
    or in a spreadsheet cell =iteration(24). In order to enter debug mode, you will need to set a breakpoint somewhere in the function, so that the function will pause and let you inspect what is happening.

    I note that you have v dim'ed twice in iteration -- once in the DIM statement and once in the function's argument list. I cannot remember for certain, but that may cause an error. You may also have issues with the scope of the various z and k variables, as they appear to only be dimensioned for the iteration procedure, but may not carry their values over to the other functions called by iteration.

    I tend to prefer to code something like this as a UDF, so that I can call the function from the spreadsheet. With that in mind, I suggest a few pages for research and review:
    http://www.cpearson.com/Excel/Writin...ionsInVBA.aspx
    http://www.cpearson.com/Excel/DebuggingVBA.aspx
    http://www.cpearson.com/Excel/Writin...ionsInVBA.aspx
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    08-18-2015
    Location
    Netherlands
    MS-Off Ver
    2010
    Posts
    10
    Quote Originally Posted by MrShorty View Post
    These methods of calling a procedure do not allow you to pass an argument to the procedure. You have written all of these functions to require an argument. As TMS suggests, you need a master Sub() procedure to call these functions with an appropriate argument, or you need to call these functions from a spreadsheet cell to pass an appropriate argument. Examples
    Sub test()
    myv=24
    myoutput=iteration(myv)
    end sub
    or in a spreadsheet cell =iteration(24). In order to enter debug mode, you will need to set a breakpoint somewhere in the function, so that the function will pause and let you inspect what is happening.

    I note that you have v dim'ed twice in iteration -- once in the DIM statement and once in the function's argument list. I cannot remember for certain, but that may cause an error. You may also have issues with the scope of the various z and k variables, as they appear to only be dimensioned for the iteration procedure, but may not carry their values over to the other functions called by iteration.

    I tend to prefer to code something like this as a UDF, so that I can call the function from the spreadsheet. With that in mind, I suggest a few pages for research and review:
    http://www.cpearson.com/Excel/Writin...ionsInVBA.aspx
    http://www.cpearson.com/Excel/DebuggingVBA.aspx
    http://www.cpearson.com/Excel/Writin...ionsInVBA.aspx

    Thanks a lot, makes sense. I'll start working on it now and I'll post if I run into anymore similar problems.

  5. #5
    Registered User
    Join Date
    08-18-2015
    Location
    Netherlands
    MS-Off Ver
    2010
    Posts
    10

    Re: VBA Function not running

    EDIT: Got everything working perfectly, thanks again to you guys for helping out

    Hey, I rewrote the code and everything seems to be working fine. However, I cannot get to the final answer which I am given, 0.201376. Rather, I get -2.308388. It's extremely frustrating as I am practically 100% sure my code is correct.

    Sub Iteration()
    
    Dim v As Double
    v = 0.01
    
    answer = newton(v)
    
    
    End Sub
    Function newton(v As Double)
    
    Dim prec As Double
    prec = 0.000000000000001
    
    Do
    v_next = v - func(v) / funcdash(v)
    v = v_next
    inum = inum + 1
    
    Loop Until (Abs(v_next) < prec Or inum = 500)
    
    Cells(2, 2) = v_next
    
    
    End Function
    Function func(v As Double) As Double
    
    Dim z1, z2, z3, z4, z5, k1, k2, k3, k4, k5 As Double
    
    z1 = 0.43
    z2 = 0.42
    z3 = 0.079
    z4 = 0.001
    z5 = 0.07
    k1 = 1.75034457
    k2 = 0.28420735
    k3 = 0.07519503
    k4 = 0.02978122
    k5 = 5.3320266
    
    
    func = (z1 * (k1 - 1) / (1 + v * (k1 - 1))) + (z2 * (k2 - 1) / (1 + v * (k2 - 1))) + (z3 * (k3 - 1) / (1 + v * (k3 - 1))) + (z4 * (k4 - 1) / (1 + v * (k4 - 1))) + (z5 * (k5 - 1) / (1 + v * (k5 - 1)))
    
    End Function
    Function funcdash(v As Double) As Double
    
    Dim z1, z2, z3, z4, z5, k1, k2, k3, k4, k5 As Double
    
    z1 = 0.43
    z2 = 0.42
    z3 = 0.079
    z4 = 0.001
    z5 = 0.07
    k1 = 1.75034457
    k2 = 0.28420735
    k3 = 0.07519503
    k4 = 0.02978122
    k5 = 5.3320266
    
    funcdash = ((z1 * (k81 - 1) ^ 2) / (1 + v * (k1 - 1)) ^ 2) + ((z2 * (k2 - 1) ^ 2) / (1 + v * (k2 - 1)) ^ 2) + ((z3 * (k3 - 1) ^ 2) / (1 + v * (k3 - 1)) ^ 2) + ((z4 * (k4 - 1) ^ 2) / (1 + v * (k4 - 1)) ^ 2) + ((z5 * (k5 - 1) ^ 2) / (1 + v * (k5 - 1)) ^ 2)
    
    End Function
    Last edited by wassili; 08-19-2015 at 04:33 AM.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,460

    Re: VBA Function not running

    Glad you got it figured out. If that resolves this problem, then please mark your thread as Solved (Edit first post).

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to stop function running
    By Theodjinn in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 07-04-2017, 11:14 PM
  2. Excel 2007 : Running a macro using an if function
    By Sky188 in forum Excel General
    Replies: 3
    Last Post: 06-01-2012, 03:14 PM
  3. Stop Function From Running
    By realniceguy5000 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-13-2011, 05:04 PM
  4. Error running Match function in VBA
    By CrystalNewb in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-07-2009, 03:20 PM
  5. Running a function from another Workbook
    By ravinder_tigh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-03-2009, 04:44 AM
  6. Recursive/Running Function
    By goldm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-07-2009, 08:34 AM
  7. Running Solver from Function
    By krepa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-24-2006, 02:07 PM
  8. Syntax for running a macro from an =IF function?
    By BobH in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-01-2005, 09:06 PM

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