PDA

View Full Version : Writing Excel cells with VBA variables



MamboKing
06-28-2008, 10:56 AM
I need to store (hence display also) the result of a VBA calculation into an Excel cell.

I cannot go through a Function and I MUST go through a variable.
Let's call it ForDebug, which can be any type but preferably it's As Double.

I tried this syntax, with ForDebug As Double first and As Variant later.

MsgBox ForDebug
Range("K4") = ForDebug
Range("K4").Value = ForDebug

Positively, the MsgBox displays valid data (-0.59702...) but nothing happens.
Cell K4 of the Excel sheet remains empty regardless of its format (Number or General).
Also I've only one WorkSheet.

What do you guys suggest?

Bob Phillips
06-28-2008, 11:04 AM
That you check that ForDebug really does have a value, because it should work.

Bob Phillips
06-28-2008, 11:04 AM
Another thin, are you sure you are writing to the worksheet that you think that you are?

Simon Lloyd
06-28-2008, 11:07 AM
What is the calculation? hwo are you achieveing the result? what is a standard result?, the Range("K4").Value = ForDebug would be correct, how is K4 formatted?

MamboKing
06-28-2008, 11:18 AM
...with the requested info.

Thanks for responding!

Simon Lloyd
06-28-2008, 11:38 AM
MamboKing you didn't answer all my questions
What is the calculation? how are you achieveing the result?, can you post the offending workbook?

MamboKing
06-28-2008, 11:49 AM
Simon,

The calculation is really complicated. It's the output of a Finite-Difference numeric method (that I'm coding) for the calculation of a non-liner partial differential equations.

Let's make it simpler. I wrote this simple test and behaves exactly the same. While DiplayVar contains data, the cell K4 remains empty.

Function DisplayCell2(Num As Variant)
Dim DiplayVar As Variant

DisplayVar = Num
MsgBox DisplayVar
Range("K4").Value = DisplayVar

End Function

I also posted the Excel & VBA here:
http://sites.google.com/a/piac3ntini.net/sharingdocs/Home/VBA2EXCEL.xls

Bob Phillips
06-28-2008, 11:59 AM
Is this a UDF that you are calling from a worksheet? You said you couldn't use a function.

If it is not a UDF, check your spelling of your variables (and the learn t9o use Option Explicit).

Simon Lloyd
06-28-2008, 12:01 PM
A function doesnt display a value like that (as far as i am aware) normally a function is called upon in another macro or used in the worksheet like this =displaycell2()

Simon Lloyd
06-28-2008, 12:02 PM
xld, DiplayVar DisplayVar see nothing escapes me! ;)

MamboKing
06-28-2008, 12:19 PM
Now I'm totally, totally lost!

I corrected the mispelling and changed the Function output (re-posted at the same link).

If I remove by commenting the line Range("K4").Value = DisplayVar

the function outputs its value in the cell G8, where it's instantiated.

But the line Range("K4").Value = DisplayVar, when active, screws up everything!

It make the function not returning anything!

That's my trouble...

Bob Phillips
06-28-2008, 12:37 PM
Because UDFs cannot write to cells in a worksheet, they only return a result into the cell that the UDF is in.

Simon Lloyd
06-28-2008, 12:42 PM
you can pass the result back to another macro and then display a result!

MamboKing
06-28-2008, 11:10 PM
you can pass the result back to another macro and then display a result!

Thanks. I think I got it to work.