#VALUE error when trying to output value to a cell excel VBA

Question

I have written a macro which computes x and y values. I am having trouble trying to write those values to cells on Excel.

I get #VALUE error when I try to display the values on the cell.

I have added my code below. Any suggestion about what is wrong with the code will be really helpful and appreciated?

Thanks in advance!

'Compute Points
Function ComputePoints(x1, y1, x2, y2, distance) As Double

'Calculate slope m
Dim m As Double
m = (y2 - y1) / (x2 - x1)

'Calculate intercept
Dim Intercept As Double
Intercept = y1 - m * x1

'Calculate x for distFinal
Dim message As String
Dim a As Double
Dim b As Double
Dim c As Double
Dim root1 As Double
Dim root2 As Double
Dim det As Double
Dim det1 As Double

Dim wb As Workbook
Dim ws As Worksheet
Dim x1Rng As Range
Dim x2Rng As Range
Dim yRng As Range

a = (m ^ 2 + 1)
b = 2 * (Intercept * m - m * y2 - x2)
c = x2 ^ 2 + (Intercept - y2) ^ 2 - distance ^ 2

det = ((b ^ 2) - (4 * a * c))

det1 = Sqr(det)

message = "There is no solution to your equation"

If det < 0 Then
    MsgBox message, vbOKOnly, "Error"
 Else
    root1 = Round((-b + det1) / (2 * a), 2)
    root2 = Round((-b - det1) / (2 * a), 2)
 End If

'Compute y
Dim y As Double
y = m * root2 + Intercept

' Trying to set cell values to root1, root2, y
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Sheet9")

Set x1Rng = ws.Range("N2")
Set x2Rng = ws.Range("O2")
Set yRng = ws.Range("P2")

x1Rng.Value2 = root1
x2Rng.Value2 = root2
yRng.Value2 = y

ComputePoints = y

End Function

A function called from the worksheet can't change the values of other cells. — Comintern
– Comintern, Commented Dec 9, 2016 at 1:36
@Comintern thank you. Any alternative suggestions to display values calculated on to the worksheet? I tried adding a button and assigning the function to the button. But I encountered Argument not optional error. — Dazzler
– Dazzler, Commented Dec 9, 2016 at 1:40
@Dazzler well obviously - you need to specify the parameter values. Write a parameterless Sub procedure that calls the function with whatever parameters it needs, and call that Sub procedure from that button. It wouldn't hurt to specify an explicit type for each parameter too, and they don't need to be passed ByRef either. Private Function ComputePoints(ByVal x1 As Double, ByVal y1 As Double, ByVal x2 As Double, ByVal y2 As Double, ByVal distance As Double) As Double should do it. — Mathieu Guindon
– Mathieu Guindon, Commented Dec 9, 2016 at 1:44
@Mat'sMug - Thank you for the suggestion. Will give it a go :) — Dazzler
– Dazzler, Commented Dec 9, 2016 at 1:57

Mathieu Guindon · Accepted Answer · 2016-12-09 01:53:49Z

3

I modified your code slightly to get values directly in excel cells. You need to select 3 horizontal cells, press F2 / =, enter your formula and then press Ctrl Shift Enter to make it an array formula.

This will give you the three output values in the cells.

Function ComputePoints(x1, y1, x2, y2, distance)

    Dim results(3) As Variant ' @nightcrawler23

    'Calculate slope m
    Dim m As Double
    m = (y2 - y1) / (x2 - x1)

    'Calculate intercept
    Dim Intercept As Double
    Intercept = y1 - m * x1

    'Calculate x for distFinal
    Dim message As String
    Dim a As Double
    Dim b As Double
    Dim c As Double
    Dim root1 As Double
    Dim root2 As Double
    Dim det As Double
    Dim det1 As Double

    a = (m ^ 2 + 1)
    b = 2 * (Intercept * m - m * y2 - x2)
    c = x2 ^ 2 + (Intercept - y2) ^ 2 - distance ^ 2

    det = ((b ^ 2) - (4 * a * c))

    det1 = Sqr(det)

    message = "There is no solution to your equation"

    If det < 0 Then
        MsgBox message, vbOKOnly, "Error"
     Else
        root1 = Round((-b + det1) / (2 * a), 2)
        root2 = Round((-b - det1) / (2 * a), 2)
     End If

    'Compute y
    Dim y As Double
    y = m * root2 + Intercept

    results(1) = root1    ' @nightcrawler23
    results(2) = root2    ' @nightcrawler23
    results(3) = y        ' @nightcrawler23

    ComputePoints = results    ' @nightcrawler23

End Function

You need to add some code to output error message, when no roots are found

edited Dec 9, 2016 at 1:53

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

answered Dec 9, 2016 at 1:48

nightcrawler23

2,0761 gold badge14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dazzler Over a year ago

Thanks for the answer. But I get a type mismatch error on the last line ComputePoints = results

nightcrawler23 Over a year ago

you original code has Function ComputePoints(x1, y1, x2, y2, distance) As Double. i have changed this in my code. Have you?

Dazzler Over a year ago

Sorry missed that one. Fixed the issue!! Thank you :)

Collectives™ on Stack Overflow

#VALUE error when trying to output value to a cell excel VBA

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related