VBA - How to get the result of a formula ? (.Value always returns 0)

Question

I want to get the result of a formula contained in a cell, but I always get 0 returned, instead of the real results.

I have the following:

Set c = .Cells (5,5)

MsgBox (c.Formula) ' this return the following: = ASIN (W22-V22/$D$7)*180/PI() 
MsgBox (c.Value) ' this returns 0
MsgBox (c.Value2) ' this returns 0 as well

And even if I try with Evaluate:

evaluation = Application.Evaluate (c.Formula) 
MsgBox (c.Value) ' it still returns 0

I tested this code and it worked fine for me. – Stef Joynson Mar 03 '15 at 12:49 — Stef Joynson, Mar 03 '15 at 12:49

score 1 · Answer 1 · answered Mar 03 '15 at 12:58

1

Zero is the correct answer if both V22 and W22 are zero.

answered Mar 03 '15 at 12:58

Gary's Student

95,722
10
59
99

No they are not, I have other results shown in my Excel in the whole column: 1.1, 0.6, 0.9, .... but in VBA (.Value) returns 0 for all of them. – Leon Mar 03 '15 at 13:37
Is Calculation Mode Manual or Automatic?? – Gary's Student Mar 03 '15 at 13:46
Make sure there are no extra spaces that would make the formula invalid. – Gary's Student Mar 03 '15 at 13:48

score 0 · Answer 2 · answered Mar 03 '15 at 13:52

0

Maybe try this:

Sub formVal()

Dim c As Range

Set c = ThisWorkbook.Sheets(1).Cells(5, 5)

MsgBox (c.Formula) 
MsgBox (c.Value)
MsgBox (c.Value2)



End Sub

answered Mar 03 '15 at 13:52

Tom K.

1,020
1
12
28

I see no changes, but I tried it and it did return the same results as before: 0 all the time – Leon Mar 03 '15 at 13:56
Maybe you've got your row or column ID wrong? It's Cells(rowIndex, columnID). You can also write: c =...Cells("E5") – Tom K. Mar 03 '15 at 14:04
And just btw, isn't your formula supposed to be =ASIN((W22-V22)/$D$7)*180/PI() ? – Tom K. Mar 03 '15 at 14:10
See here: http://stackoverflow.com/questions/17745656/does-value-value-act-similar-to-evaluate-function-in-vba?rq=1 – Tom K. Mar 03 '15 at 14:26

VBA - How to get the result of a formula ? (.Value always returns 0)

2 Answers2

Linked