Microsoft Office Tutorials and References
In Depth Information
Adding a Description to the Insert Function Dialog
‘Employ the On Error Resume Next statement to check for, and bypass,
‘a run time error in case the workbook is not open.
On Error Resume Next
Set wkb = Workbooks(wb)
‘If there is no error, the workbook is open.
If Err = 0 Then
Err.Clear
OpenTest = True
Else
‘An error was raised, meaning the workbook is not open.
OpenTest = False
End If
End Function
Sub OpenOrClosed()
‘Declare a string variable that will be the workbook name.
Dim strFileName As String
strFileName = “YourWorkbookName.xls”
‘Call the OpenTest UDF to evaluate whether or not the workbook is open.
If OpenTest(strFileName) = True Then
‘For demo purposes, this Message Box informs you if the workbook is open.
MsgBox strFileName & “ is open.”, vbInformation, “FYI...”
Else
‘The OpenTest UDF determines that the workbook is closed.
‘A Message Box asks if you want to open that workbook.
Dim OpenQuestion As Integer
OpenQuestion = _
MsgBox(strFileName & “ is not open, do you want to open it?”, _
vbYesNo, _
“Your choice”)
‘Example code if you answer No, meaning you want to keep the workbook closed.
If OpenQuestion = vbNo Then
MsgBox “No problem, it’ll stay closed.”, , “You clicked No.”
Else
‘Example code if you answer Yes, meaning you want to open the workbook.
‘You need to tell the macro what the full path is for this workbook,
‘so another String type variable is declared for the path.
Dim strFileFullName As String
strFileFullName = “C:\Your\File\Path\” & strFileName
‘Open the workbook.
Workbooks.Open Filename:=strFileFullName
End If
End If
End Sub
Adding a description to the insert function dialog
Chances are, the more VBA you learn, the more popular you’ll be at your workplace as the Excel
go-to person. Soon, if not already, you’re building workbooks for other people to use, and it’s a nice
touch to add a helpful description to your UDFs for the benefit of those other users. The Insert
Search JabSto ::




Custom Search