AXISHK
asked on
Cannot run macro on Excel 2010 64 bit
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
Thanks
Last Comment
Rory Archibald
Do you mean it simply does nothing, or do you get an error?
Did you have the Macros enabled on both computers?
Capturar.JPG
Capturar.JPG
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
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
ASKER
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.
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.
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?
It sounds as though a reference has been set that is not available on that machine. Which line causes the error?
ASKER
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".
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".
ASKER
Further debug the statement, it stops at
Query1count = rst.RecordCount
However, Query1count has been declared with Integer. Any idea ?
Thanks
Query1count = rst.RecordCount
However, Query1count has been declared with Integer. Any idea ?
Thanks
ASKER
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
ReDim stDBArray(rst.RecordCount -1).
Any idea ?
Thanks
Try declaring as long instead
ASKER
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
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
You should not get an error on that line just because there are no records. What is the exact error message?
ASKER
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
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
Do you have a check for record count [b]greater than[/b] 0 first? some cursors will return -1 rather than an actual count.
ASKER
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 ?
Why Excel 32bit and 64bit behave in different way ?
ASKER
BTW, what is the data type returned by recordset.RecordCount ?
Thanks
Thanks
It should be a long, but I think this is your issue: http://support.microsoft.com/kb/983246
ASKER
Yes, but any solution to fix the problem.
Thanks again.
Thanks again.
Yes - see the Resolution section in that article, or the Hotfix link.
As an aside, even MS don't recommend using 64 bit office unless you have a real need for working with huge files.
ASKER
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.
"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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Tks
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
