VBA Code designed in old Excel won't execute in Excel 2016 | VBA & Macros

VBA Code designed in old Excel won't execute in Excel 2016

Keith Thompson

New Member

Members

Forum Posts: 1

Member Since:
November 5, 2012

Offline

August 28, 2017 - 2:10 pm

Good Afternoon,

I have an Excel time sheet for payroll purposes, it contains some code that allows start and stop times entered as, for example, 0700 to format and read as a time format so 07:00. This code now fails when I attempt to enter times on my 64 bit computer that is running both 64 bit Excel 2016 and 64 bit Excel 2010. The code will still run on a 32 bit operating system in a 32 bit version of Excel 2016 so I am assuming the 64 bit operating system and 64 bit versions of excel are the issue. Can anybody offer any suggestions?

Regards,

Keith

See Answer

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 403

Member Since:
November 8, 2013

Offline

August 28, 2017 - 7:39 pm

Hi Keith,

Only the version of Excel matters, 64 bit office needs a different API function declarations. Here is a sample of how you can change the declarations to match both 32 and 64 bit office:

#If VBA7 Then
Public Declare PtrSafe Function FindWindowA& Lib "user32" (ByVal lpClassName$, ByVal lpWindowName$)
Public Declare PtrSafe Function GetWindowLongA& Lib "user32" (ByVal hwnd&, ByVal nIndex&)
Public Declare PtrSafe Function SetWindowLongA& Lib "user32" (ByVal hwnd&, ByVal nIndex&, ByVal dwNewLong&)
Public Declare PtrSafe Function ShowWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal nCmdShow As Long) As Long
#Else
Public Declare Function FindWindowA& Lib "user32" (ByVal lpClassName$, ByVal lpWindowName$)
Public Declare Function GetWindowLongA& Lib "user32" (ByVal hwnd&, ByVal nIndex&)
Public Declare Function SetWindowLongA& Lib "user32" (ByVal hwnd&, ByVal nIndex&, ByVal dwNewLong&)
Public Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
#End If

You can find here the PtrSafe declarations: http://www.cadsharp.com/docs/W.....trSafe.txt

Other resources: http://www.jkp-ads.com/article.....ations.asp