Cannot run VBA code in Excel 365

[johnbrownbaby]

Hello,

I have this VBA code written in the "ThisWorkbook" section of my excel file to list the filenames from a folder:

On Error Resume Next

Const WINDOW_HANDLE = 0
Const BIF_EDITBOX = &H10
Const BIF_NONEWFOLDER = &H200
Const BIF_RETURNONLYFSDIRS = &H1

Set objShell = CreateObject("Shell.Application")
Set wshShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")

'**Browse For Folder To Be Processed
strPrompt = "Please select the folder to process."
intOptions = BIF_RETURNONLYFSDIRS + BIF_NONEWFOLDER + BIF_EDITBOX
strTargetPath = wshShell.SpecialFolders("MyDocuments")
strFolderPath = Browse4Folder(strPrompt, intOptions, strTargetPath)

Set objNewFile = objFSO.CreateTextFile(strFolderPath & "\filelist.txt", True)
Set objFolder = objFSO.GetFolder(strFolderPath)
' CHANGE STARTS HERE :
Set objColFolders = objFolder.Folders

For Each tfolder In objColFolders
    objNewFile.WriteLine (tfolder.Name)
Next
' CHANGE DONE.
objNewFile.Close

'**Browse4Folder Function
Function Browse4Folder(strPrompt, intOptions, strRoot)
    Dim objFolder, objFolderItem

    On Error Resume Next

    Set objFolder = objShell.BrowseForFolder(0, strPrompt, intOptions, strRoot)
    If (objFolder Is Nothing) Then
        Wscript.Quit
    End If
    Set objFolderItem = objFolder.Self
    Browse4Folder = objFolderItem.Path
    Set objFolderItem = Nothing
    Set objFolder = Nothing
End Function

I am not able to run the code in excel as the run button does not do anything. It used to work before, however now I am not able to execute the code.

I went into the Trust Center and "Enable VBA macros" but I still cannot execute the code. Please tell me what I am doing wrong.

Thanks!

Please tell me what I am doing wrong.

 

[MARK858]

The bottom part is a Function not a Sub and so you would need a sub to call the function, do you have the top part within a Sub because it doesn't look like it?
If you do then please post your full code (if you have it in something like a Workbook_Open code then you wouldn't "Run" it as that is for regular Subs).

 

[johnbrownbaby]

Hello Mark858, Thanks for your response.

That is all I have in the code window. I was modifying the code so probably I inadvertently deleted some code. Can you tell me what I may need to get the code functional again?

Thanks!

 

[MARK858]

You need something like

Sub YourSubname()
'The top part of your code
End Sub

and then both the Sub and the Function go in a regular module and then you run YourSubname

 

[GWteB]

@johnbrownbaby , you've obtained a portion of VB script code, which is supposed to be executed by a CSript or WScript run-time. Therefore the code has to be amended to be suitable running by VBA. Also note that the code in its current form will not work because the Folder object does not have a property called Folders. No doubt the SubFolders property is meant, but VB script bypasses that, gives no screen message and you wonder why nothing happened.