Searching \ for '[OT] Book recommendations for VBA' in subject line. ()
Make payments with PayPal - it's fast, free and secure! Help us get a faster server
FAQ page: www.piclist.com/techref/index.htm?key=book+recommendations
Search entire site for: 'Book recommendations for VBA'.

Exact match. Not showing close matches.
PICList Thread
'[OT] Book recommendations for VBA'
2005\07\04@172550 by John Pearson

flavicon
face
I need to do some macros in Excel. Am I correct in assuming that I should learn VBA to do this?

If so, can anyone recommend a good book for begginers VBA.

Things I will want to do is to be able to place some values that will arrive in A1 through D1 into other cells, to form a list. I will also like to be able to visually graph the data that is arriving and being placed into other cells in real time. Is this doable with VBA?

Thanks

John

2005\07\04@233451 by Don Taylor

flavicon
face

On Mon, 4 Jul 2005, John Pearson wrote:
> I need to do some macros in Excel. Am I correct in assuming that I
> should learn VBA to do this?

Yes, if you have any recent version of Excel

> If so, can anyone recommend a good book for begginers VBA.

There is an old O'Reilly book you might find, "Writing Excel Macros."

John Walkenbach has also written a number of books on Excel and VBA,
his books tend to copy the bulk of what he has written before.

There is a new thick book out this year on Excel VBA but I can't
find the title here at the moment.

> Things I will want to do is to be able to place some values that will
> arrive in A1 through D1 into other cells, to form a list. I will also
> like to be able to visually graph the data that is arriving and being
> placed into other cells in real time. Is this doable with VBA?

"Arrays" in macros are feasible.  Sometimes there are even shreds of
documentation on them.  Sometimes they act in very opaque fashions.

Array parameters and functions returning arrays can be more challenging.
I struggled for a while with a project that was doing 3-d geometry,
arrays, vectors, dot, cross, norm, etc.  It can be done but there can
be a lot of head scratching and occasional deadends.

"real time" I've never even considered doing with Excel/VBA.

Sometimes the microsoft.public.excel.programming newsgroup can be of
assistance, either searching old postings or when that doesn't work
asking new questions.

Toss me mail if you like, I'll tell you what I know and what I don't.

2005\07\05@041041 by Tony Smith

picon face
> I need to do some macros in Excel. Am I correct in assuming that I should
> learn VBA to do this?
>
> If so, can anyone recommend a good book for begginers VBA.
>
> Things I will want to do is to be able to place some values that will
> arrive in A1 through D1 into other cells, to form a list. I will also like
> to be able to visually graph the data that is arriving and being placed
> into other cells in real time. Is this doable with VBA?
>
> Thanks
>
> John


You really need to know two things, Excel & VBA.  If you don't know Excel,
you end up writing VBA routines to emulate (poorly) what Excel already
does.

The graphing you describe can be done with plain Excel, with the VBA macro
running to pull in data.  You might want to explain "data that is arriving
and being placed into other cells in real time" a bit more.

VBA is just like VB.  Excel 97 = VB5, Excel 2000 & above = VB6.  There's
not a great deal of difference between the two.  eg VB6 added the Split &
Join functions, lets you return arrays from funtions etc.

Like Don Taylor, I'd say look at the John Walkenbach books.  There's one
book just on charts.  He has a VBA one and is currently writing 'VBA for
Dummies'.  He may have finished that.  His web site is
http://www.j-walk.com, and has bunch of stuff you might find useful.

The book Don may be thinking of is "Professional Excel Development", which
is a bit heavy duty.

For people who know Excel and VB (but not how to use one with the other),
Wrox puts out a fairly good book.  It's a bit terse, as it skips the
basics.  The key to Excel VBA is knowing Excel.  Figure out how Excel does
something, then code it.  The other tip is don't write cell by cell, put
your data in an array, then copy it to the sheet (faster).

One solution to your problem is with PivotCharts (XL2000+).  A PivotChart
can be set up to "Get External Data", and refresh every minute.  (A
refresh macro can speed that up).  External data can be anything from a
text file, database query, or another spreadsheet, which you can update or
replace as needed.  This can be a zero VBA solution, is your idea of
"real-time" is "every 60 seconds".

Tony

2005\07\05@072810 by spiridon a. gkoutzamanis

flavicon
face
try "VBA for Dummies"
I Bought it and itisi quite good!

2005\07\05@101223 by John Pearson

flavicon
face
Thanks to all for the very usefull help.

The data input is from another program via DDE. 2 to 5 values will arrive
about every second or maybe 2.

And thanks for the advice to learn Excel as I do not know Excel and that
could save me some headaches to do so first.

John

----- Original Message -----
From: "spiridon a. gkoutzamanis" <spam_OUTsgoutTakeThisOuTspamtee.gr>
To: "Microcontroller discussion list - Public." <.....piclistKILLspamspam@spam@mit.edu>
Sent: Tuesday, July 05, 2005 3:57 AM
Subject: Re: [OT] Book recommendations for VBA


> try "VBA for Dummies"
> I Bought it and itisi quite good!
> -

2005\07\09@112820 by Tony Smith

picon face
part 1 3535 bytes content-type:text/plain; (decoded 7bit)

Hi John,

You won't find any info on DDE in recent books, that's fairly old stuff.  I had a look thru my book collection, a book on VB4 gave
it an entire paragraph.  Another book, "Developing Applications with MS Office", (published in 1995 and referring to Office V4) has
a short chapter, amusing called "DDE: It's not dead yet!".

The way I'd do it is to write a small VB app to handle the DDE, and write the data to a text file.  An Excel spreadsheet can then
load & graph this text file.  By decoupling the two tasks, it should be more reliable, and easier to upgrade in future.

The zip I've attached has 2 files, an Excel sheet and a text file with dummy data.  There's a little bit of VBA code, 4 lines
essentially.

When you open the sheet, a macro triggers to load the data file, and refresh itself every second.  With Excel still open, open the
.txt file, add/edit/delete, save and watch the sheet update.  Neat, huh?

Most of the hard work is done by Excel itself, hence the "learn Excel first" comment made previously.  First, a Query is used to
load the data.  Look at "Data / Get External Data / Import Text file".  More than just "open file", this is essentially "Get * from
file.txt" as Excel treats it as just another data source.

Second, named ranges are used to define where the data is (See Insert / Names / Define).  Rather than refer to cells like A1, A2:A9,
you can give them names, just like variable names.

Take this one step further by using formulas, you get what's called self-expanding ranges.  As the number of rows changes, so does
the area defined by the named ranges.  In this case, I've called them X_Values & Y_Values.

Since the chart refers to the named ranges, it's guaranteed to include all the data points.

The macros are simple.  On opening, the path where the data file resides is fixed (Connection string).  Next, the Query to get the
data is refreshed (chart updates automatically).  Finally, a curious item in Excel, the OnTime method, is used.  This lets you
program a Date & Time for when a macro will run.  In this case, we want to re-run this macro in one seconds time.  Upon closing,
this timer is disabled.

Note that the query can be set to refresh itself, but the minimum interval is 1 minute.  By using that, you don't need the macros.
(Except perhaps to set the Connection string for the Query.)

I'm sure this is completely different to what you envisaged!  :-)

Tony


> {Original Message removed}
part 2 10535 bytes content-type:application/octet-stream; (decode)

part 3 35 bytes content-type:text/plain; charset="us-ascii"
(decoded 7bit)

2005\07\09@143311 by Peter

picon face
part 1 715 bytes content-type:TEXT/PLAIN; CHARSET=US-ASCII; format=flowed

On Sun, 10 Jul 2005, Tony Smith wrote:

> Hi John,
>
> You won't find any info on DDE in recent books, that's fairly old
> stuff.  I had a look thru my book collection, a book on VB4 gave it an
> entire paragraph.  Another book, "Developing Applications with MS
> Office", (published in 1995 and referring to Office V4) has a short
> chapter, amusing called "DDE: It's not dead yet!".

At least it is hiding successfully. I have tried to use DDE a few times
(from tcl scripts on win32) with the result that it works in test
instances but I cannot ever be sure whether the DDE 'resource name' they
made up for some application will be the same one I made up.

Peter

part 2 10535 bytes content-type:APPLICATION/OCTET-STREAM; NAME="Chart test.zip" (decode)

part 3 35 bytes content-type:text/plain; charset="us-ascii"
(decoded 7bit)

2005\07\10@114434 by John Pearson

flavicon
face
Thank you very much for that Tony. I appreciate the effort. I will certainly
look this over and learn from it.

I have a quick question, I think it may be simple, and perhaps it is already
in the example code you sent along (excuse me if so, but I have someone who
needs this feature last week and it may take me some time to find it)....

Is there a real simple way to get Excel to automatically copy the contents
of a cell to another cell every time the first cell is updated or changed.

Thanks again!

John

----- Original Message -----
From: "Tony Smith" <ajsmithspamKILLspamrivernet.com.au>
To: "Microcontroller discussion list - Public." <.....piclistKILLspamspam.....mit.edu>
Sent: Saturday, July 09, 2005 8:30 AM
Subject: RE: [OT] Book recommendations for VBA


> Hi John,
>
> You won't find any info on DDE in recent books, that's fairly old stuff.
I had a look thru my book collection, a book on VB4 gave
> it an entire paragraph.  Another book, "Developing Applications with MS
Office", (published in 1995 and referring to Office V4) has
> a short chapter, amusing called "DDE: It's not dead yet!".
>
> The way I'd do it is to write a small VB app to handle the DDE, and write
the data to a text file.  An Excel spreadsheet can then
> load & graph this text file.  By decoupling the two tasks, it should be
more reliable, and easier to upgrade in future.
>
> The zip I've attached has 2 files, an Excel sheet and a text file with
dummy data.  There's a little bit of VBA code, 4 lines
> essentially.
>
> When you open the sheet, a macro triggers to load the data file, and
refresh itself every second.  With Excel still open, open the
> .txt file, add/edit/delete, save and watch the sheet update.  Neat, huh?
>
> Most of the hard work is done by Excel itself, hence the "learn Excel
first" comment made previously.  First, a Query is used to
> load the data.  Look at "Data / Get External Data / Import Text file".
More than just "open file", this is essentially "Get * from
> file.txt" as Excel treats it as just another data source.
>
> Second, named ranges are used to define where the data is (See Insert /
Names / Define).  Rather than refer to cells like A1, A2:A9,
> you can give them names, just like variable names.
>
> Take this one step further by using formulas, you get what's called
self-expanding ranges.  As the number of rows changes, so does
> the area defined by the named ranges.  In this case, I've called them
X_Values & Y_Values.
>
> Since the chart refers to the named ranges, it's guaranteed to include all
the data points.
>
> The macros are simple.  On opening, the path where the data file resides
is fixed (Connection string).  Next, the Query to get the
> data is refreshed (chart updates automatically).  Finally, a curious item
in Excel, the OnTime method, is used.  This lets you
> program a Date & Time for when a macro will run.  In this case, we want to
re-run this macro in one seconds time.  Upon closing,
> this timer is disabled.
>
> Note that the query can be set to refresh itself, but the minimum interval
is 1 minute.  By using that, you don't need the macros.
> (Except perhaps to set the Connection string for the Query.)
>
> I'm sure this is completely different to what you envisaged!  :-)
>
> Tony
>
>
> > {Original Message removed}

2005\07\10@120013 by Tony Smith

picon face
> > Hi John,
> >
> > You won't find any info on DDE in recent books, that's fairly old
> > stuff.  I had a look thru my book collection, a book on VB4 gave it an
> > entire paragraph.  Another book, "Developing Applications with MS
> > Office", (published in 1995 and referring to Office V4) has a short
> > chapter, amusing called "DDE: It's not dead yet!".
>
> At least it is hiding successfully. I have tried to use DDE a few times
> (from tcl scripts on win32) with the result that it works in test
> instances but I cannot ever be sure whether the DDE 'resource name' they
> made up for some application will be the same one I made up.
>
> Peter


DDE hasn't gone away, but it's been dead for quite a while.  DDE always sucked anyway, I have not-so-fond memories of UAE's from
DDE.  Slow, buggy, unreliably and only handled text.  Those were the days!

Tony

(not that things have improved much, it seems at times!)

2005\07\10@121657 by Tony Smith

picon face
Sure, you just use a formula.  This is known as linking.  If you are in cell C3, and you enter =A1, whatever is in A1 will show up
in both cells.

You can access cells on other sheets, so =Sheet2!A1 will copy the value stored in cell A1 on Sheet2 to wherever you entered the
formula.  You can access cells in other workbooks this way too.

It possible to do it in VBA as well.  When a cell is edited, a Change event fires, you can add code there.  Something like:

       ActiveSheet.Range("A1").Copy Range("C3")

This copies the value stored in cell A1 to C3.  It's preferable to avoid VBA if you can, although a little VBA is often required to
get precisely what you want (like the 1 second chart refresh).

Tony


> {Original Message removed}

2005\07\10@144837 by olin piclist

face picon face
John Pearson wrote:
> Is there a real simple way to get Excel to automatically copy the
> contents of a cell to another cell every time the first cell is
> updated or changed.

How about make the second cell a summation that includes only the first
cell?


*****************************************************************
Embed Inc, embedded system specialists in Littleton Massachusetts
(978) 742-9014, http://www.embedinc.com

2005\07\11@003011 by Jose Da Silva

flavicon
face
On July 10, 2005 11:48 am, Olin Lathrop wrote:
> John Pearson wrote:
> > Is there a real simple way to get Excel to automatically copy the
> > contents of a cell to another cell every time the first cell is
> > updated or changed.
>
> How about make the second cell a summation that includes only the
> first cell?

Let us suppose, the cell is A5 and you want to copy it to cell H2.
In cell H2 enter in the formula
=A5

Olin's suggestion works as well, but fills H2 with the formula
=SUM(A5:A5)

if you pick up a book at the library for LOTUS 123 or QUATTRO, or some
other older spreadsheet, they tend to show these old-style formulas.
The old formulas are a bonus since they are transferable to competing
spreadsheets while VBA is not transferable beyond Excell.

2005\07\11@091159 by Josh Koffman

face picon face
John Pearson wrote:
> Is there a real simple way to get Excel to automatically copy the
> contents of a cell to another cell every time the first cell is
> updated or changed.


I'm not an Excel Guru, but what about copying the first one, then
doing Paste Special -> Paste Link. I think that should work.

Josh

--
A common mistake that people make when trying to design something
completely foolproof is to underestimate the ingenuity of complete
fools.
       -Douglas Adams

2005\07\11@094208 by Anand Dhuru

flavicon
face

----- Original Message -----
From: "Josh Koffman" <EraseMEjoshybearspam_OUTspamTakeThisOuTgmail.com>
To: "Microcontroller discussion list - Public." <piclistspamspam_OUTmit.edu>
Sent: Monday, July 11, 2005 6:41 PM
Subject: Re: [OT] Book recommendations for VBA


> John Pearson wrote:
>> Is there a real simple way to get Excel to automatically copy the
>> contents of a cell to another cell every time the first cell is
>> updated or changed.
>
>

Suppose your first cell is A2, and your target cell (the one to be updated
automatically) is B5;

In B5, enter "=A2" (minus the quotes, of course)

That should do it.

Regards,

Anand

2005\07\12@063259 by Alan B. Pearce

face picon face
>On Mon, 4 Jul 2005, John Pearson wrote:
>> I need to do some macros in Excel. Am I correct in assuming that I
>> should learn VBA to do this?
>
>Yes, if you have any recent version of Excel

Maybe you need to learn VBA, but the problem I find is that the help files
are almost useless, and finding the functions names and definitions is an
exercise in futility. What I tend to do is turn on the macro recorder, then
carry out the operation I want, turn off the macro recorder, and then look
at the VBA it recorded. Modify as I feel fit and try it.

>"real time" I've never even considered doing with Excel/VBA.

Have a spreadsheet written by a colleague, where LabView writes to the
spreadsheet data that is obtained in the process of calibration. The graph
is updated in real time, so I can set the calibration process running, and
switch to the graph, and watch it change. This spreadsheet uses no VBA, but
there is no reason that using VBA to update values would not allow the same
to happen.

>Sometimes the microsoft.public.excel.programming newsgroup can
>be of assistance, either searching old postings or when that
>doesn't work asking new questions.

This was where I went to learn most of my Excel VBA snippets. The MVP folk
there were most helpful. Here is a list of credits that I put in one of my
spreadsheets, after getting help from them.


     Steve Bullen http://www.bmsltd.co.uk/Excel/
     Dave McRitchie http://www.mvps.org/dmcritchie/excel/excel.htm
     John Walkenbach  http://www.j-walk.com/ss/excel/index.htm
     Chip Pearson http://www.cpearson.com/
     Tushar Mehta http://www.tushar-mehta.com/
     Microsoft MVP's http://www.mvps.org/
     Woody's Watch http://woodyswatch.com/

2005\07\12@091249 by Tony Smith

picon face

> Maybe you need to learn VBA, but the problem I find is that the help files
> are almost useless, and finding the functions names and definitions is an
> exercise in futility. What I tend to do is turn on the macro recorder,
> then
> carry out the operation I want, turn off the macro recorder, and then look
> at the VBA it recorded. Modify as I feel fit and try it.
>
>>"real time" I've never even considered doing with Excel/VBA.
>
> Have a spreadsheet written by a colleague, where LabView writes to the
> spreadsheet data that is obtained in the process of calibration. The graph
> is updated in real time, so I can set the calibration process running, and
> switch to the graph, and watch it change. This spreadsheet uses no VBA,
> but
> there is no reason that using VBA to update values would not allow the
> same
> to happen.


Real time update with no VBA?  How's does that work?  Labview opens &
controls the spreadsheet?

MS help has been getting steadily worse over the years, although the MSDN
website occasionally has useful things.  Because XL has such a big object
model, using the macro recorder is often the only way you can figure out
what's going on.  The code generated tends to be a bit horrible, but it
points you in the right direction.  eg, Who would think that comments are
just a type of shape?

I wish Outlook had a macro recorder.

Tony

2005\07\12@094424 by Alan B. Pearce

face picon face
>Real time update with no VBA?  How's does that work?
>Labview opens & controls the spreadsheet?

Yes, I guess it uses OLE, or whatever the current name is. It is a module
that is inherent in the LabView install that allows access to Excel.

More... (looser matching)
- Last day of these posts
- In 2005 , 2006 only
- Today
- New search...