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


    ' 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)

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
            Goto ErrH:
        End If

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)

The error handling there you may output a message

  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

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.