Excel Utilities and Macro Codes
The following utilities and macros were written in Excel 2000. They should be back compatable within reason.
The visual angle an object (stimulus) subtends varies with the size of that stimulus and the viewing distance. Visual Angle.xls is a 32 bit program that I've written in Excel. If you input any two of the above factors, it will calculate the third. Note that the stimulus size and the viewing distance should both in the same units and the visual angle should be in degrees. To use this program, download the zip file, unpack it and call up the xls file in Excel. 
Visual Angle Excel zipfile
This utility will generate a column of up to 500 random numbers without replacement. These can easily be cut and pasted into other spreadsheets to allow for such things as the random presentation of stimuli to subjects, the random assignment of subjects to condition, etc. It can also be handy for playing the lottery.To use this program, download the zip file, unpack it and call up the xls file in Excel. Note that if excel asks about the macros when you open this file, they must be enabled. Random Number Generator Excel zipfile
Here's some VBA code for an Excel macro that will join the contents of the selected cells of two or more adjacent columns of data into the cells of the first column (a space will separate the values). The secondary columns will be blank. For example, assume cell A1 is John, B1 is William and C1 is Smith. If these 3 cells are selected and the Join macro is run then A1 will become John William Smith and cells B1 and C1 will be blank. The reverse of this macro ("Split Column") can be found below.
Sub Join()
Application.ScreenUpdating = False
On Error Resume Next
iRows = Selection.Rows.Count
Set lastcell = Cells.SpecialCells(xlLastCell)
mRow = lastcell.Row
If mRow < iRows Then iRows = mRow
iCols = Selection.Columns.Count
For ir = 1 To iRows
newcell = Trim(Selection.Item(ir, 1).Value)
For ic = 2 To iCols
trimmed = Trim(Selection.Item(ir, ic).Value)
If Len(trimmed) <> 0 Then newcell = newcell & " " & trimmed
Selection.Item(ir, ic) = ""
Next ic
Selection.Item(ir, 1).Value = newcell
Next ir
Application.ScreenUpdating = True
End Sub
Here's some VBA code for an Excel macro that will split the contents of the selected cells into two columns of data. The cell(s) to the right of the selected cells must be blank (to avoid overwriting important data). It splits the data in the highlighted cell(s) at the first blank in those cells. Essentially this macro is the reverse of the "Join Columns" (see above) macro except that each blank character split in the selected cells must be done individually and each column must be done separately. For example, assume cell A1 is John William Smith, B1 is blank, and C1 is blank. If A1 is selected and the Split macro is run then A1 will become John and B1 will become William Smith. If B1 is then selected and the Split macro is run B1 will become William and C1 will become Smith.
.
Sub Split()
'--Application.ScreenUpdating = False
'On Error Resume Next
iRows = Selection.Rows.Count
Set lastcell = Cells.SpecialCells(xlLastCell)
mRow = lastcell.Row
If mRow < iRows Then iRows = mRow
For ir = 1 To iRows
If Len(Trim(Selection.Item(ir, 1).Offset(0, 1))) <> 0 Then
iAnswer = MsgBox("Found non-blank in adjacent column -- " _
& Selection.Item(ir, 1).Offset(0, 1) & " -- in " & _
Selection.Item(ir, 1).Offset(0, 1).AddressLocal(0, 0) & _
Chr(10) & "Press OK to process those than can be split", _
vbOKCancel)
If iAnswer = vbOK Then GoTo DoAnyWay
GoTo terminated
End If
Next ir
DoAnyWay:
For ir = 1 To iRows
If Len(Trim(Selection.Item(ir, 1).Offset(0, 1))) <> 0 Then GoTo nextrow
checkx = Trim(Selection.Item(ir, 1))
L = Len(Trim(Selection.Item(ir, 1)))
If L < 3 Then GoTo nextrow
For im = 2 To L
If Mid(checkx, im, 1) = " " Then
Selection.Item(ir, 1) = Left(checkx, im - 1)
Selection.Item(ir, 1).Offset(0, 1) = Trim(Mid(checkx, im + 1))
GoTo nextrow
End If
Next im
nextrow:
Next ir
terminated:
'--Application.ScreenUpdating = True
End Sub
Here's some VBA code for an Excel macro that will reverse the contents of the selected cells across both columns and rows (it reads and writes columns first and then rows). Note that this is quite different from simply sorting. A simple example: assume A1 is 3, A2 is 7, and A3 is 1. After highlighting these cells and running "Reverse" A1 is 1, A2 is 7 and A3 is 3.
A more complex example: assume A1 is 3, A2 is 7, A3 is 1, B1 is 11, B2 is 2, and B3 is 9. After highlighting these cells and running "Reverse" A1 is 9, A2 is 2, A3 is 11, B1 is 1, B2 is 7, and B3 is 3.
Sub ReversI()
'David McRitchie 07/30/1998 documented in
' http://www.oocities.org/davemcritchie/excel/excel.htm
'Reverse Item values in Range, Row, or Column [Ctrl+R]
'Items are counted proceeding down first column and
'continues top of range in next column.
'NOT RECOMMENDED WHEN FORMULAS ARE INVOLVED.
tCells = Selection.Count
mCells = tCells / 2
For iX = 1 To mCells
iValue = Selection.Item(iX).Value
oX = tCells + 1 - iX
Selection.Item(iX).Value = Selection.Item(oX).Value
Selection.Item(oX).Value = iValue
Next iX
End Sub
to Main Menu
for Contact and Update information