How to make WinHttpRequest as double? (runtime error 13)

74 views Asked by At

I kindly ask you for help with this, as I am becoming frustrated AF.

Here is my code:

Private Sub Calc()
Dim EBITDA As Double
Dim EnterValue As Double
Dim EqValue As Double
Dim OperProfit As Double
Dim Depreciation As Double
Dim NetDebt As Double
Dim EqValueEUR As Double
Dim ExRate As Double
Dim ExchangeRate As Double


Dim URL As String: URL = "https://download.finance.yahoo.com/d/quotes.csv?s=EURCZK%3DX&f=b"
Dim yahooCE As New WinHttpRequest
yahooCE.Open "GET", URL, False
yahooCE.Send
ExchangeRate = yahooCE.ResponseText

    If CheckBox1.Value = True Then Range("C23").Value = ExchangeRate
    If CheckBox1.Value = True Then Range("B22").Value = "Convert to EUR"
    If CheckBox1.Value = True Then Range("C22").Value = "Yes"
    If CheckBox1.Value = True Then Range("B23").Value = "Exchange rate"
    If CheckBox1.Value = False Then Range("B22").Value = "Convert to EUR"
    If CheckBox1.Value = False Then Range("C22").Value = "No"
    If CheckBox1.Value = True Then Range("B24").Value = "Equity value in EUR"
    If ComboBox1.Value = "Healthcare" Then Mult = 6
    If ComboBox1.Value = "Industrials" Then Mult = 3
    If ComboBox1.Value = "Consumer Staples" Then Mult = 3.5
    If ComboBox1.Value = "Consumer Discretionary" Then Mult = 1.5
    If ComboBox1.Value = "Utilities" Then Mult = 2
    If ComboBox1.Value = "Energy" Then Mult = 2.5
    If ComboBox1.Value = "Materials" Then Mult = 4.5
    If ComboBox1.Value = "IT" Then Mult = 7


OperProfit = OpProfit
Depreciation = Deprec


EBITDA = OperProfit + Depreciation
EnterValue = EBITDA * Mult
NetDebt = Loan.Value - Cash.Value
EqValue = EnterValue - NetDebt
**EqValueEUR = EqValue / ExchangeRate** 

    If CheckBox1.Value = True Then Range("C24").Value = EqValueEUR


Range("C15").Activate
ActiveCell.Value = EBITDA
Range("C16").Activate
ActiveCell.Value = Mult
Range("C17").Activate
ActiveCell.Value = EnterValue
Range("C18").Activate
ActiveCell.Value = NetDebt
Range("C19").Activate
ActiveCell.Value = EqValue

It pops up Runtime error type 13, because the downloaded value to ExchangeRate is treated as String, however in the bold equation we are mixing Double (EqValue) and String (ExchangeRate) - I understand the cause of the problem. However, I am not able to find a way how to fix it.

Is there a way to force Excel to treat the downloaded value as Double or somehow to bypass it?

Thank you!

1

There are 1 answers

4
h2so4 On

since the answer to http is text and Exchangerate is double you need to convert from text to double

ExchangeRate = CDbl(yahooCE.ResponseText)