How to find the first empty cell at the bottom of a column in a worksheet using VBA code.

A common task when using WinWedge to feed data to an Excel spreadsheet using Dynamic Data Exchange (DDE) is to stack data received from WinWedge in a column in a worksheet where each new data reading is always stored at the bottom of a column in the worksheet. The problem is how to find the first empty cell at the bottom of a column in the most efficient way.

There are a number of ways to accomplish this however the most efficient way that we have found so far is using the technique in the following Excel VBA function.
The following subroutine accepts either a sheet name (or the ordinal number for a sheet) and a column number as input variables and then returns the row number of the first empty cell at the bottom of the specified column.

Function FindBottomRow(WhatSheet As Variant, WhatColumn As Long) As Long
Dim R As Long
R = Sheets(WhatSheet).Cells(65534, WhatColumn).End(xlUp).Row
If Len(Sheets(WhatSheet).Cells(R, WhatColumn).Text) Then R = R + 1
FindBottomRow = R
End Function

Launching programs directly from Excel using VBA code.

The VBA programming language in Excel provides a "Shell" function that you can use to launch other application programs however you need to know the exact file location of the program that you want to launch.

Suppose that you want to launch a program and force it to open a specific file? For example, suppose that you want to launch Microsoft Word from an Excel spreadsheet and have it automatically open a specific document file or suppose that you want to launch WinWedge from Excel and have WinWedge open a specific configuration file.
Most programs that work with documents (or configuration files) will accept the name of the document or configuration file on the command line used to launch the program and then automatically open the specified file as soon as the program loads into memory however there is a much easier way to accomplish the same thing using a special Windows API function called ShellExecute.

The ShellExecute command allows you to specify only the name of the file that you want to open and it will then automatically launch the correct application that is associated with the filename extension for the file and feed it the document (or configuration file) on the command line.

For example, the filename extension ".DOC" is normally associated with Microsoft Word therefore if you call the ShellExecute function and pass it the filename for a Word document that has the extension ".DOC", the ShellExecute function will automatically launch Microsoft Word (no matter what folder it is installed in) and open the specified document. The same is true for WinWedge configuration files that have the .SW3 filename extension. In fact, you can even pass in a string containing a URL for a web page and the ShellExecute function will automatically open the default web browser and display the specified web page.

The following Excel VBA subroutine demonstrates the technique:

Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) _
As Long

Sub LaunchDocument(FileToLaunch As String)

x = ShellExecute(0, "open", FileToLaunch, vbNullString, vbNullString, 0)

' optional - if you want to give the program time to load and then switch
' the input focus back to Excel, you can use the following lines of code:

' Application.Wait Now + TimeValue("00:00:03") ' wait 3 seconds
' AppActivate Application.Caption ' switch focus back to Excel
End Sub

Create a chart that updates automatically as you enter data into a column in a spreadsheet.

Background:
A common application for the Software Wedge is to input data from an instrument to an Excel spreadsheet and also have a chart in the spreadsheet that automatically updates itself as you add new data to the sheet.
The following steps describe a very simple way to accomplish this without having to write any code.

1. Enter a number into cell A1 and press the Enter key. (Any number will do.)

2. In Excel's main menu, select INSERT, NAME and DEFINE
and then enter the name "ChartData" in the textbox labeled "Names in workbook"
and then enter the following formula in the textbox labeled "Refers to:"
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A))
(Note: the above formula contains references to "Sheet1" - if your chart data is in a different sheet, change this to the desired sheet name)
When you are finished entering the above formula, click the button labeled "Add" and then click the button labeled "Close". The above formula returns a range consisting of all data in column A starting in cell A1.

3. Insert a chart in your spreadsheet using the Excel Chart Wizard.
Select INSERT and CHART... from the Excel main menu and then walk through the chart wizard options.
In step 2 of the Chart Wizard, make sure that you leave the default settings for the "Data Range" value.

4. Select your chart in the worksheet by clicking on it and and then select CHART and SOURCE DATA... from the Excel main menu.
Click on the "Series" tab in the window that appears and then enter the following formula in the "Values" textbox for the series named "Series1".
=Sheet1!ChartData
Finally, click the OK button to return to the spreadsheet and enter data into column A.
The chart will automatically update as you enter new data into the worksheet in column A.

Cycle through a range of cells by pressing the Tab or Enter keys.

When you highlight a range of cells in Excel and then enter values into that range and pressing the Enter key after each data value, Excel will move the cursor down one cell each time you press the Enter key until it reaches the bottom of the highlighted range. When you press the Enter key while the cursor is in a cell at the bottom of the highlighted range, Excel will automatically move the cursor to the top of the range one column to the right of the previous column. If you press the Enter key while the cursor is in the the very last cell in the lower right corner of the highlighted range, Excel will automatically move to the cell in the upper left corner of the range.
If you do the same thing except instead of pressing the Enter key, you press the TAB key after you type each data value, the cursor will cycle through the highlighted range moving to the right and down.

This interesting behavior can be taken advantage of when using WinWedge in "Send Keystrokes" mode to automatically fill a specific range of cells with data. All you would need to do is to highlight the range of cells where you want the data to go and then configure WinWedge to issue the necessary "Postamble Keystrokes" that will cause the cursor to cycle through the range the way you want. For example, if you want to have the data go into the range moving down and to the right through the range, you would configure WinWedge to issue an "Enter" keystroke after each data value. If you want the cursor to move to the right and down through the range, you would configure WinWedge to issue a "TAB" keystroke after every data value.

For an excellent introduction to automating Excel using macros visit:
http://www.taltech.com/support/sw_tricks/exmacros.htm