Cannot run macro on Excel 2010 64 bit

AXISHK
AXISHK used Ask the Experts™
on
I couldn't run my customized marco under Excel 2010 64 bit. However, the macro works perfect under Excel 2010 32 bit. And I have already lower the macro settings. Any idea ?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011

Commented:
Do you mean it simply does nothing, or do you get an error?

Commented:
Did you have the Macros enabled on both computers?
Capturar.JPG
Top Expert 2010

Commented:
AXISHK,

Are you using any API calls in your code?  If so, make sure that you are using the 64 bit APIs, and not the 32 bit APIs.

Patrick
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Author

Commented:
It doesn't call any API, and macro has been enabled.

Can I install Office 32 bit on Window 7 64bit, or it is still recommended to install 32 bit ?

When I run the macro, it says the compile error.
Most Valuable Expert 2011
Top Expert 2011

Commented:
You can certainly install 32 bit office on 64 bit windows.
It sounds as though a reference has been set that is not available on that machine. Which line causes the error?

Author

Commented:
Here are the references in the Excel marco
1. Visual Basic For Applications
2. Microsoft Excel 14.0 Object Library
3. OLE Automatoin
4. Microsoft Office 14.0 Object Library
5. Microsoft ActiveX Date Objects 2.8 Library

When the macro is run on Excel 2010 64bit, it pops an error "Compile error: Type mismatch".

Author

Commented:
Further debug the statement, it stops at
Query1count = rst.RecordCount

However, Query1count has been declared with Integer. Any idea ?

Thanks

Author

Commented:
One more finding, when I debug the return of rst.RecordCount, It returns a value 41^. In another macro problem, the follow assignment also has the problem,

ReDim stDBArray(rst.RecordCount -1).

Any idea ?

Thanks
Most Valuable Expert 2011
Top Expert 2011

Commented:
Try declaring as long instead

Author

Commented:
Get pass the rst.RecordCount statement but get another error in case rst return a null object "Application object error"

So, how to check the null recordset under Excel 2010 64bit ?

Greats.


    stSQL = "select FUNLCURR from MC40000 "
 
-->  Set rst = cnt.Execute(stSQL)
       If rst.RecordCount = 0 Then
          stFunCur = ""
      Else
         stFunCur = RTrim(rst("FUNLCURR"))
     End If
Most Valuable Expert 2011
Top Expert 2011

Commented:
You should not get an error on that line just because there are no records. What is the exact error message?

Author

Commented:
Sorry, I have stimulated the problem again on a clean macro.

1. Change the recountcount as long.
2. the macro stop "ReDim stDBArray(rst.RecordCount - 1)" - Type mismatch

So, how to convert the long value back to integer to redefine the array element ?

Thanks
Most Valuable Expert 2011
Top Expert 2011

Commented:
Do you have a check for record count [b]greater than[/b] 0 first? some cursors will return -1 rather than an actual count.

Author

Commented:
Actually, it seems that there is a internal type check on the Excel macro. When I run the macro, it stop at the line. Even though I put a breakpoint at the first line, it doesn't break at that line.

Why Excel 32bit and 64bit behave in different way ?

Author

Commented:
BTW, what is the data type returned by recordset.RecordCount ?

Thanks
Most Valuable Expert 2011
Top Expert 2011

Commented:
It should be a long, but I think this is your issue: http://support.microsoft.com/kb/983246

Author

Commented:
Yes, but any solution to fix the problem.

Thanks again.
Most Valuable Expert 2011
Top Expert 2011

Commented:
Yes - see the Resolution section in that article, or the Hotfix link.
Most Valuable Expert 2011
Top Expert 2011

Commented:
As an aside, even MS don't recommend using 64 bit office unless you have a real need for working with huge files.

Author

Commented:
Actually, we need as some financial model is quite really and 64bit is benefit.

"If you want to make sure that the VBA code can run on computers that have and that do not have the hotfix, use ADO with late binding."

So, any idea how to use ADO with late binding. There are a lot of different .dll mentioned in the article but I don't really understand which fix should I need. Seem like ADO with late binding is a way to go.

Any comment ? Tks.
Most Valuable Expert 2011
Top Expert 2011
Commented:
To use late binding, you declare the objects as Object rather than as ADODB.Recordset for example.

Author

Commented:
Tks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial