"Simple" Calculation

[JinkyJulie]

Hello...

I am new to Word VBA... I have successfully created a macro to do simple
things to my document. I wish to extend the macro if possible...

I have tables of varying lengths (rows)... I wish to calculate the
difference (the figures represent time) between the last time (last row,
first column) and the SECOND (third row, first column) time in the table...
store the result and then "paste" the result in a specific spot (last row,
second column) in another table existing directly below this one in the
document...

Is this possible? Am I clear in my explanation?

Any help or direction would be most appreciated... just a push even...

Thank you all in advance...

Julie

 

[Jay Freedman]

Certainly it's possible, but let me clarify a point: Do you want the macro
to repeat this action throughout all the tables in the document? In other
words, if you have thee tables, then the difference from the first table is
stored into the second table, and the difference from the second table is
stored into the third table? And what do you want to do with the difference
from the third table? Or do the differences from all the existing tables get
placed into one special table?

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[JinkyJulie]

Jay... Thanks for getting back to me...

All of the tables are grouped into separate sections... So, the data I wish
to calculate with is in the first table and the location for the result is in
the next... Subsequent tables are grouped in the same manner... unrelated
(directly)

I am looking for a push... a little direction... with that I could adapt
the code to the existing macro... I am just so new at Word VBA that I am not
sure of where to start...

Julie...

 

[Jay Freedman]

Hmmm. The choice of words is extremely important here. When you say
"grouped in separate sections", do you mean there are *section breaks*
between the groups, or just that you think of them logically as different
groups? A VBA macro can easily deal with actual sections separated by
section breaks, but it doesn't understand "this group over here" if there's
nothing special in the document to distinguish it from "that group over
there".

An arrangement that could be understandable to the macro is if the tables
are just in pairs... Table 1 contains the times for one group, and Table 2
gets the difference from Table 1; then Table 3 contains the times for the
second group, and Table 4 gets the difference from Table 3; and so on. Is
that your setup?

What I'm trying to nail down is the answer to the question "How does the
macro 'know' which table contains the data and which table gets the result,
for each group?"

Calculating the time differences is relatively trivial -- there's a DateDiff
function in VBA that can do it in one step -- but it needs to be given the
right input, and the right place to put the output. That's the harder part.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[JinkyJulie]

" An arrangement that could be understandable to the macro is if the tables
are just in pairs... Table 1 contains the times for one group, and Table 2
gets the difference from Table 1; then Table 3 contains the times for the
second group, and Table 4 gets the difference from Table 3; and so on. Is
that your setup?"

Jay,

Yes it is... no "physical" breaks... It is one long document consisting of
the various tables...

My overall objective is to condense a previously generated Word document from
approx 20 pages of information down to a quick read of one or two pages...
(and without colored borders and other superfluous "stuff" ) I have already
made great strides in doing that... with the existing (albeit amateur) macro,
I have cut my workload in half... however, extending it a few more steps
will help me that much further... currently I copy (manually) the column of
time entries into Excel and have it do the calculations and then type the
result in the table... then delete the table from the Word doc...

If it would be easier, consider the tables as stand alones... two tables in
a document... subtract one time entry (third row, first column of the first
table (the first is the header row)) from the last entry (last row, first
column) and send the result to the last cell (last row, second column) of the
next table. That would be OK..

I do appreciate your time...

Julie

 

[fumei via OfficeKB.com]

I agree with Jay that we need more specific details on how your document is
structured. "Section" is a specific term in Word, so if you are dealing with
real Section (as Jay points out, those that are separated by Section breaks),
then that is one thing. If they are "sections" that are only in your mind,
then Word does not know that.

As one possible solution, if these are "groups" of two tables - that is
grouped in your mind - then you could bookmark each group of two, and work
with the bookmarks. It is always better if you can work with objects, which
the following code does.

So say you have:

Table_1 and Table_2 are related for your calculation.
Table_3 and Table_4 are related for your calculation.
Table_5 and Table_6 are related for your calculation.

Let’s just use Table_1 and Table_2 for this example.

Bookmark Table_1 and Table_2 and name it, say, "TableGroup1".

Sub yadda()
Dim oTableSource As Table
Dim oTableResult As Table
Dim oCell As Cell
Dim j As Long
Dim k As Long

‘ this makes Table_1 a table object
Set oTableSource = ActiveDocument.Bookmarks("GroupTable1") _
.Range.Tables(1)

‘ this makes Table_2 a table object
Set oTableResult = ActiveDocument.Bookmarks("GroupTable1") _
.Range.Tables(2)

' get the number of cells in oTableResult
' IF the table only has two columns then
' this will work for the requirement
' "last row, second column"
j = oTableResult.Range.Cells.Count

' this sets the Cell object to be the LAST cell
' of oTableresult
Set oCell = oTableResult.Range.Cells(j)

' this gets the row count for oTableSource
k = oTableSource.Rows.Count

' this make the cell object,
' the LAST cell in oTableResult,
' text equal the DateDiff between
' oTableSource.Cell(last_row, column 1)
' and oTableSource.Cell(row 2, col 1)
oCell.Range.Text = _
DateDiff("n", _
oTableSource.Cell(k, 1).Range.Fields(1).Result, _
oTableSource.Cell(2, 1).Range.Fields(1).Result)

End Sub

This assumes that the dates in the table you are using are FIELD dates. You
do not state this. If you are doing something different, then you have to
say so. There could be format requirements. See how to use Format in Help.

It also makes the DateDiff result in minutes – the “n†in DateDiff. Again,
you do not state your requirements. Look up DateDiff in Help and you can see
what intervals are available.

The code can be easily adjusted if the cell I am pointing to is not the
correct one.

The beauty of using objects is that it does not matter if the tables are of
varying numbers of rows. You can get tableObject.Rows.Count to get the last
row, no matter what it is.

 

[Jay Freedman]

fumei's macro is about what I had in mind. Here's another version that
doesn't need any bookmarks, and has a bit of error-checking (although more
of it could be done).

Sub StoreTimes()
Dim tSrc As Table, tDest As Table
Dim tblNum As Long
Dim rgSrc1 As Range, rgSrc2 As Range, rgDest As Range

' safety check: are there any tables?
If ActiveDocument.Tables.Count = 0 Then
MsgBox "This document doesn't contain any tables.", _
, "Error"
Exit Sub
End If

' safety check: is there an even number of tables?
If ActiveDocument.Tables.Count Mod 2 = 1 Then
MsgBox "This document contains an odd number of tables.", _
, "Error"
Exit Sub
End If

For tblNum = 1 To ActiveDocument.Tables.Count - 1 Step 2
Set tSrc = ActiveDocument.Tables(tblNum)
Set tDest = ActiveDocument.Tables(tblNum + 1)

On Error GoTo SkipTable

' pick out the cells to use
Set rgSrc1 = tSrc.Cell(2, 2).Range
Set rgSrc2 = tSrc.Cell(tSrc.Rows.Count, 2).Range
Set rgDest = tDest.Cell(tDest.Rows.Count, 2).Range
' exclude cell markers
rgSrc1.MoveEnd wdCharacter, -1
rgSrc2.MoveEnd wdCharacter, -1
rgDest.MoveEnd wdCharacter, -1

rgDest.Text = DateDiff("n", _
CDate(rgSrc1.Text), CDate(rgSrc2.Text))

GoTo ResumeLoop

SkipTable:
MsgBox "Problem in table " & tblNum & "; skipped it." _
& vbCr & Err.Description, , "Error"

ResumeLoop:
Next tblNum

End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[JinkyJulie via OfficeKB.com]

Jay, fumei,

You guys are awesome!!! Give me a chance to try them out and adapt them to
my existing macro and I will let you know....

Thanks so much for your time and effort!!!

Julie

 

[JinkyJulie via OfficeKB.com]

Hi again...

Tried to run both subs... same problem... file not found: vba6.dll (running
XP, Office 2003 (all updates current)) Cannot seem to locate cause... dll
is in right places...

Suggestions?!?!?!

Thanks again for all your help...

Julie

 

[Jay Freedman]

Hi again...

Tried to run both subs... same problem... file not found: vba6.dll
(running XP, Office 2003 (all updates current)) Cannot seem to
locate cause... dll is in right places...

Suggestions?!?!?!

Thanks again for all your help...

Julie

Are you sure the message says "vba6" and not "vbe6"? Also, does this happen
with all macros, or is it just this code that's causing the problem?

In the VBA editor, click Tools > References and click on "Visual Basic for
Applications", which should have a check next to it. Look in the area at the
bottom, which should show the dll that corresponds (geez, I wish they'd make
that box wide enough to show the whole path...). The reference should be to
the file

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL

If it points somewhere else, or you're not sure, click the Browse button and
select that file.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[JinkyJulie via OfficeKB.com]

Found the VBA6.dll problem... fixed... Jay's sub running... stopping at...

rgDest.Text = DateDiff("n", _
CDate(rgSrc1.Text), CDate(rgSrc2.Text))

with a Type Mismatch error...

I understand what the error is but cannot figure out why...

I'll keep trying... any assistance would be grand...

Thanks again...

Julie

 

[John... Visio MVP]

So what is in rgSrc1.Text and rgSrc2.Text? You may find one or the other
does not convert to date.

For a test insert
Debug.print "[";rgSrc1.Text;"] [";rgSrc2.Text;"]"
Debug.print CDate(rgSrc1.Text)
Debug.print CDate(rgSrc2.Text))

and see what show up in the immediate window

John... Visio MVP

 

[Jay Freedman]

When I wrote the macro, I tested a couple of deliberate error conditions to
see what it would do. I did see a Type Mismatch error when either of the
source cells was empty (that is, the CDate function was trying to convert an
empty string to a date).

Another debugging step you can try is to put the lines

rgSrc1.Select
rgSrc2.Select

into the macro just before the DateDiff line, and run the macro from the VBA
editor by pressing the F8 key repeatedly -- each press executes one line,
and you can look at the document after executing each of these two lines to
see which cell got selected. Maybe the macro is pointing to the wrong
cell(s).

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

So what is in rgSrc1.Text and rgSrc2.Text? You may find one or the
other does not convert to date.

For a test insert
Debug.print "[";rgSrc1.Text;"] [";rgSrc2.Text;"]"
Debug.print CDate(rgSrc1.Text)
Debug.print CDate(rgSrc2.Text))

and see what show up in the immediate window

John... Visio MVP

Found the VBA6.dll problem... fixed... Jay's sub running... stopping
at...

rgDest.Text = DateDiff("n", _
CDate(rgSrc1.Text), CDate(rgSrc2.Text))

with a Type Mismatch error...

I understand what the error is but cannot figure out why...

I'll keep trying... any assistance would be grand...

Thanks again...

Julie

 

[JinkyJulie via OfficeKB.com]

Guys, you rule!!! I have it working...

With the debugging code and a little tweaking, it works great...

One "small" thing more.... (thanks for being so patient...)

The result is in minutes only... How could I get h:mm? Is this easily done??


Some sort of fancy calculation I would assume... I am looking at the WWW for
answers, but can only find VBScript answers.... That's why I turn to you....

Thanks again, ever so much...

Julie

 

[Jay Freedman]

Great, I'm glad to hear it's working for you.

To get h:mm, first insert this line at the top with the other Dim
statements:

Dim nHr As Long, nMin As Long

Then replace the rgDest.Text = DateDiff line with this section of code:

nHr = DateDiff("h", CDate(rgSrc1.Text), CDate(rgSrc2.Text))
nMin = DateDiff("n", CDate(rgSrc1.Text), CDate(rgSrc2.Text))
rgDest.Text = Format(nHr, "0") & ":" & Format(nMin Mod 60, "00")

This gets the difference expressed first as a number of hours, and then the
same difference expressed as a number of minutes. The expression nMin Mod 60
is the remainder when the number of minutes is divided by 60, so that's the
"mm" part of "h:mm". The Format functions make the numbers display the
proper digits.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[John... Visio MVP]

Jay: What about

nMin = DateDiff("n", CDate(rgSrc1.Text), CDate(rgSrc2.Text))/1440
rgDest.Text = Format(nMin,"hh:mm")

John... Visio MVP

 

[Jay Freedman]

Well, almost. It needs two tweaks:

- DateDiff returns a Variant containing a Long value, so the expression you
wrote does integer math. In particular, if the difference in the times is
less than one day, nMin would be zero. To fix that, you have to declare nMin
as a Single or Double (floating-point) variable, and you need to make the
divisor a floating-point constant by writing it as 1440# (which VBA will
substitute if you write it as 1440.0). That forces the division to be done
as floating-point.

- The format string needs to be "h:Nn" instead of "hh:mm". That's shown in
the help topic "User-Defined Date/Time Formats (Format Function)".

I think the original code is clearer, and I doubt that the difference in
execution speed is measurable. You do get points for elegance, though.