+ Reply to Thread
Results 1 to 7 of 7

Macro exits prematurely from function after calling other function

  1. #1
    Registered User
    Join Date
    01-20-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    4

    Question Macro exits prematurely from function after calling other function

    Hi everyone

    I don't have a lot of experience with VBA, mainly just adjusting some existing macros and making some very simple things for myself. Now I have been writing the code below and the second formula just does not work and I don't understand why.

    Please Login or Register  to view this content.
    When executing the second function, the calculations just stop after making a call to the first function and I get a #value error in the cell. Could someone please explain to me why this happens and how I can fix this?

    I have tried just placing the code of the first function inside the second, but the same problem arises. (I need this first function in some other places as well so it made sense to separate them.)

    All help is greatly appreciated!

    Kind regards

    Louis-Philippe

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Macro exits prematurely from function after calling other function

    Can you attach a workbook with sample data?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    01-20-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Macro exits prematurely from function after calling other function

    Thanks for your reply!

    I have included a spreadsheet with sample data, the "agentProfit" formula has now been entered in cell C24 and should eventually be used in the complete table. The rest of the table is now filled with the "actualCost" formula to illustrate that this formula is functional.

    Kind regards

    Louis-Philippe
    Attached Files Attached Files

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Macro exits prematurely from function after calling other function

    Is it the formula in C24 with the problem?

    If it is the problem is a type mismatch caused by 'Psi 0' being passed to the function for the psi argument.

    In the formula you refer to C$23, but in the other formulas in that row/area you refer to C$22.

    Changing to C$22 doesn't quite fix things - the formula still return's #VALUE! but it seems to get further on in the function's code.

    The next problem is this.
    Please Login or Register  to view this content.
    According to the watch window s_w is a range with one cell, so I'm not sure what you are trying here.

    Also, even if it was a larger range targetCost-costUpperBound returns -20000, which I don't think you can use as an index - if that was the intention.

    I was wondering if the problem here is a simple typo, in the rest of the code you have this sort of thing.
    Please Login or Register  to view this content.
    If I add the * in, and change the formula on the worksheet to this,
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    the formula returns 887,500 instead of an error.

  5. #5
    Registered User
    Join Date
    01-20-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Macro exits prematurely from function after calling other function

    It works now! That was the main issue! (Another small logical error, but this was nothing that could be seen in the formulae.)

    With hindsight I can't believe I missed it. I will have to do some serious work on my VBA debugging skills!

    Thanks a lot for your help, I really appreciate it!

    LP

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Macro exits prematurely from function after calling other function

    No problem.

    One thing you might want to do is declare the types of the arguments being passed to the functions.

  7. #7
    Registered User
    Join Date
    01-20-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Macro exits prematurely from function after calling other function

    That is a good tip! I did not even know it was possible (as said I do need to brush up on my VBA skills).

    Thanks!

    LP

+ 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