Hello,

I have an excel workbook that has a worksheet that consists of 30 columns
with 1000 rows.

One of my main columns of input is the Admit Date of a Patient. From the
Admit Date, various Due Dates are generated at specific points in time
(ie.14 days, 30 days, 60 days etc)

So, I would have a formula as follows -
=IF(AND(Patient_Name<"",Admit_Date<""),Admit_Dat e+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.

I am trying to figure out ways to reduce my 10 Mbs excel workbook.

Thanks
Ruan

I beleive formulas are easier. VBA will in one way or
another have to copy the formulas down or something
similar. That is my opinion, anyway.

-----Original Message-----
Hello,

I have an excel workbook that has a worksheet that
consists of 30 columns
with 1000 rows.

One of my main columns of input is the Admit Date of a
Patient. From the
Admit Date, various Due Dates are generated at specific
points in time
(ie.14 days, 30 days, 60 days etc)

So, I would have a formula as follows -
=IF(AND
(Patient_Name<"",Admit_Date<""),Admit_Date+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.

I am trying to figure out ways to reduce my 10 Mbs excel
workbook.

Thanks
Ruan


.

Hi,

I've seen some people do nothing but use formulas, that
are nearly as complicated as programming (in a limited
sense with lots of nested if statements etc.). But, I
won't hesitate to do all my work with VBA. I abandoned
the formula method of Excel automation years ago (except
in some cases where it's just convenient etc.).

Once you have the VBA code written in place, it's just a
matter of having the computer loop it through from one end
of the range to another. You can then do incredibly more
and more...where you'll find that it seems like that there
are no limits...just your imagination. The computer
processing speed and amount of data one has, also has to
be considered whether you want VBA programming or formulas.

Once you learn and use VBA on a regular basis, I think
you'll find it much better. Anyway, that is my personal
preference.

It will be interesting to hear other responses. I'm sure
everyone has a different answer with what works best for
them.

Rick

-----Original Message-----
Hello,

I have an excel workbook that has a worksheet that
consists of 30 columns
with 1000 rows.

One of my main columns of input is the Admit Date of a
Patient. From the
Admit Date, various Due Dates are generated at specific
points in time
(ie.14 days, 30 days, 60 days etc)

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.

I am trying to figure out ways to reduce my 10 Mbs excel
workbook.

Thanks
Ruan


.

Rick,
I am one of the guys that do the formulas that get really complicated, etc.
They work but I feel that I would be much better off using VBA--except I
have never gotten started learning it.
I've looked at books, etc., but they are all fairly advanced and I just
didn't want to put the time in.
What would u suggest to get started in VBA on a real gradual learning curve?
Tx,
S
"Rick" wrote in message
...
Hi,

I've seen some people do nothing but use formulas, that
are nearly as complicated as programming (in a limited
sense with lots of nested if statements etc.). But, I
won't hesitate to do all my work with VBA. I abandoned
the formula method of Excel automation years ago (except
in some cases where it's just convenient etc.).

Once you have the VBA code written in place, it's just a
matter of having the computer loop it through from one end
of the range to another. You can then do incredibly more
and more...where you'll find that it seems like that there
are no limits...just your imagination. The computer
processing speed and amount of data one has, also has to
be considered whether you want VBA programming or formulas.

Once you learn and use VBA on a regular basis, I think
you'll find it much better. Anyway, that is my personal
preference.

It will be interesting to hear other responses. I'm sure
everyone has a different answer with what works best for
them.

Rick

-----Original Message-----
Hello,

I have an excel workbook that has a worksheet that
consists of 30 columns
with 1000 rows.

One of my main columns of input is the Admit Date of a
Patient. From the
Admit Date, various Due Dates are generated at specific
points in time
(ie.14 days, 30 days, 60 days etc)

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.

I am trying to figure out ways to reduce my 10 Mbs excel
workbook.

Thanks
Ruan


.

Follow these steps.
Note: This code assumes all relevant data for each admit date is located in the same row

Name the Range where Your Admit dates are entered Admit_Date using: Insert Name Defin

Name the Range where Your Patient Names are entered Patient_Name using: Insert Name Defin

Name the Range where Your Due Dates are entered Due_Date14 using: Insert Name Defin
Due_Date30..

Go to you Projects window. dbl-click your worksheet, should look like: Sheet1("xxxxx"

In your code window(to the Right), In left dropdown box choose Worksheet,
In Right dropdown box choose Chang

In the The worksheets Change event put in this code

Private Sub Worksheet_Change(ByVal Target As Range)'<< Provide
On Error Resume Nex
isect = Application.Intersect(Target, Range("AdmitDate")
If isect Is Nothing Then exit su
If Target ="" Then Exit Su
If Target.Cells.Count = 1 And IsDate(Target.value) And
Cells(Target.Row, Range(" Patient_Name").Column).Value<"" The

Cells(Target.Row, Range(" Due_Date14").Column).Value = CDate(Target.Value +14
Cells(Target.Row, Range(" Due_Date30").Column).Value = CDate(Target.Value +30
Cells(Target.Row, Range(" Due_Date60").Column).Value = CDate(Target.Value +60
' Put more as neede
End i
End Su
You may need to do a save and close to force code to activate for first time
Now, any time you enter a new admit date this code will fire and fill in the rest of the fields for you


----- Ruan wrote: ----

Hello

I have an excel workbook that has a worksheet that consists of 30 column
with 1000 rows

One of my main columns of input is the Admit Date of a Patient. From th
Admit Date, various Due Dates are generated at specific points in tim
(ie.14 days, 30 days, 60 days etc

So, I would have a formula as follows
=IF(AND(Patient_Name<"",Admit_Date<""),Admit_Dat e+30,""

My question is, which direction is more efficient
1) Creating formulas that are copied for 1000 rows o
2) Creating VBA code that only enters the result once an input has bee
entered in the Admit Date column

I am trying to figure out ways to reduce my 10 Mbs excel workbook

Thank
Rua

Put Set in front of isect..
Set isect = Application.Intersect(Target, Range("AdmitDate")


----- chris:Use this code wrote: ----

Follow these steps.
Note: This code assumes all relevant data for each admit date is located in the same row

Name the Range where Your Admit dates are entered Admit_Date using: Insert Name Defin

Name the Range where Your Patient Names are entered Patient_Name using: Insert Name Defin

Name the Range where Your Due Dates are entered Due_Date14 using: Insert Name Defin
Due_Date30..

Go to you Projects window. dbl-click your worksheet, should look like: Sheet1("xxxxx"

In your code window(to the Right), In left dropdown box choose Worksheet,
In Right dropdown box choose Chang

In the The worksheets Change event put in this code

Private Sub Worksheet_Change(ByVal Target As Range)'<< Provide
On Error Resume Nex
isect = Application.Intersect(Target, Range("AdmitDate")
If isect Is Nothing Then exit su
If Target ="" Then Exit Su
If Target.Cells.Count = 1 And IsDate(Target.value) And
Cells(Target.Row, Range(" Patient_Name").Column).Value<"" The

Cells(Target.Row, Range(" Due_Date14").Column).Value = CDate(Target.Value +14
Cells(Target.Row, Range(" Due_Date30").Column).Value = CDate(Target.Value +30
Cells(Target.Row, Range(" Due_Date60").Column).Value = CDate(Target.Value +60
' Put more as neede
End i
End Su
You may need to do a save and close to force code to activate for first time
Now, any time you enter a new admit date this code will fire and fill in the rest of the fields for you


----- Ruan wrote: ----

Hello

I have an excel workbook that has a worksheet that consists of 30 column
with 1000 rows

One of my main columns of input is the Admit Date of a Patient. From th
Admit Date, various Due Dates are generated at specific points in tim
(ie.14 days, 30 days, 60 days etc

So, I would have a formula as follows
=IF(AND(Patient_Name<"",Admit_Date<""),Admit_Dat e+30,""

My question is, which direction is more efficient
1) Creating formulas that are copied for 1000 rows o
2) Creating VBA code that only enters the result once an input has bee
entered in the Admit Date column

I am trying to figure out ways to reduce my 10 Mbs excel workbook

Thank
Rua

"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).

Private Sub Worksheet_Change(ByVal Target As Range
On Error Resume Nex
Set isect = Application.Intersect(Target, Range("Admit_Date")
If isect Is Nothing Then Exit Su
If Target ="" Then Exit Su
If Target.Cells.Count = 1 And IsDate(Target.value) And
Cells(Target.Row, Range("Patient_Name").Column).Value<"" The

Cells(Target.Row, Range("Due_Date14").Column).Value = CDate(Target.Value +14
Cells(Target.Row, Range("Due_Date30").Column).Value = CDate(Target.Value +30
Cells(Target.Row, Range("Due_Date60").Column).Value = CDate(Target.Value +60

End i
End Su


----- chris:Use this code wrote: ----

Follow these steps.
Note: This code assumes all relevant data for each admit date is located in the same row

Name the Range where Your Admit dates are entered Admit_Date using: Insert Name Defin

Name the Range where Your Patient Names are entered Patient_Name using: Insert Name Defin

Name the Range where Your Due Dates are entered Due_Date14 using: Insert Name Defin
Due_Date30..

Go to you Projects window. dbl-click your worksheet, should look like: Sheet1("xxxxx"

In your code window(to the Right), In left dropdown box choose Worksheet,
In Right dropdown box choose Chang

In the The worksheets Change event put in this code

Private Sub Worksheet_Change(ByVal Target As Range)'<< Provide
On Error Resume Nex
isect = Application.Intersect(Target, Range("AdmitDate")
If isect Is Nothing Then exit su
If Target ="" Then Exit Su
If Target.Cells.Count = 1 And IsDate(Target.value) And
Cells(Target.Row, Range(" Patient_Name").Column).Value<"" The

Cells(Target.Row, Range(" Due_Date14").Column).Value = CDate(Target.Value +14
Cells(Target.Row, Range(" Due_Date30").Column).Value = CDate(Target.Value +30
Cells(Target.Row, Range(" Due_Date60").Column).Value = CDate(Target.Value +60
' Put more as neede
End i
End Su
You may need to do a save and close to force code to activate for first time
Now, any time you enter a new admit date this code will fire and fill in the rest of the fields for you


----- Ruan wrote: ----

Hello

I have an excel workbook that has a worksheet that consists of 30 column
with 1000 rows

One of my main columns of input is the Admit Date of a Patient. From th
Admit Date, various Due Dates are generated at specific points in tim
(ie.14 days, 30 days, 60 days etc

So, I would have a formula as follows
=IF(AND(Patient_Name<"",Admit_Date<""),Admit_Dat e+30,""

My question is, which direction is more efficient
1) Creating formulas that are copied for 1000 rows o
2) Creating VBA code that only enters the result once an input has bee
entered in the Admit Date column

I am trying to figure out ways to reduce my 10 Mbs excel workbook

Thank
Rua

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).

The way to get started, is to get some easy-to-learn books
and be persistent. It takes years to learn programming.
No matter how hard or how boring at times, you keep at
it. All of the sudden, it starts making sense.

You might first start with one of Excel workbooks that
contain formulas, that you have. As a fun project, try to
convert it to a program with VBA. I'm sure a lot of
people here would help you get through the bottlenecks.

People make fun of me when I say this, but I admit to
using one of the yellow Dummies books. I first was using
the Learn-VBA-In-21-Days book, and then found the Dummies
book (Visual Basic 3 for Dummies) filled in the gaps for
me. I'm self-taught and now am very advanced. I got
there by swallowing my pride and starting with the most
simple books.

Rick

-----Original Message-----
Rick,
I am one of the guys that do the formulas that get really
complicated, etc.
They work but I feel that I would be much better off
using VBA--except I
have never gotten started learning it.
I've looked at books, etc., but they are all fairly
advanced and I just
didn't want to put the time in.
What would u suggest to get started in VBA on a real
gradual learning curve?
Tx,
S
"Rick" wrote in
message
...
Hi,

I've seen some people do nothing but use formulas, that
are nearly as complicated as programming (in a limited
sense with lots of nested if statements etc.). But, I
won't hesitate to do all my work with VBA. I abandoned
the formula method of Excel automation years ago (except
in some cases where it's just convenient etc.).

Once you have the VBA code written in place, it's just a
matter of having the computer loop it through from one
end
of the range to another. You can then do incredibly
more
and more...where you'll find that it seems like that
there
are no limits...just your imagination. The computer
processing speed and amount of data one has, also has to
be considered whether you want VBA programming or
formulas.

Once you learn and use VBA on a regular basis, I think
you'll find it much better. Anyway, that is my personal
preference.

It will be interesting to hear other responses. I'm
sure
everyone has a different answer with what works best for
them.

Rick

-----Original Message-----
Hello,

I have an excel workbook that has a worksheet that
consists of 30 columns
with 1000 rows.

One of my main columns of input is the Admit Date of a
Patient. From the
Admit Date, various Due Dates are generated at specific
points in time
(ie.14 days, 30 days, 60 days etc)

So, I would have a formula as follows -
=IF(AND
(Patient_Name<"",Admit_Date<""),Admit_Date+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.

I am trying to figure out ways to reduce my 10 Mbs
excel
workbook.

Thanks
Ruan


.



.

Hello Rick,

I actually have the following books -
1) VBA for Dummies
2) Excel 2002 Formulas - Walkenbach
3) Excel 2002 Power Programming with VBA - Walkenbach

I have written simple VBA macros, but I realize there is a great deal more I
need to learn. My company owns 25 medical facilities in California, and I
have convinced my boss to invest in my department to develop an application
with Visual Studio .NET into a SQL database.

Thanks
Ruan



"Rick" wrote in message
...
The way to get started, is to get some easy-to-learn books
and be persistent. It takes years to learn programming.
No matter how hard or how boring at times, you keep at
it. All of the sudden, it starts making sense.

You might first start with one of Excel workbooks that
contain formulas, that you have. As a fun project, try to
convert it to a program with VBA. I'm sure a lot of
people here would help you get through the bottlenecks.

People make fun of me when I say this, but I admit to
using one of the yellow Dummies books. I first was using
the Learn-VBA-In-21-Days book, and then found the Dummies
book (Visual Basic 3 for Dummies) filled in the gaps for
me. I'm self-taught and now am very advanced. I got
there by swallowing my pride and starting with the most
simple books.

Rick

-----Original Message-----
Rick,
I am one of the guys that do the formulas that get really
complicated, etc.
They work but I feel that I would be much better off
using VBA--except I
have never gotten started learning it.
I've looked at books, etc., but they are all fairly
advanced and I just
didn't want to put the time in.
What would u suggest to get started in VBA on a real
gradual learning curve?
Tx,
S
"Rick" wrote in
message
...
Hi,

I've seen some people do nothing but use formulas, that
are nearly as complicated as programming (in a limited
sense with lots of nested if statements etc.). But, I
won't hesitate to do all my work with VBA. I abandoned
the formula method of Excel automation years ago (except
in some cases where it's just convenient etc.).

Once you have the VBA code written in place, it's just a
matter of having the computer loop it through from one
end
of the range to another. You can then do incredibly
more
and more...where you'll find that it seems like that
there
are no limits...just your imagination. The computer
processing speed and amount of data one has, also has to
be considered whether you want VBA programming or
formulas.

Once you learn and use VBA on a regular basis, I think
you'll find it much better. Anyway, that is my personal
preference.

It will be interesting to hear other responses. I'm
sure
everyone has a different answer with what works best for
them.

Rick

-----Original Message-----
Hello,

I have an excel workbook that has a worksheet that
consists of 30 columns
with 1000 rows.

One of my main columns of input is the Admit Date of a
Patient. From the
Admit Date, various Due Dates are generated at specific
points in time
(ie.14 days, 30 days, 60 days etc)

So, I would have a formula as follows -
=IF(AND
(Patient_Name<"",Admit_Date<""),Admit_Date+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.

I am trying to figure out ways to reduce my 10 Mbs
excel
workbook.

Thanks
Ruan


.



.

Rick,
I am with you and believe you are right.
That is my approach as well--simplest first, to learn the basics, then go up
from there.
I have had trouble finding books that were simple enough but I'll have
another look.
Tx for the feedback.
S

"Rick" wrote in message
...
The way to get started, is to get some easy-to-learn books
and be persistent. It takes years to learn programming.
No matter how hard or how boring at times, you keep at
it. All of the sudden, it starts making sense.

You might first start with one of Excel workbooks that
contain formulas, that you have. As a fun project, try to
convert it to a program with VBA. I'm sure a lot of
people here would help you get through the bottlenecks.

People make fun of me when I say this, but I admit to
using one of the yellow Dummies books. I first was using
the Learn-VBA-In-21-Days book, and then found the Dummies
book (Visual Basic 3 for Dummies) filled in the gaps for
me. I'm self-taught and now am very advanced. I got
there by swallowing my pride and starting with the most
simple books.

Rick

-----Original Message-----
Rick,
I am one of the guys that do the formulas that get really
complicated, etc.
They work but I feel that I would be much better off
using VBA--except I
have never gotten started learning it.
I've looked at books, etc., but they are all fairly
advanced and I just
didn't want to put the time in.
What would u suggest to get started in VBA on a real
gradual learning curve?
Tx,
S
"Rick" wrote in
message
...
Hi,

I've seen some people do nothing but use formulas, that
are nearly as complicated as programming (in a limited
sense with lots of nested if statements etc.). But, I
won't hesitate to do all my work with VBA. I abandoned
the formula method of Excel automation years ago (except
in some cases where it's just convenient etc.).

Once you have the VBA code written in place, it's just a
matter of having the computer loop it through from one
end
of the range to another. You can then do incredibly
more
and more...where you'll find that it seems like that
there
are no limits...just your imagination. The computer
processing speed and amount of data one has, also has to
be considered whether you want VBA programming or
formulas.

Once you learn and use VBA on a regular basis, I think
you'll find it much better. Anyway, that is my personal
preference.

It will be interesting to hear other responses. I'm
sure
everyone has a different answer with what works best for
them.

Rick

-----Original Message-----
Hello,

I have an excel workbook that has a worksheet that
consists of 30 columns
with 1000 rows.

One of my main columns of input is the Admit Date of a
Patient. From the
Admit Date, various Due Dates are generated at specific
points in time
(ie.14 days, 30 days, 60 days etc)

So, I would have a formula as follows -
=IF(AND
(Patient_Name<"",Admit_Date<""),Admit_Date+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.

I am trying to figure out ways to reduce my 10 Mbs
excel
workbook.

Thanks
Ruan


.



.