Results 1 to 5 of 5

Solver in VBA: Difficulty with "Valueof"

Threaded View

  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!!
    Sub Macro5()
    
    SolverReset
    ' SolverOptions precision:=0.00000001
    SolverAdd CellRef:="$b$6", Relation:=2, FormulaText:="$b$6"
    SolverAdd CellRef:="$b$7", Relation:=2, FormulaText:="$b$7"
    SolverAdd CellRef:="$b$9", Relation:=2, FormulaText:="$b$9"
    SolverAdd CellRef:="$b$10", Relation:=2, FormulaText:="$b$10"
    SolverOk SetCell:="$b$5", MaxMinVal:=3, ValueOf:="$b$13", ByChange:="$b$12"
    SolverSolve True
    SolverSolve UserFinish:=False
    MsgBox ("Theo vol solved")
    
    End Sub
    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.

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