Invalid Formula Does not throw Error - VBA Excel 2010 64 bit
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 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
What do I need to do for Excel to throw an error on the line that I have indicated in my code?
To validate the formula, you can use the predifined function in VBA
Right(text,number of character) ISNUMBER(text)
dim i as integer :i=0 dim j as integer :j=0
for i = 0 to 1 for j = 0 to 1 if ISNUMBER(Right(x(i,j),1)) OR Right(x(i,j),1)=")" Then ' do anything you want if the formula is correct else Goto ErrH: End If next next
I don't think you can output the value of X by using this method as X is a 2-D array.
Range("A1:B2").Value = x
The proper way to output it is use 2 for loop
dim i as integer :i=0 dim j as integer :j=0 for i = 0 to 1 for j = 0 to 1 Cells(i+1).value = x(i,j) next next
The error handling there you may output a message
ErrH: MsgBox "Invalid Formula" exit sub
I have found a way for this to work for me. I just used Range.FormulaArray instead of Range.Value although I'm not yet sure what is the difference between the two, It seems to work just fine.
But I still don't know why excel 64 bit doesn't throw the error that It used to.maybe I'll look into it next time that I have time.