Tag: VBA

Minimum and maximum values of numeric data types

Posted on by Tushar Mehta

There has been many an occasion when I have wanted programmatic access to the maximum or minimum or smallest value of a data type. Some programming languages have built-in support through names like MaxInt. VBA, unfortunately, is not one of them.

I decided to “translate” the documentation defining the different data types into code. The functions Max{datatype}, Min{datatype}, and Smallest{datatype} return the appropriate value. The Max and Min functions should be self-evident. The Smallest function returns the smallest non-zero value supported by the data type. Since this is simply 1 for all data types associated with integers (Byte, Integer, Long, LongPtr, LongLong, and Currency), Smallest is defined only for data types associated with real numbers (Single, Double, and Decimal).

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1003%20MinMaxVals.shtml

Tushar Mehta

Access data in a closed workbook containing a protected worksheet

Posted on by Tushar Mehta

In a LinkedIn group, someone wanted to access data in a shared server-based workbook that contained a protected sheet with locked cells that were not selectable. In addition to sharing an automated way of doing this, this post contains two other embedded tips.

The solution, as many know, is to enter a formula in the destination worksheet that references the source cell, e.g., =’C:\Temp\[Book2.xlsx]Sheet1′!$E$5

Given the high likelihood of making an error in entering long formulas, I decided to see if I could automate the process.

Tip 1: In doing so, I discovered that under certain circumstances Excel will make a very interesting correction. If the source workbook has a single worksheet, then one can use any sheet name in the formula and Excel will change it to the correct one! So, if book2.xlsx contains a single sheet named Sheet1, and one were to enter the incorrect formula =’C:\Temp\[Book2.xlsx]abc’!$E$5, Excel will correct it to =’C:\Temp\[Book2.xlsx]Sheet1′!$E$5.

That aside, since the cells in the source worksheet are not selectable, one cannot construct the formula using click-and-point. So, I decided that as long as one wants the values from the source cells to be in the same cell in the destination worksheet, why not select the cells in the destination worksheet? The code below does just that. Also, there is no longer a need to open the shared server workbook at all!

One final note. I rarely use so many different interactions with the consumer, preferring a userform. But, the below is easier to share. ;-)

Tip 2: The Inputbox method gets a single piece of information from the user, e.g., the sheet name in the code below. If the user were to cancel the resulting dialog box, the method returns False. The usual way to check for this is to compare the returned value with “False”. But, this precludes a legitimate response of “False”! So, I tend to check if the returned type is a boolean. The same applies to the GetOpenFilename method.

Enter the code below is a standard VBE module. Then, open the destination worksheet (or create a new one), and then run the linkToExternal subroutine. It will ask for the source workbook, the source worksheet, and then the destination cells. The code will add in each destination cell a formula that links to the same cell in the source worksheet.

Option Explicit

Sub linkToExternal()
If ActiveWorkbook Is Nothing Then
MsgBox "Please open the destination workbook before running this macro"
Exit Sub
End If
Dim FName
FName = Application.GetOpenFilename( _
Title:="Please select the source workbook")
If TypeName(FName) = "Boolean" Then Exit Sub
FName = Left(FName, InStrRev(FName, Application.PathSeparator)) _
& "[" & Mid(FName, InStrRev(FName, Application.PathSeparator) + 1) _
& "]"
Dim SheetName
SheetName = Application.InputBox("Please enter the name of the source sheet", Type:=2)
If TypeName(SheetName) = "Boolean" Then Exit Sub
FName = "='" & FName & SheetName & "'!"
Dim Rng As Range
On Error Resume Next
Set Rng = Application.InputBox( _
"Please select the destination cells into which you want the corresponding source cell values", _
Type:=8)
On Error GoTo 0
If Rng Is Nothing Then Exit Sub
Dim aCell As Range
For Each aCell In Rng
aCell.Formula = FName & aCell.Address(True, True)
Next aCell
End Sub

Error Handling Template

Posted on by Dick Kusleika

Since Professional Excel Development was published, I’ve been using the error handling method described in chapter 12. It’s very comprehensive and easy to implement. It’s no guarantee that my code is right, of course, but it does guarantee that the end user won’t be plopped into the VBE when an error occurs.

I also MZ-Tools while I’m coding. One of my favorite features is being able to add module headers, procedure headers, and error handling code easily. Below is what I have on the Error Handling tab under MZ-Tools > Options:

If you haven’t read the book, all non-trivial, non-entry point procedures are made into Boolean functions that return TRUE if no error occurs. When I add the above code into a an entry point procedure, I have to delete any line with the variable bReturn in it because that’s only applicable for functions.

The part that I’m not totally clear on is for functions that return something other than Boolean. For instance, I have a function that reads a Jet table, fills an array, and returns a Variant array to be used to populate a ListBox. I can’t make this procedure a Boolean function because I need it to return an array. One option is to treat this function as an entry point, which has the effect of not pushing the error back into the calling procedure. This is the way I’ve been doing it. A second option is to make the function return a Boolean, but pass the “return” variable ByRef. This would change the way I call the procedure, but would still achieve the result I need.

Option 1:

Option 2:

I’m having second thoughts about choosing Option 1. How do you do it?

Passing Arguments to a Userform

Posted on by Dick Kusleika

The Initialize event of userforms doesn’t take an argument, so you can’t pass data directly to it. Here are three ways to pass a variable to a userform.

Global Variable

I avoid using global variables whenever practical, but depending on the complexity of the application, it may be the best choice. Create a global variable in a standard module and set it before showing the form.

Then you can use that variable in the userform’s code

The Tag Property

If you’re not using the Tag property for something else, you can store some data in it for later use. You just need to load the form before you set the property, then show it.

Properties

Because a userform is just a class module with a built-in user interface, you can create properties in the form’s module. The form’s module might look like this

and showing the form from a standard module

Redim an Array

Posted on by Dick Kusleika

Array variables can be static or dynamic. That’s determined by the Dim statement. If you specify dimensions when you declare the variable, it’s static and always will be. If you leave the dimensions blank in the Dim statement, it’s dynamic and can be changed.

Dynamic arrays can be changed using the Redim statement.

If you use Redim, all the data in your array is lost, unless you use the Preserve keyword. This keeps the data in tact, but limits what you can change with a Redim. For instance, when you use Preserve, you can only change the last dimension of the array. Sometimes you have to organize your array horizontally to accomodate this restriction.

Preserve is an expensive keyword, so you use it sparingly. Many people will Redim their arrays in blocks to avoid having to do it in every iteration of a loop.

I’m not a big fan of the block Redim, but if you have a really time intensive procedure and this shaves some valuable milliseconds, then go for it. If there’s a way to figure out the upper bounds of the array before you add data, then you may save time there also.