32 bit VBA on 64 bit pc

[Tim_Excel_]

Hi forum

I have written my macro on a 32 bit pc. I've added

#If VBA7 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal ms As LongPtr)
#Else
    Private Declare Sub Sleep Lib "kernel32" (ByVal ms As Long)
#End If

in order to try and stop excel from asking for a PtrSafe attribute whenever the macro is executed on a 64 bit pc. However, these lines of code simply disappear whenever the file is opened on a 64 bit pc.

Is there any way I can make my file good to go on a 64 bit pc? Could I for example safe it on a 64 bit pc?

Thanks in advance

 

[Derek Brown]

The following may be of interest:
Excel: Declaring API functions in 64 bit Office

 

[GTO]

#If VBA7 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal ms As LongPtr)
#Else
    Private Declare Sub Sleep Lib "kernel32" (ByVal ms As Long)
#End If

Greetings,

I would first try changing dwMilliseconds back to a Long as I do not believe it to be a pointer (just how many thousandths of a second you want it to sleep.

Declare PtrSafe Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

The above is from 'Win32API_PtrSafe.TXT' which I am fairly certain I downloaded from MSDN.

Hop that helps,

Mark

 

[GTO]

in order to try and stop excel from asking for a PtrSafe attribute whenever the macro is executed on a 64 bit pc. However, these lines of code simply disappear whenever the file is opened on a 64 bit pc.

Is there any way I can make my file good to go on a 64 bit pc? Could I for example safe it on a 64 bit pc?

Thanks in advance

Actually in re-reading your question, I am a bit confused. Windows (presumably what you mean by '64 bit pc') being 64-bit doesn't change what is conditionally compiled; it is whether Excel is installed in 64-bit mode.

 

[Tim_Excel_]

GTO,

As I am not very familiar (read: I don't have a clue) with these type of codes, I am really prone to confusing people.

Looking back at the code I posted in my original comment, it seems very specific. Does it even do what I want it to do? It looks as if it is made for a very specific type of VBA code.
I think you're right about the 64 bit excel part. My colleague recently had his laptop back with some new software, claiming everything was 64 bit now. Since I am still working on a 32 bit system, I can't execute the code I made on my laptop on his laptop right away without encountering error popups.

Private Declare Function apiShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) _
As Long

Private Sub PrintFile(ByVal strPathAndFilename As String)
     
    Call apiShellExecute(Application.hwnd, "print", strPathAndFilename, vbNullString, vbNullString, 0)
     
End Sub

This is the piece of code Excel starts complaining about. I figure it musn't be too hard to make this work on a 64 bit Excel version. However, having close to zero knowledge about this, I just can't seem to figure it out.

 

[GTO]

Does it even do what I want it to do? It looks as if it is made for a very specific type of VBA code.

I am afraid I wouldn't know what you want to do. You showed a declaration for the Sleep API and now a snippet of code that presumably prints a file using the ShellExecuteA API. What are you trying to do and can you show us your code?

I think you're right about the 64 bit excel part. My colleague recently had his laptop back with some new software, claiming everything was 64 bit now. Since I am still working on a 32 bit system, I can't execute the code I made on my laptop on his laptop right away without encountering error popups.

Private Declare Function apiShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) _
As Long

Private Sub PrintFile(ByVal strPathAndFilename As String)
     
    Call apiShellExecute(Application.hwnd, "print", strPathAndFilename, vbNullString, vbNullString, 0)
     
End Sub

This is the piece of code Excel starts complaining about. I figure it musn't be too hard to make this work on a 64 bit Excel version. However, having close to zero knowledge about this, I just can't seem to figure it out.

I would suggest you look and see definitively how Excel is installed in both your PC and your friend's laptop before going any further. At least for me, in Excel 2010, I can go to the file tab, select Help, and under About Microsoft Excel, it shows the version number (14.0.71......) and '(32-bit), which means that although Excel 2010 can be installed in 64-bit, my copy is installed in the default mode of 32-bit.


This should work for 64-bit.

Declare PtrSafe Function ShellExecute Lib "shell32.dll" _
                  Alias "ShellExecuteA" (ByVal hwnd As LongPtr, _
                                         ByVal lpOperation As String, _
                                         ByVal lpFile As String, _
                                         ByVal lpParameters As String, _
                                         ByVal lpDirectory As String, _
                                         ByVal nShowCmd As Long _
                                         ) As LongPtr

 

[Tim_Excel_]

GTO,

Forget about the declaration for the Sleep API, it was something I had found online and I had not realised it didn't apply to my code. I am trying to print files, for which I am using the code I posted.

I checked, and my colleague is running a 64 bit version of Excel whereas I am running on a 32 bit version. We're both using Excel 2013

I had already tried integrating the PtrSafe attribute before saving the file and sending it to my colleague. I thought that this would work, but when I open the file on my colleague's laptop, the PtrSafe attribute simply has disappeared. I'm guessing Excel does this as a preventive method, but of course this limits me in sharing the file with my colleagues of which some have a 64 bit excel..

Oh man.

 

[GTO]

I have no way of testing in 64-bit Excel, but I think this should work.

In a Standard Module:

Option Explicit
  
#If VBA7 Then
Declare PtrSafe Function ShellExecute Lib "shell32.dll" _
                  Alias "ShellExecuteA" (ByVal hWnd As LongPtr, _
                                         ByVal lpOperation As String, _
                                         ByVal lpFile As String, _
                                         ByVal lpParameters As String, _
                                         ByVal lpDirectory As String, _
                                         ByVal nShowCmd As Long _
                                         ) As LongPtr




#Else
Declare Function ShellExecute Lib "shell32.dll" _
          Alias "ShellExecuteA" (ByVal hWnd As Long, _
                                 ByVal lpOperation As String, _
                                 ByVal lpFile As String, _
                                 ByVal lpParameters As String, _
                                 ByVal lpDirectory As String, _
                                 ByVal nShowCmd As Long) As Long
#End If
  
Const SW_HIDE = 0&
Const SW_SHOWNORMAL = 1&
  
Sub example(ByVal FullFileName As String)
#If VBA7 Then
Dim lRet As LongPtr
#Else
Dim lRet As Long
#End If


  lRet = ShellExecute(Application.hWnd, "print", FullFileName, vbNullChar, vbNullChar, SW_HIDE)
  Debug.Print lRet
End Sub
  
Sub callExampe()
  example "I:\TRAVELDRIVE02\TRAVELDRIVE\_Weekend\TempVBAX\vbax 43809 (Unlock VBE)\ReferenceOtherProject\clsBoolean.txt"
End Sub

Use the immediate window to see if a handle or error value is returned.

Hope that helps,

Mark

 

[RoryA]

Excel should never remove the PtrSafe code - and I have never heard of it doing so. Are you sure you sent the correct version of the file?

 

[Tim_Excel_]

Rory,

I was wondering the same thing.. But I literally saved the file, then went to my email and selected the file. Also it's not that this happened once, I had it happen every time I tried adding PtrSafe.







I wonder if Excel is contemplating against me.