Writing Excel cells with VBA variables

[MamboKing]

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.

[VBA] MsgBox ForDebug
Range("K4") = ForDebug
Range("K4").Value = ForDebug[/VBA]

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]

That you check that ForDebug really does have a value, because it should work.

[Bob Phillips]

Another thin, are you sure you are writing to the worksheet that you think that you are?

[Simon Lloyd]

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]

...with the requested info.

Thanks for responding!

[Simon Lloyd]

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]

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.

[VBA] Function DisplayCell2(Num As Variant)
Dim DiplayVar As Variant

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

End Function[/VBA]

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

[Bob Phillips]

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]

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]

xld, DiplayVar DisplayVar see nothing escapes me!

[MamboKing]

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]

Because UDFs cannot write to cells in a worksheet, they only return a result into the cell that the UDF is in.

[Simon Lloyd]

you can pass the result back to another macro and then display a result!

[MamboKing]

you can pass the result back to another macro and then display a result!

Thanks. I think I got it to work.