VBA - Visual Basic for Applications
With this collection of articles I want to build a wiki for myself as well as others.
The goal is to build not only a little framework for Excel VBA, but also a collection of general tips and hints on issues I experienced working with Excel and Visual Basic for Excel.
Once completed, I am guessing it never will be, this will serve me well. Like my wife always says, I am "Mr. Forgetful" and a "Lazy Bum".
Yes dear, I love you too.
And it's true, good programmers are lazy!
- Hits: 3751
The following function checks, if a worksheet is existing under a given name in a provided workbook object.
' @Author - Alexander Bolte
' @Description - Checking, if a worksheet is existing in given workbook under given name.
' @Param wrk - an object of type Workbook.
' @Param name - a String holding the name of the worksheet existance should be checked for.
' @Returns - true, if worksheet exists under given name, else false.
Public Function worksheetExists(ByRef wrk As Workbook, ByVal name As String) As Boolean
Dim ret As Boolean
Dim tmp As Worksheet
On Error GoTo errHandle:
Set tmp = wrk.Worksheets(name)
ret = Not (tmp Is Nothing)
If Err.Number <> 0 Then
ret = False
Set tmp = Nothing
worksheetExists = ret
- Hits: 3593
Whenever you have to interact with users there is information you would like to store outsie the lifetime of an application in order for it to be available at next startup.
The below class can assist in writing and reading property files into hidden temp directories like AppData in Windows systems. This way you can for example store user settings without having to deal with protecting and hiding Excel sheets. I think it is bad style to store information that only rarely is touched by a user in a workbook, which is used to process data. Unless Excel is not used as an interface to configure a lot of different parameters in an application, I use property files.
However the main advantage is that you can use a Scripting.Dictionary internally in the below class in order to ease getting and setting properties by name. This includes the following advantages compared to storing information in Excel files.
- No duplicates possible.
- No handling of indices in Excel sheets necessary.
- Property files are more reliable because Excel sheets are more likely to be modified by users.
- Hits: 3641
Excel VBA Article Count: 30
This category will hold articles regarding developement in Excel VBA. It will serve as a wiki and an Excel VBA Framework for myself.
Some development tasks reoccur for every customer. Since I am a lazy bum it will be nice to have a central source where I can reuse source code from.
MS Office and VBA Article Count: 11
This category holds articles regarding general things in MS Office VBA independent from the MS Office application.
Power Point VBA Article Count: 1
Page 9 of 17