5

I have a formula in a range of cells in a worksheet which evaluate to numerical values. How do I get the numerical values in VBA from a range passed into a function?

Let's say the first 10 rows of column A in a worksheet contain rand() and I am passing that as an argument to my function...

public Function X(data as Range) as double

    for c in data.Cells
        c.Value    'This is always Empty
        c.Value2   'This is always Empty
        c.Formula  'This contains RAND()
    next

end Function

I call the function from a cell...

=X(a1:a10)

How do I get at the cell value, e.g. 0.62933645?

Excel 2003, VB6

Simon
  • 78,655
  • 25
  • 88
  • 118
  • I did some quick VBA code and I get the value of the cells Rand(); no problems. Maybe post more code to see what else is going on? – Richard Morgan May 23 '09 at 12:38
  • Richard, there is no more code, that's it... All I want to do is get the cell values and they are all "Empty" – Simon May 23 '09 at 12:40
  • Please also explain under which conditions you use the function. In particular, are the cells on the same sheet and is something else active at the time you use it, like a dialog box or some Excel feature (certain Range methods just don't work in Excel in certain situations). – GSerg May 23 '09 at 13:17
  • Opening line of For loop incorrect - should be: For Each c in data.Cells Presume this is a typo as the program won't compile with the Each missing – barrowc May 24 '09 at 02:37
  • Shouldn't that be "for each"? – Oorang May 27 '09 at 08:46

3 Answers3

6

The following code works for me when running from VBA (Excel 2003):

Public Function X(data As Range) As Double

For Each c In data.Cells
    a = c.Value     'This works
    b = c.Value2    'This works too (same value)
    f = c.Formula   'This contains =RAND()
Next

End Function

a and b are the same and equal what I'm passing in (which is a range of cells with Rand() in them). I'm not sure what else is going on here.

Aha! You need to set X, no? I'm not sure what exactly you expect this function to do, but you need to set X (the name of the function) to the value you want returned. Add this line:

X = a
Richard Morgan
  • 7,601
  • 9
  • 49
  • 86
  • I can get it to work if the cell contains: a value a reference to a value a formula made from two values but it never works if I refer directly to a cell containing the rand() function. – Simon May 23 '09 at 12:55
  • Maybe put how you're calling the X function? Again, every way I can think of is working for me. – Richard Morgan May 23 '09 at 13:00
  • X function as written in the question doesn't do anything with c.Value, c.Value2 or c.Formula. Presume that we need to do perform some action within the loop on c.Value so e.g. a = a + c.Value within the loop then X = a as stated immediately above – barrowc May 24 '09 at 02:40
2

I can't replicate a problem using the layout you posted. I noticed a few syntax errors in your posted code (ie: "for" should be "for each"). But when I put =RAND() in A1:A10 and =X(A1:A10) I got a return just fine with this:

Public Function X(data As Range) As Double
    Dim c As Excel.Range
    Dim sum As Double
    For Each c In data.Cells
        sum = sum + c.Value
    Next
    X = sum
End Function

However, just to a expand a little more on a few of the other questions you brushed up against. You can evaluate a formula for a result like so:

Public Function X(data As Range) As Double
    Dim c As Excel.Range
    Dim sum As Double
    For Each c In data.Cells
        sum = sum + Excel.Evaluate(c.Formula)
    Next
    X = sum
End Function

But generally speaking you won't want to, as this is basically calculating the same value twice.

Oorang
  • 6,630
  • 1
  • 35
  • 52
  • That Evaluating the formula again (although in most cases redundant) came in pretty handy for me. I wanted to cache values from another workbook, but it's cells would return 'Empty' because they where to be calculated later in whatever queue excel has in mind. – aliqandil Aug 13 '19 at 06:25
0

Make sure you do a calculate before requesting the value.

To Speed up macros something like the following is often preformed..

'Set Reasonable default
Application.CutCopyMode = False
Application.ScreenUpdating = False
Application.Interactive = False
Application.Calculation = xlCalculationManual

in this state you must force calculation before the value will be available.

Public Function X(data As Range) As Double
    'You may need the following as well
    'Application.Calculate
    Dim c As Range
    For Each c In data.Cells
        c.Calculate
        c.Value    'This is now has a value
        c.Value2   'This is now has a value
        c.Formula  'This contains RAND()
    Next
End Function
Frobbit
  • 1,652
  • 17
  • 30
  • Application.Calculate is not neccessary, since Excel does calculate new value of the formula even when calculation mode is set to xlManual. if you have to call Application.Calculate each time then you are defeating the whole purpose of setting it to manual and thus speeding things up, right? – Adarsha Apr 25 '11 at 05:33
  • Not really as you are only calculating that one cell. Not the entire sheet. Beside the biggest CPU saver is turning off the screen updating. – Frobbit Sep 14 '11 at 19:23