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

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.