Cell Formulas vs. VBA
Thanks for all your great responses. I did a little test. I created two
identicle workbooks with the difference being that in one I used formulas
(5 columns by 1000 rows) and in the other I used VBA code with the help of
the News Group.
I noticed that VBA code reduces the size considerably -
1) Workbook - Formulas = 214 Kb
2) Workbook - VBA = 74 Kb
Obviously, my existing workbook I created for our various Medical facilities
consists of more complicated formulas than what I presented to you. My
workbook when blank has a size of 5.66 Mb. My test proves that I can reduce
this with VBA code and make my workbook more efficient for our Users.
Unfortuantely, I am still in the beginning stages of learning VBA code.
Thanks again
Ruan
"Harlan Grove" wrote in message
...
"Ruan" wrote...
...
So, I would have a formula as follows -
=IF(AND(Patient_Name<"",Admit_Date<""),Admit_Da te+30,"")
My question is, which direction is more efficient -
1) Creating formulas that are copied for 1000 rows or
2) Creating VBA code that only enters the result once an input has
been entered in the Admit Date column.
...
If you're really concerned with efficiency, you shouldn't be using
spreadsheets at all. A Tk frontend on a simple XML format would be much
more
storage and speed efficient than anything involving Excel.
That said, there are ways to improve efficiency by *simplifying* formulas.
If the <"" tests in your formula are intended to check if the two
referenced cells are nonblank, you could replace your formula with
=IF(COUNTA(Patient_Name,Admit_Date)=2,Admit_Date+3 0,"")
The event handler alternative would require code to figure out which cells
should be used as patient name and admit date. Probably on the same row as
the changed cell. Then you have to have code to exit the event handler
quickly if you haven't entered the required info to fill in the other
dates.
Presumably you don't want to fill in 30-Jan-1900 when you enter patient
name
before entering admit date.
So you'd need something like
Private Sub Worksheet_Change(ByVal Target As Range)
Dim da As Variant, i As Long
If Target.Column < Evaluate("Admit_Date").Column Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
da = Array(15, 30, 45, 60)
If Not (IsEmpty(Target.Value) Or IsEmpty(Target.Offset(0, _
Evaluate("Patient_Name").Column - Target.Column).Value)) Then
For i = LBound(da) To UBound(da)
Target.Offset(0, Evaluate("Admit_" & Format(da(i))).Column _
- Evaluate("Admit_Date").Column).Value = Target.Value + da(i)
Next i
End If
CleanUp:
Application.EnableEvents = True
End Sub
Spreadsheet formulas represent a functional programming language biased
towards rectangular data structures. VBA despite its object-oriented
veneer
is a procedural language. If you think more readily in functional
programming constructs, use formulas. If you find it easier to thing
procedurally, use VBA. If the workbook would be used by others (and some
day
maintained by others), use whatever is clearest (generally a handful of
formulas beats even the best written VBA for clarity for other
maintainers).
|