Need Some Help with macros written on a 32bt running on 64bt please.

[Tuckejam]

Working with one of the great books i got for xmass and im trying to import some of the files it came with so i can learn VBA a littlet better.

The Book is written for Excel 2010 and i am using Office Packge 14?

The Error Message i when trying to run the macro on the this workbook is

Compile Error:

The Code in this project mest be updated for use on 64-bit systems.
Please review and update Declare statements and then mark them with the PtrSafe attribute.


Now the CODE that pops up with this Error message is in module 3

[COLOR=#00FF00]'//////////////////////////////////
'//Written by Masaru Kaji @ MrExcel.com
'//////////////////////////////////[/COLOR]
[COLOR=#0000CD]
Option Explicit[/COLOR]
[COLOR=#FF0000]
Declare Function ShellAbout Lib "shell32.dll" Alias "ShellAboutA" (ByVal hwnd As Long, ByVal szApp As String, ByVal szOtherStuff As String, ByVal hIcon As Long) As Long
Declare Function GetActiveWindow Lib "user32" () As Long[/COLOR]


Public Const Nm = "TransposeData"
---------------------------------------------------------------------------------
Sub TransposeData()
Dim shOrg As Worksheet, shRes As Worksheet
Dim rngStart As Range, rngPaste As Range
Dim lngData As Long

This seems odd to me because the code i wanted to run is in module 1 and dosent seem to have anythig to do with the "Option Explicit thats being used in module 3 However I am still learning so I dont know...
any way the code i wanted to run is below (its just suposed to list all the files in a directory)

Thanks for the help

Sub ExcelFileSearch()
Dim srchExt As Variant, srchDir As Variant, i As Long, j As Long
Dim strName As String, varArr(1 To 1048576, 1 To 3) As Variant
Dim strFileFullName As String
Dim ws As Worksheet
Dim fso As Object
Let srchExt = Application.InputBox("Please Enter File Extension", "Info Request")
If srchExt = False And Not TypeName(srchExt) = "String" Then
    Exit Sub
End If
Let srchDir = BrowseForFolderShell
If srchDir = False And Not TypeName(srchDir) = "String" Then
    Exit Sub
End If
Application.ScreenUpdating = False
Set ws = ThisWorkbook.Worksheets.Add(Sheets(1))
On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Worksheets("FileSearch Results").Delete
Application.DisplayAlerts = True
On Error GoTo 0
ws.Name = "FileSearch Results"
Let strName = Dir$(srchDir & "\*" & srchExt)
Do While strName <> vbNullString
    Let i = i + 1
    Let strFileFullName = srchDir & strName
    Let varArr(i, 1) = strFileFullName
    Let varArr(i, 2) = FileLen(strFileFullName) \ 1024
    Let varArr(i, 3) = FileDateTime(strFileFullName)
    Let strName = Dir$()
Loop
Set fso = CreateObject("Scripting.FileSystemObject")
Call recurseSubFolders(fso.GetFolder(srchDir), varArr(), i, CStr(srchExt))
Set fso = Nothing
ThisWorkbook.Windows(1).DisplayHeadings = False
With ws
    If i > 0 Then
        .Range("A2").Resize(i, UBound(varArr, 2)).Value = varArr
        For j = 1 To i
            .Hyperlinks.Add Anchor:=.Cells(j + 1, 1), Address:=varArr(j, 1)
        Next
    End If
    .Range(.Cells(1, 4), .Cells(1, .Columns.Count)).EntireColumn.Hidden = True
    .Range(.Cells(.Rows.Count, 1).End(xlUp)(2), _
        .Cells(.Rows.Count, 1)).EntireRow.Hidden = True
    With .Range("A1:C1")
        .Value = Array("Full Name", "Kilobytes", "Last Modified")
        .Font.Underline = xlUnderlineStyleSingle
        .EntireColumn.AutoFit
        .HorizontalAlignment = xlCenter
    End With
End With
Application.ScreenUpdating = True
End Sub

 

[RickXL]

Hi, as far as I know, VBA macros should run just as well on 64 bit Windows and Office as 32 bit Windows and Office.

However, your code looks to be more than just straight macro code. There are statements declaring Windows program libraries. Presumably, the mentioned routines are called somewhere in the macro code. I think this is the area that will need to be changed.

So, instead of:

Declare Function ShellAbout Lib "shell32.dll" Alias "ShellAboutA" (ByVal hwnd As Long, ByVal szApp As String, ByVal szOtherStuff As String, ByVal hIcon As Long) As Long
Declare Function GetActiveWindow Lib "user32" () As Long

Those statements are specific to Windows 32. There are some more details here: 64-Bit Visual Basic for Applications Overview
Basically, you now need to declare the functions as "pointer safe" and make sure that all the variables that would have been declared as 32 bits will now be declared as 64 bits.

Try these instead:

Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
Declare  PtrSafe Function ShellAbout Lib "shell32.dll" Alias "ShellAboutA"  (ByVal hwnd As LongPtr, ByVal szApp As String, ByVal szOtherStuff As  String, ByVal hIcon As LongPtr) As Long

I am sorry I cannot test this because although I run Windows 64 I still use Office 32.

regards,

 

[Osvaldo Palmeiro]

Hi. Maybe this is the case.

"Existing Declare statements will not compile in 64-bit VBA until they have been marked as safe for 64-bit by using the PtrSafe attribute".

Take a look at the link below.

Compatibility Between the 32-bit and 64-bit Versions of Office 2010