Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #31  
Old 02-22-2020, 10:23 PM
macropod's Avatar
macropod macropod is offline Macro to highlight alternating sections of a table Windows 7 64bit Macro to highlight alternating sections of a table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

As coded, the macro requires the heading row parameter to be present, to make it optional, it needs to be the last parameter:
Parms: Col=3, RGB=215,199,244, Hdrs=0


The macro can then be recoded as:
Code:
Sub TblHiLite()
Application.ScreenUpdating = False
Dim Hdr As Long, S As Long, W As Long, C As Long, R As Long, H As Long, i As Long
Dim StrTitle As String, StrParms As String, StrRGB As String
'Abort if the cursor is not in a table
If Selection.Information(wdWithInTable) = False Then
  MsgBox "The selection is not in a table!", vbOKOnly, "TblHiLite"
  GoTo ErrExit
End If
'Get Parameters
On Error GoTo ErrExit
StrParms = Split(Selection.Tables(1).Range.Previous.Paragraphs.Last.Range.Text, vbCr)(0)
For i = 0 To UBound(Split(StrParms, "="))
  Select Case i
    i = 0: C = Trim(Split(Split(StrParms, "=")(1), ",")(0))
    i = 1: StrRGB = Trim(Split(Split(StrParms, "=")(2), ",")(0))
    i = 2: Hdr = Trim(Split(StrParms, "=")(3))
  End Select
Next
S = RGB(Split(StrRGB, ",")(0), Split(StrRGB, ",")(1), Split(StrRGB, ",")(2))
On Error GoTo 0
W = RGB(255, 255, 255)
'process the table
With Selection.Tables(1)
  'Validate the column count
  If C > .Columns.Count Then
    MsgBox "There is no column " & C & " in the table!", vbOKOnly, "TblHiLite"
    End
  End If
  'Validate the heading row count
  If Hdr = 0 Then
    For i = 1 To .Rows.Count
      If .Rows(i).HeadingFormat = True Then
        Hdr = Hdr + 1
      Else
        Exit For
      End If
    Next
  ElseIf Hdr > .Rows.Count Then
    MsgBox "There is no row " & Hdr & " in the table!", vbOKOnly, "TblHiLite"
    End
  End If
  StrTitle = Split(.Cell(1 + Hdr, C).Range.Text, vbCr)(0)
  .Rows(1 + Hdr).Shading.BackgroundPatternColor = W: H = W
  For R = 2 + Hdr To .Rows.Count
    If Split(.Cell(R, C).Range.Text, vbCr)(0) <> StrTitle Then
      If H = W Then H = S Else H = W
      StrTitle = Split(.Cell(R, C).Range.Text, vbCr)(0)
    End If
    .Rows(R).Shading.BackgroundPatternColor = H
  Next
End With
Application.ScreenUpdating = True
End
ErrExit:
MsgBox "Invalid Parameter", vbExclamation
End Sub
Now, if the 'Hdrs=#' parameter is missing or set to 0, the code will work it out for itself.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #32  
Old 02-23-2020, 12:28 AM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Macro to highlight alternating sections of a table Windows XP Macro to highlight alternating sections of a table Office 2007
Competent Performer
Macro to highlight alternating sections of a table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
As coded, the macro requires the heading row parameter to be present, to make it optional, it needs to be the last parameter:
Parms: Col=3, RGB=215,199,244, Hdrs=0
I'm not sure which code you are referencing. In the code I am working on, the keyword parameters can be written in any order. That's the whole purpose of keyword parameters, no?. If they must be in a specific order, there is little need for the keywords. They can just be coded as values:

Parms: 3, 215, 199, 244, 0

And in my code, all of them have defaults, so none of them are required. I loop through them one by one using a Case statement to determine which keyword it is and then process the value. I have actually come up with a couple more parameters as well, all of which can be coded in any order.

Code:
ParmStr = Trim(Selection.Tables(1).Range.Previous.Paragraphs.last.Range.Text)
ParmStr = Left(ParmStr, Len(ParmStr) - 1)
Tokens = Split(ParmStr, " ")
If UBound(Tokens) < 1 Then GoTo NoInDocParms
If UCase(Tokens(0)) <> ParmKW Then GoTo NoInDocParms
For i = 1 To UBound(Tokens)
  If Tokens(i) = "" Then
    GoTo Continue_i: End If
  KwVal = Split(UCase(Tokens(i)), "=")
  If UBound(KwVal) <> 1 Then
    msg = "Invalid keyword parameter (" & Tokens(i) & ")"
    MsgBox msg, vbOKOnly, MyName: GoTo ErrExit: End If
  Select Case Trim(KwVal(0))
    Case "COLUMN"
      ... process the Column value
    Case "Headers"                  'If it's a Headers parameter,
      ... process the Headers value
    Case "RGB1"
      ... process the RGB1 values
    Case "RGB2"
      ... process the RGB2 values
    Case "MATCHCASE"
      ... process the MatchCase value
    Case Else
      msg = "Invalid keyword parameter (" & Tokens(i) & ")"
      MsgBox msg, vbOKOnly, MyName: GoTo ErrExit
  End Select
Continue_i:
Next i
This is working as far as I have gotten. I'll post my complete code when it's ready.

Quote:
The macro can then be recoded as:
Code:
 . . .
For i = 0 To UBound(Split(StrParms, "="))
   Select Case i
     i = 0: C = Trim(Split(Split(StrParms, "=")(1), ",")(0))
     i = 1: StrRGB = Trim(Split(Split(StrParms, "=")(2), ",")(0))
     i = 2: Hdr = Trim(Split(StrParms, "=")(3))
   End Select
Next
 . . .
That's an interesting and clever way to do that. At first it looked odd to me, but I see that it avoids having to test for i > UBound at each step and then having a label to Go To or messy nested If statements.

But I've never seen a Case statement coded like that. It's always:
Code:
   Select Case i
      Case 0: C = Trim(Split(Split(StrParms, "=")(1), ",")(0))
      Case 1: StrRGB = Trim(Split(Split(StrParms, "=")(2), ",")(0))
      Case 2: Hdr = Trim(Split(StrParms, "=")(3))
   End Select
Reply With Quote
  #33  
Old 02-23-2020, 02:48 AM
macropod's Avatar
macropod macropod is offline Macro to highlight alternating sections of a table Windows 7 64bit Macro to highlight alternating sections of a table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Jennifer Murphy View Post
I'm not sure which code you are referencing.
The code I supplied in post #21.
Quote:
Originally Posted by Jennifer Murphy View Post
In the code I am working on, the keyword parameters can be written in any order. That's the whole purpose of keyword parameters, no?. If they must be in a specific order, there is little need for the keywords. They can just be coded as values:

Parms: 3, 215, 199, 244, 0
That code was based on your original specification:
Quote:
Originally Posted by Jennifer Murphy View Post
Parms: Col=3, Hdrs=0, RGB=215,199,244
and requires each parameter to be preceded by '=' and, except for the last, to be followed by ','. The order in which they occur is critical - regardless of whether you supply the keywords (which aren't actually necessary for the parsing). For example, the code cannot possibly know which is the column and the row if you use:
3, 1, 215, 199, 244
or:
1, 3, 215, 199, 244
unless you stick to a specified order. Yes, you could just pass a string of numbers separated by whatever character you choose (e.g. just a space or a comma) but, even so, the order would remain critical.
Quote:
Originally Posted by Jennifer Murphy View Post
And in my code, all of them have defaults, so none of them are required. I loop through them one by one using a Case statement to determine which keyword it is and then process the value.
It seems you're using something different from what we've discussed. It's a bit hard to develop code if you're shifting the goal posts like that.
Quote:
Originally Posted by Jennifer Murphy View Post
That's an interesting and clever way to do that. At first it looked odd to me, but I see that it avoids having to test for i > UBound at each step and then having a label to Go To or messy nested If statements.

But I've never seen a Case statement coded like that. It's always:
Code:
   Select Case i
      Case 0: C = Trim(Split(Split(StrParms, "=")(1), ",")(0))
      Case 1: StrRGB = Trim(Split(Split(StrParms, "=")(2), ",")(0))
      Case 2: Hdr = Trim(Split(StrParms, "=")(3))
   End Select
It can be coded either way. The only reason for having the loop & select case statement, though, is to cater for:
Quote:
Originally Posted by Jennifer Murphy View Post
I think I'll include the Headers=n parameter. If it's present, I'll use it. If not, I'll check for the Repeat option and use that. If neither are present, I'll assume no headers and start highlighting with row 1
which means one parameter has become optional and, so, a way is needed to test for its presence.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #34  
Old 02-23-2020, 10:26 AM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Macro to highlight alternating sections of a table Windows XP Macro to highlight alternating sections of a table Office 2007
Competent Performer
Macro to highlight alternating sections of a table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
The code I supplied in post #21.

That code was based on your original specification:

and requires each parameter to be preceded by '=' and, except for the last, to be followed by ','. The order in which they occur is critical - regardless of whether you supply the keywords (which aren't actually necessary for the parsing). For example, the code cannot possibly know which is the column and the row if you use:
3, 1, 215, 199, 244
or:
1, 3, 215, 199, 244
unless you stick to a specified order. Yes, you could just pass a string of numbers separated by whatever character you choose (e.g. just a space or a comma) but, even so, the order would remain critical.

It seems you're using something different from what we've discussed. It's a bit hard to develop code if you're shifting the goal posts like that.
Perhaps I was not as clear as I could have been. Sorry about that. Written discussions are fraught.

Once you showed me how to have a parameter string in the document, it was always my intention to have keyword parameters that could be coded in any order and for all of them to have default values in case no parameters at all are passed.

The general syntax is something like this:

Code:
TblHiLite: [Column=C|1] [Headers=H|1] [RGB1=R,G,B|255,255,255]
           [RGB2=R,G,B|219,229,241] [MatchCase=Yes/No|Yes]
My code handles that by breaking the entire string into tokens delimited by spaces. It then breaks each token into a keyword and value delimited by "=". If the keyword matches one of the defined ones, the value is used.

The order doesn't matter and none of them are required. This is a very flexible syntax and easy for the user (me) to use.
Reply With Quote
  #35  
Old 02-23-2020, 10:33 AM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Macro to highlight alternating sections of a table Windows XP Macro to highlight alternating sections of a table Office 2007
Competent Performer
Macro to highlight alternating sections of a table
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

I just realized what this statement of your really does:

Code:
Split(.Cell(Row, Column).Range.Text, vbCr)(0)
I tried reading the contents of a cell using this code:

Code:
.Cell(Row, Column).Range.Text
I was surprised to discover that there are two extra characters at the end of the string: x'13' and x'07'. The first is a carriage return, which I was a little surprised to find in a cell, but then I remembered that that's where Word stores all the paragraph properties. The second one, according to an ASCII table, is a Bell character. What the heck is that for?

In any case, your code neatly captures just the text.

I assume that there is no way to retrieve just the (visible) text, right?
Reply With Quote
  #36  
Old 02-23-2020, 02:53 PM
macropod's Avatar
macropod macropod is offline Macro to highlight alternating sections of a table Windows 7 64bit Macro to highlight alternating sections of a table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Table cells and rows always end with a Chr(13) & Chr(07) pair. At a guess, I'd say the Chr(07) is a replacement for the Chr(10) that is paired with Chr(13) to form a normal paragraph break.
Quote:
Originally Posted by Jennifer Murphy View Post
I assume that there is no way to retrieve just the (visible) text, right?
No, the code I used shows one way. Do be aware that, even with an ordinary paragraph, .Range.Text includes the paragraph break. In a multi-paragraph cell, or if you're wanting to capture the text and its formatting, different code would be required to capture all paragraphs and omit the end-of-cell marker (i.e. the Chr(13) & Chr(07) pair).
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to highlight alternating sections of a table Macro to highlight a list of words bakerkr Word VBA 4 10-19-2017 02:23 PM
Macro to highlight alternating sections of a table Word 2010 VBA Print Macro - Specified Sections Benbon Word VBA 3 03-30-2017 02:31 PM
Macro to highlight alternating sections of a table Macro Question: Need help making a macro to highlight the first word in every sentence LadyAna Word 1 12-06-2014 10:39 PM
Macro to highlight alternating sections of a table Macro to highlight words bertietheblue Word VBA 9 07-01-2013 12:39 PM
find - reading highlight - highlight all / highlight doesn't stick when saved bobk544 Word 3 04-15-2009 03:31 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:29 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft