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?

Answers


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.


Need Your Help

How to apply sorting and pagination when accessing objects of current_user?

ruby-on-rails ruby-on-rails-3 sorting pagination kaminari

I'm using Rails 3.2 and Kaminari for pagination. I'm using the same controller action (index) for rendering all articles and all articles of a specific user. My controller code looks like this:

how to transmit modulated voice over a call in android?

android call

Hello friends i want to know in android it is possible to transmit modulated voice like Ultra voice changer app does during a call. I have googled a lot but i only got results for how to change voice