5

I am using this statement in VBA

Private Declare PtrSafe Function GetTimeZoneInformationAny Lib "kernel32" Alias _
  "GetTimeZoneInformation" (buffer As Any) As Long

This works fine with:

  • Win 64-bit, Excel 2010 64-bit
  • Win 64-bit, Excel 1010 32-bit

However my client reported a compile error on the word PtrSafe. He is using Excel 2010.

I am not sure why this happens. any ideas?

JoaMika
  • 1,727
  • 6
  • 32
  • 61
  • @chris I don't think it's a duplicate, and I don't think the [linked answer](http://stackoverflow.com/a/3073704/11683) is correct in its "If you're on 64-bit then," because `PtrSafe` works on both 32-bit and 64-bit. – GSerg Nov 08 '14 at 21:19
  • `PtrSafe` works in Excel 2010. Make sure your client actually uses that (as opposed to e.g. Excel 2003). – GSerg Nov 08 '14 at 21:25
  • He did say that but then again he might be wrong. Just to confirm will the above statement work fine in Excel 2007? Are there any other variables that need to be considered? I am only interested in Excel 2007 and later versions – JoaMika Nov 09 '14 at 02:17
  • 1
    Office 2007 does not have a 64-bit version and has VBA 6. Office 2010 was the first to include VBA 7 and 64-bit versions. If you need to support Office 2007 or below, you should be using `#If VBA7` and [declare your API twice](http://msdn.microsoft.com/en-us/library/ff700513(v=office.11).aspx#odc_dnofftalk_ta_WorkingwithVBA32bit64bitOffice2010_WorkingwithDifferentVersionsVBA). – GSerg Nov 09 '14 at 10:41
  • GSerg thanks for clarifying this. I will now declare this twice and let you know after my tests. – JoaMika Nov 09 '14 at 23:48

1 Answers1

9

ok I have re-written this like:

#If VBA7 Then
Private Declare PtrSafe Function GetTimeZoneInformationAny Lib "kernel32" Alias "GetTimeZoneInformation" (buffer As Any) As Long
#Else
Private Declare Function GetTimeZoneInformationAny Lib "kernel32" Alias "GetTimeZoneInformation" (buffer As Any) As Long
#End If

This gave a compile error on the second line in Excel 2010 64-bit and the line remained colored red but when I compile again the error doesn't show up anymore. Is this how Excel behaves?

JoaMika
  • 1,727
  • 6
  • 32
  • 61
  • 7
    Yes, in the IDE the unused line is going to remain red and cause a message box with the error if you try to change it manually (unless it's Office 2010+ 32-bit, where both lines are valid so both will not get red). The message box is highly annoying in general, not only when declaring APIs, so I personally prefer to untick `Tools - Options - Editor - Auto Syntax Check`. Despite the name, this keeps the syntax check, only suppressing the message box. – GSerg Nov 10 '14 at 09:25