+ Reply to Thread
Results 1 to 5 of 5

Solver in VBA: Difficulty with "Valueof"

  1. #1
    Registered User
    Join Date
    12-30-2019
    Location
    Boston, MA
    MS-Off Ver
    Windows 10
    Posts
    2

    Solver in VBA: Difficulty with "Valueof"

    Hi all,

    I am new to VBA and am trying to code something that will use solver. I am asking solver to take a inputs to a binomial tree and solve for the implied volatility. Thus, I am providing the current option premium (to be overwritten with the new), time to maturity, risk-free rate, strike price, current stock price, and the *new* option premium. Solver would need to take these inputs and determine implied vol to arrive that new premium.

    As I mentioned, I am new to VBA and I have no idea why this code is not working. I would really appreciate guidance!!
    Please Login or Register  to view this content.
    In my file,
    b5 = current option premium (to be changed to be equal to the value in b13)
    b6 = underlying price
    b7 = strike
    b9= time to maturity
    b10 = risk free rate
    b13 = my desired option premium
    b12 = the current vol, to be changed solved for such that the option premium in b5 changes to b13

    Right now I get the error:
    Compile error:
    Expected: .

    Please let me know if you need more information to assist. Sample excel file attached.

    Thank you!
    Attached Files Attached Files
    Last edited by kneich; 12-30-2019 at 05:42 PM.

  2. #2
    Registered User
    Join Date
    12-30-2019
    Location
    Cambridge, England
    MS-Off Ver
    Various versions
    Posts
    10

    Re: Solver in VBA: Difficulty with "Valueof"

    I tried running the code on the provided xlsx sheet and it completed without a compile error!

  3. #3
    Registered User
    Join Date
    12-30-2019
    Location
    Boston, MA
    MS-Off Ver
    Windows 10
    Posts
    2

    Re: Solver in VBA: Difficulty with "Valueof"

    Thank you for giving it a try. The plot thickens for me....I do not know why I get the compiling error.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Solver in VBA: Difficulty with "Valueof"

    In this,

    Please Login or Register  to view this content.
    ... CellRef:="B6" is the address of the cell to be constrained, Relation:=2 says make it equal to, and FormulaText:=... specifies the value, which must be just that, a value. E.g., ... FormulaText:=1
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Solver in VBA: Difficulty with "Valueof"

    You could try
    Please Login or Register  to view this content.
    to replace the "ValueOf:="$b$13", that solver is not happy with but it will not find a solution.

    Alf

    Ps A Happy New Year" to all an this thread & all other forum members and guests

+ 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. Solver mistakenly producing "Solver stopped by user" error
    By Scolopendra in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-17-2019, 12:02 PM
  2. [SOLVED] Difficult macro to change regional settings from "Windows Compatibile" to US
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 29
    Last Post: 02-01-2019, 02:55 PM
  3. Creating a "difficult" or "simple" bar chart
    By NLatuny in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 03-24-2016, 01:20 PM
  4. Replies: 0
    Last Post: 09-22-2015, 06:09 PM
  5. [SOLVED] Solver "ValueOf":=1+-0.1 - how to set it?
    By drrazor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-03-2014, 12:08 PM
  6. Replies: 11
    Last Post: 10-30-2013, 02:11 PM
  7. Macro for "adding" solver "constraints"?
    By hansaaa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-21-2013, 02:58 PM

Tags for this Thread

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