Floating Point Errors in Excel VBA

I recently had a project where I had to total dollar figures in two columns and compare that total to the reported total in a third. Easy, right? The VBA code was pretty simple: If Cells(ActiveCell.Row, 4).Value + Cells(ActiveCell.Row, 6).Value <> _      Cells(ActiveCell.Row, 8).Value Then         Cells(ActiveCell.Row, 10).Value = “Totals don’t match”         ‘(e.g. if D2 + F2 <> H2 Then J2 contains an error message) End If In a set of 70 rows, I kept getting two rows where the totals were equal, but reported as not equal. I tested the … Continue reading

Fix Gene Names Turned into Dates Problem

I became aware of this issue in http://www.sciencemag.org/news/sifter/one-five-genetics-papers-contains-errors-thanks-microsoft-excel?utm_source=newsfromscience&utm_medium=facebook-text&utm_campaign=excel-6929. The following user-defined Function is a start. You can add to the Case statement to add whatever gene names you like, with the caveat that once Excel finds a match, it will jump out of the Case statement. Add this to your Personal Macro Workbook and save it. You can then find the function in the User Defined Functions category of the Function Wizard. Add the function to a separate column, reference the first cell with the problem, copy it down the column, then Copy | Paste | Values into the same column to … Continue reading

Using Range.Offset in Excel VBA

To select a cell in Excel, you have two basic methods: RANGE and CELLS: Range (“A1”).Select Range(“RangeName”).Select Cells(3, 4).Select ‘Selects Row 3, Column 4, i.e. cell D3 Range works well for hard-coded cells. Cells works best with calculated cells, especially when you couple it  with a loop: For i = 1 to 10      Cells(i, 1).value = i ‘ fill A1 through A10 with the value of i Next i Note that your focus does not change. Whatever cell you were in when you entered the loop is where you are when you leave the loop. This is way faster … Continue reading

Excel VBA – Constants and Cell References

One of the techniques I have found very useful in writing Excel VBA code is to make the cell, row, and column references public constants rather than hard coding them. Take a reference to a row and column: cells(4,27).value If row 4 is the first data row and column 27 is the last column in the list, it is more useful to make them constants and refer to the constant: Public Const intFirstDataRow As Integer = 4 Public Const intLastRow As Integer = 27 And then make the above reference: cells(intFirstDataRow, intLastRow).value This way, when you add a couple of … Continue reading