How does one use a VB module in an Excel sheet's code?

Hi Everyone,

This is my first post here, so I hope I am following the proper format. I am newer to Visual Basic, but have some back ground in C++, Java script, and Python.

Getting to the question at hand, I am wondering just how to call a 'module' via a button in an excel sheet. Creating the button and the linked title was easy enough, but I cannot seem to get the module to be called. I know the module works on its own with no variables fed in, but every time I get the error:

Compile error:

Expected variable or procedure, not module

For the code:

Private Sub Cmd_Bttn__Find_Folder_Click()

' Display Message to user:
' ========================
MsgBox "Please choose the folder which contains the file 'Fan_Data.xlsx'"

' Browse for Folder:
' ===================
Call BrowseForFolder

' Assign folder location to a variable:
' -------------------------------------
' OpenBrowseAt = BrowseForFolder

End Sub

Browse for Folder Function: (in module named "BrowseForFolder") (imported as a .bas file)

Option Explicit

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
' NOTE_1: If the "OpenAt" path is provided, open the browser at that directory
' NOTE_2: If "OpenAt" path is invalid, function will open at the Desktop level

Dim ShellApp As Object

' Create a file browser window at the default folder
' -----------------------------------------------------
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

' Set the folder to that selected. (On error in case cancelled)
' -----------------------------------------------------------------
On Error Resume Next
BrowseForFolder = ShellApp.self.path
On Error GoTo 0

' Destroy the Shell Application
' ----------------------------------
Set ShellApp = Nothing

' Check for invalid or non-entries and send to the Invalid error handler if found
' -------------------------------------------------------------------------------
' Valid selections can begin L: (where L is a letter) or
' \\ (as in \\servername\sharename.
'
' All others are invalid
'
Select Case Mid(BrowseForFolder, 2, 1)

Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid

Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid

Case Else
GoTo Invalid ' (See Routine Below)

End Select

Exit Function

Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False

End Function

Do I need to copy and paste my code from the module "BrowseForFolder" into the sheet itself to make it work? If so, using modules aside from a form of back up seems entirely redundant. I'm sure there has to be a way to do this, but I am just not aware of it yet. Ideally I want to take the found folder location and assign it to the variable as shown, then use it so I can open the indicated excel file to extract data.

Thank you in advance for any assistance!

never give a module and a procedure the same name. you don't call a module you call a routine

Hi,

you don't have to copy the module code into the sheet. Your code looks absolutely fine. Can you post your file so that we can simulate your error.

functions and subs are both routines. the module is simply a container-like a class except you do not create instances of it-so yes it works like a library. and like a class, you call the routines it contains, not the class itself.
for your purposes you just need to change the module name to something different and then the code will work

you're welcome

note that like a class you can refer to a routine as a member of its container module

Please Login or Register  to view this content.

for instance-necessary if you have routines of the same name in different modules

Naming modules is an opportunity for organization. A module may have only a single procedure, but will commonly have several procedures, all of which should be closely related (or they should be in separate modules).

I use the first several characters of module names to give an hint what they are, which also keeps them visually sorted in the VBA project and directories. Examples:

appWhatever (Application code unique to the workbook)

xlInterpolators (xl because the interpolation is relevant to the Excel object model)
xlMergeStyles
xlMakeDynRanges
xlSheetMap

mathSimpson (an integration routine)
mathBaseConversion
mathCubicSpline

strSort (a whole bunch of string sorting routines, generally more relevant to string processing than sorting)
strPadNumbers

sortQuickSort
sortInsertionSort

fileListFiles (file I/O)