Invalid Formula does not Throw Error - VBA Excel 2010 64 bit

[SandmanIce]

Hi,


I have this project that works fine with Excel 32 bit but I am having problems running it in 64 bit.

I have this part where I handle Invalid formulas (those things that could not be evaluated by excel.) 32 bit used to throw error that I could catch but in 64 bit, I seem to have Issues that I am not sure of. The code kinda got stucked.

Sub Macro1()
    Dim x As Variant
   On Error Goto ErrH:
    ReDim x(1, 1)
    x(0, 0) = "=1+1"
    x(0, 1) = "=1+ "  ' <--this is a sample of what I refer to as an Invalid formula
    x(1, 0) = "=1+2"
    x(1, 1) = "=1+1"
    


    Range("A1:B2").Value = x  ' <--Im stuck in this part. the program does not proceed beyond this point and does not throw error like it used to.

    'I do something here
    

    On Error Goto 0
    Exit Sub
 
ErrH:
    

    ' I have bunch of stuffs that I do here, basically, Error handling.



End Sub

 

[sijpie]

I take it that if x(0,1) contains a valid formula, the range gets written to the sheet OK, and the sub finishes

 

[SandmanIce]

@sijpie: Yes I does. And in 32 bit, it's suppose to throw an error when the formula is invalid or not complete.
I Actually played with it and found a way to make it work for me. I just used "Range.FormulaValue" instead of "Range.Value" to display the array. But I'm still wondering why my code is stuck there when I'm using 64 bit Excel. And can someone tell me the difference of FormulaValue and Value?

Thanks

 

[SandmanIce]

posted the reply 2 times. Sory

 

[sijpie]

not really, msdn is less then helpfull on this. Value is what it says, the value. formulavalue is a structure which contains the value and an extra byte with addtional information. But as to what it is good for....
[MS-XLS]: FormulaValue

 

[jkpieterse]

There must be something else amiss, because My Excel 64 bit happily throws an Application defined or object defined error when I run your code (and put a MsgBox Err.Description after the error label).

 

[SandmanIce]

Really? Well I guess I'm back with my wild goose chase.

Just In case, I am running a Windows 7 64 bit with MS Office 2010 64 Bit. Maybe there is something about my references. Thanks

 

[jkpieterse]

Yes, I'd check the references first.