Hello everyone,
I am trying to create a macro to highlight the differences in 2
worksheets within the same workbook. I would like to highlight the
differences and show them in Sheet1. I would also like to display the
changes in Sheet3(Activity changes) Sheet1 name is "This Weeks POR"
and Sheet2 name is "Last Weeks POR" Sheet3 name is "Activity Changes".
Here is a link to the file in question -
https://docs.google.com/uc?id=0B8VP5...nload&hl=en_US
Thanks in advance for your help. Any response is appreciated and if
you can show as much how you came up with logical solution would help.
I am a beginner and am taking the learn as I go approach.
Josh

On Aug 13, 3:45*pm, Joshua Houck wrote:
Hello everyone,
I am trying to create a macro to highlight the differences in 2
worksheets within the same workbook. I would like to highlight the
differences and show them in Sheet1. I would also like to display the
changes in Sheet3(Activity changes) Sheet1 name is "This Weeks POR"
and Sheet2 name is "Last Weeks POR" Sheet3 name is "Activity Changes".
Here is a link to the file in question -https://docs.google.com/uc?id=0B8VP5dFKXt9hODA2OWNmM2ItMDQ5OC00MTAyLW...
Thanks in advance for your help. Any response is appreciated and if
you can show as much how you came up with logical solution would help.
I am a beginner and am taking the learn as I go approach.
Josh

It looks like this code was able to highlight the information i needed
in Sheet1, but now I need to put that highlighted data in sheet 3.
Sub comparesheets()
For Each cl In Sheets("sheet2").UsedRange
If cl.Value < Sheets("Sheet1").Cells(cl.Row, cl.Column) Then
cl.Interior.Color = RGB(0, 0, 255)
End If
Next cl
End Sub

I think I have figured out how to highlight the differences in This
Weeks POR and Last Weeks POR using this code-
Sub comparesheets()
For Each cl In Sheets("This Weeks POR").UsedRange
If cl.Value < Sheets("Last Weeks POR").Cells(cl.Row,
cl.Column) Then
cl.Interior.Color = RGB(0, 0, 255)
End If
Next cl
End Sub
Now I just have to figure out how to take the highlighted data and
format them sheet 3(activity changes)

On Aug 13, 6:28*pm, Joshua Houck wrote:
I think I have figured out how to highlight the differences in This
Weeks POR and Last Weeks POR using this code-
Sub comparesheets()
* * For Each cl In Sheets("This Weeks POR").UsedRange
* * * * If cl.Value < Sheets("Last Weeks POR").Cells(cl.Row,
cl.Column) Then
* * * * * * cl.Interior.Color = RGB(0, 0, 255)
* * * * End If
* * Next cl
End Sub
Now I just have to figure out how to take the highlighted data and
format them sheet 3(activity changes)

You didn't mention what you want to copy & your link didn't work. Try
If cl.Value < Sheets("Last Weeks POR").Cells(cl.Row, cl.Column)
Then
cl.Interior.Color = RGB(0, 0, 255)
cl.entirerow.copy sheets("sheet 3").cells(rows.count,
1).end(xlup).offset(1)
end if

On Aug 13, 4:46*pm, Don Guillett wrote:
On Aug 13, 6:28*pm, Joshua Houck wrote:

I think I have figured out how to highlight the differences in This
Weeks POR and Last Weeks POR using this code-
Sub comparesheets()
* * For Each cl In Sheets("This Weeks POR").UsedRange
* * * * If cl.Value < Sheets("Last Weeks POR").Cells(cl.Row,
cl.Column) Then
* * * * * * cl.Interior.Color = RGB(0, 0, 255)
* * * * End If
* * Next cl
End Sub
Now I just have to figure out how to take the highlighted data and
format them sheet 3(activity changes)

You didn't mention what you want to copy & your link didn't work. Try
* If cl.Value < Sheets("Last Weeks POR").Cells(cl.Row, cl.Column)
Then
* * * * * * *cl.Interior.Color = RGB(0, 0, 255)
cl.entirerow.copy sheets("sheet 3").cells(rows.count,
1).end(xlup).offset(1)
end if

Thanks for the response, I have fixed the link and I posted what I
have so far. This should give you a better idea what I'm looking to
do.
https://docs.google.com/leaf?id=0B8V...5ZmU1&hl=en_US

Download from... http://www.mediafire.com/?ytuty9hk5rts34q
It is a copy of your workbook with a comparison of the two sheets.
It was done using my XL Companion Excel add-in.
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)



"Joshua Houck"
wrote in message
...

Thanks for the response, I have fixed the link and I posted what I
have so far. This should give you a better idea what I'm looking to
do.
https://docs.google.com/leaf?id=0B8V...5ZmU1&hl=en_US

On Aug 13, 7:18*pm, "Jim Cone" wrote:
Download from...http://www.mediafire.com/?ytuty9hk5rts34q
It is a copy of your workbook with a comparison of the two sheets.
It was done using my XL Companion Excel add-in.
--
Jim Cone
Portland, Oregon USA .http://www.mediafire.com/PrimitiveSoftware.
(free and commercial excel programs)

"Joshua Houck"
wrote in ...

Thanks for the response, I have fixed the link and I posted what I
have so far. This should give you a better idea what I'm looking to
do.https://docs.google.com/leaf?id=0B8V...ItNThmMS00NDRj...

Thanks Jim,
I have been going over the logic to try and take the highlighted cell
differences and place them in the Activity Changes worksheet "new
value" column. But to take it one step further also move the column
headers of EventID, Entity Code, Life, CEID, and Activity associated
with each cells differences. I am not sure if I could write it in the
macro I already have, create a new macro, or use an add-in like you
used to compare to new worksheet, but with customized headers to
match the format I am trying to achieve. Like I stated earlier, I am a
newby, but trying to figure out as I go. Thanks for your interest.
Josh

Maybe...
Copy the two sheets.
Clear the contents below the column headers.
When cells don't agree, enter those values into the copied sheets.
You end up with the sheets just showing the problem cells/values.
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(XL Companion add-in: compares, matches, counts, lists, finds, deletes...)





"Joshua Houck"
wrote in message
...
On Aug 13, 7:18 pm, "Jim Cone" wrote:
Download from...http://www.mediafire.com/?ytuty9hk5rts34q
It is a copy of your workbook with a comparison of the two sheets.
It was done using my XL Companion Excel add-in.
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware.
(free and commercial excel programs)




"Joshua Houck"
wrote in ...

Thanks for the response, I have fixed the link and I posted what I
have so far. This should give you a better idea what I'm looking to
do.https://docs.google.com/leaf?id=0B8V...ItNThmMS00NDRj...

Thanks Jim,
I have been going over the logic to try and take the highlighted cell
differences and place them in the Activity Changes worksheet "new
value" column. But to take it one step further also move the column
headers of EventID, Entity Code, Life, CEID, and Activity associated
with each cells differences. I am not sure if I could write it in the
macro I already have, create a new macro, or use an add-in like you
used to compare to new worksheet, but with customized headers to
match the format I am trying to achieve. Like I stated earlier, I am a
newby, but trying to figure out as I go. Thanks for your interest.
Josh

In message of Sat, 13 Aug 2011 19:18:33 in
microsoft.public.excel.programming, Jim Cone
writes

Download from... http://www.mediafire.com/?ytuty9hk5rts34q
It is a copy of your workbook with a comparison of the two sheets.
It was done using my XL Companion Excel add-in.

Jim,
Please blow your trumpet. I see you offer a 3 week trial.
What does the software do? I downloaded XLCompanion.zip and had a look.
What does a license cost? XL Companion Read Me.doc seems to be written
in a clever fashion so Ctrl-F does not work; nor does text selection. ;)

I have two known unsatisfied needs for Excel:

1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;

2) Deep comparison. I get workbooks from a company, but have no access
to any technical people.
The latest workbooks are flawed. (Text does not fit in a textbox.)
I want to compare a good textbox and a bad textbox at the VBA level.
If I can analyse the flaw, there is a small chance it will be fixed.
I downloaded XLCompanion.zip, read it only compares cells and infer it
does not fill my need. ;(

I continue to use Excel 2003.

I would value suggestions of products likely to support those needs!

P.S. I wrote a simple shape-dump routine (showing Left, Top, Height,
Width, TextFrame.Characters.Font(FontStyle, Name, Size) and
TextFrame.Characters), but it showed nothing.
TextFrame.Characters.Text limits itself to 255 characters.
This code seems to grab it all - only tested to 321!
With V.TextFrame
For I = 1 To .Characters.Count Step 255
S = S & .Characters(Start:=I).Text ' Text limit is 255
Next I
End With
It took me a little while to deduce that code after googling.
The hard thing was placing that "Start:=I".
Somebody may find the snippet useful. ;)
--
Walter Briscoe

Walter,

The XL Companion Read Me file (Word 2002) is protected, but requires no password to unprotect.
The protection is to prevent accidental changes to or repositioning of pictures.
The XL Companion program can do the following:

Compare ...
worksheets, cell by cell (any of 9 differences)
selections, cell by cell
each row to all rows
lists (will color any matches)

Count...
unique cells (or color them)
unique rows
unique words

Remove, color or clear...
Identical rows from a worksheet
(a row is defined by the columns the user selects)

Delete, color or clear...
rows that meet criteria the user specifies

Clean Data
3 intensity levels

Find
multiple items on multiple sheets

The program sells for $39.00
Download from: http://www.mediafire.com/PrimitiveSoftware
--
Jim Cone
Portland, Oregon USA
http://excelusergroup.org/media/
(Formats & Styles xl add-in: lists/removes unused styles & number formats)





"Walter Briscoe"
wrote in message
...
In message of Sat, 13 Aug 2011 19:18:33 in
microsoft.public.excel.programming, Jim Cone
writes

Download from... http://www.mediafire.com/?ytuty9hk5rts34q
It is a copy of your workbook with a comparison of the two sheets.
It was done using my XL Companion Excel add-in.

Jim,
Please blow your trumpet. I see you offer a 3 week trial.
What does the software do? I downloaded XLCompanion.zip and had a look.
What does a license cost? XL Companion Read Me.doc seems to be written
in a clever fashion so Ctrl-F does not work; nor does text selection. ;)

I have two known unsatisfied needs for Excel:

1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;

2) Deep comparison. I get workbooks from a company, but have no access
to any technical people.
The latest workbooks are flawed. (Text does not fit in a textbox.)
I want to compare a good textbox and a bad textbox at the VBA level.
If I can analyse the flaw, there is a small chance it will be fixed.
I downloaded XLCompanion.zip, read it only compares cells and infer it
does not fill my need. ;(

I continue to use Excel 2003.

I would value suggestions of products likely to support those needs!

P.S. I wrote a simple shape-dump routine (showing Left, Top, Height,
Width, TextFrame.Characters.Font(FontStyle, Name, Size) and
TextFrame.Characters), but it showed nothing.
TextFrame.Characters.Text limits itself to 255 characters.
This code seems to grab it all - only tested to 321!
With V.TextFrame
For I = 1 To .Characters.Count Step 255
S = S & .Characters(Start:=I).Text ' Text limit is 255
Next I
End With
It took me a little while to deduce that code after googling.
The hard thing was placing that "Start:=I".
Somebody may find the snippet useful. ;)
--
Walter Briscoe

Walter Briscoe pretended :
In message of Sat, 13 Aug 2011 19:18:33 in
microsoft.public.excel.programming, Jim Cone
writes

Download from... http://www.mediafire.com/?ytuty9hk5rts34q
It is a copy of your workbook with a comparison of the two sheets.
It was done using my XL Companion Excel add-in.

Jim,
Please blow your trumpet. I see you offer a 3 week trial.
What does the software do? I downloaded XLCompanion.zip and had a look.
What does a license cost? XL Companion Read Me.doc seems to be written
in a clever fashion so Ctrl-F does not work; nor does text selection. ;)

I have two known unsatisfied needs for Excel:

1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;

Can you elaborate more on this? How are the variables declared? (ie:
scope, type, udt...)

Have you looked at using VB's Put/Get functions?


2) Deep comparison. I get workbooks from a company, but have no access
to any technical people.
The latest workbooks are flawed. (Text does not fit in a textbox.)
I want to compare a good textbox and a bad textbox at the VBA level.
If I can analyse the flaw, there is a small chance it will be fixed.
I downloaded XLCompanion.zip, read it only compares cells and infer it
does not fill my need. ;(

I continue to use Excel 2003.

I would value suggestions of products likely to support those needs!

P.S. I wrote a simple shape-dump routine (showing Left, Top, Height,
Width, TextFrame.Characters.Font(FontStyle, Name, Size) and
TextFrame.Characters), but it showed nothing.
TextFrame.Characters.Text limits itself to 255 characters.
This code seems to grab it all - only tested to 321!
With V.TextFrame
For I = 1 To .Characters.Count Step 255
S = S & .Characters(Start:=I).Text ' Text limit is 255
Next I
End With
It took me a little while to deduce that code after googling.
The hard thing was placing that "Start:=I".
Somebody may find the snippet useful. ;)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Aug 14, 9:46*am, "Jim Cone" wrote:
Walter,

The XL Companion Read Me file (Word 2002) is protected, but requires no password to unprotect.
The protection is to prevent accidental changes to or repositioning of pictures.
The XL Companion program can do the following:

Compare ...
* worksheets, cell by cell (any of 9 differences)
* selections, cell by cell
* each row to all rows
* lists (will color any matches)

Count...
* unique cells (or color them)
* unique rows
* unique words

Remove, color or clear...
* Identical rows from a worksheet
* (a row is defined by the columns the user selects)

Delete, color or clear...
* rows that meet criteria the user specifies

Clean Data
* 3 intensity levels

Find
* multiple items on multiple sheets

The program sells for $39.00
Download from: *http://www.mediafire.com/PrimitiveSoftware
--
Jim Cone
Portland, Oregon USAhttp://excelusergroup.org/media/
(Formats & Styles xl add-in: *lists/removes unused styles & number formats)

"Walter Briscoe"
wrote in ...



In message of Sat, 13 Aug 2011 19:18:33 in
microsoft.public.excel.programming, Jim Cone
writes

Download from...http://www.mediafire.com/?ytuty9hk5rts34q
It is a copy of your workbook with a comparison of the two sheets.
It was done using my XL Companion Excel add-in.

Jim,
Please blow your trumpet. I see you offer a 3 week trial.
What does the software do? I downloaded XLCompanion.zip and had a look.
What does a license cost? XL Companion Read Me.doc seems to be written
in a clever fashion so Ctrl-F does not work; nor does text selection. ;)

I have two known unsatisfied needs for Excel:

1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;

2) Deep comparison. I get workbooks from a company, but have no access
to any technical people.
The latest workbooks are flawed. (Text does not fit in a textbox.)
I want to compare a good textbox and a bad textbox at the VBA level.
If I can analyse the flaw, there is a small chance it will be fixed.
I downloaded XLCompanion.zip, read it only compares cells and infer it
does not fill my need. ;(

I continue to use Excel 2003.

I would value suggestions of products likely to support those needs!

P.S. I wrote a simple shape-dump routine (showing Left, Top, Height,
Width, TextFrame.Characters.Font(FontStyle, Name, Size) and
TextFrame.Characters), but it showed nothing.
TextFrame.Characters.Text limits itself to 255 characters.
This code seems to grab it all - only tested to 321!
* *With V.TextFrame
* * * *For I = 1 To .Characters.Count Step 255
* * * * * *S = S & .Characters(Start:=I).Text *' Text limit is 255
* * * *Next I
* *End With
It took me a little while to deduce that code after googling.
The hard thing was placing that "Start:=I".
Somebody may find the snippet useful. ;)
--
Walter Briscoe- Hide quoted text -

- Show quoted text -

On the COMPARE question. Since you have an xlsm file you can copy both
sheets to a single sheet and simply use xl2007 or xl2010
dataduplicates..... I don't understand the red,blue,green logic.

In message of Sun, 14 Aug 2011 07:46:43 in
microsoft.public.excel.programming, Jim Cone
writes
Walter,

The XL Companion Read Me file (Word 2002) is protected, but requires no
password to unprotect.

Thank you. I have no experience with protected files.

The protection is to prevent accidental changes to or repositioning of
pictures.
The XL Companion program can do the following:

Compare ...
worksheets, cell by cell (any of 9 differences)

[snip]

Thank you for that useful list.
I might pay that 39USD if it compared shapes.
--
Walter Briscoe

In message of Sun, 14 Aug 2011 11:56:58 in
microsoft.public.excel.programming, GS writes
Walter Briscoe pretended :

[snip]

I have two known unsatisfied needs for Excel:

1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;

Can you elaborate more on this? How are the variables declared? (ie:
scope, type, udt...)

No! I think we are at cross purposes. I run VBA code and stop it.
It is usually in PERSONAL.XLS. When I stop, I want to examine variables.
If I have declared the variable, It appears in the Locals Window.
If it is a predefined variable, such as ActiveCell, it does not seem to
be viewable in a similar manner.
I maintain a module variable declared with Dim probe As Object. (It
would probably be better declared as Dim probe as Variant so it could
reference anything. I might use probe in the immediate window by running
set probe=activecell. I can then examine a copy of ActiveCell in
arbitrary detail.


Have you looked at using VB's Put/Get functions?

No! What are they? I think I have confused you into thinking I am using
native VB rather than VBA. Help on get and put are both empty.
I repeat. I want to grab text which copies information such as that
which appears in the View/Locals window.

e.g. I want to see something like
ActiveCell Range
AddIndent False Variant/Boolean
AllowEdit False Boolean
....

I gather that VBA code can't be used to get a list of the names in an
object referenced by a VBA variable.

[snip]
--
Walter Briscoe

Walter Briscoe presented the following explanation :
In message of Sun, 14 Aug 2011 11:56:58 in
microsoft.public.excel.programming, GS writes
Walter Briscoe pretended :

[snip]

I have two known unsatisfied needs for Excel:

1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;

Can you elaborate more on this? How are the variables declared? (ie:
scope, type, udt...)

No! I think we are at cross purposes. I run VBA code and stop it.
It is usually in PERSONAL.XLS. When I stop, I want to examine variables.
If I have declared the variable, It appears in the Locals Window.
If it is a predefined variable, such as ActiveCell, it does not seem to
be viewable in a similar manner.
I maintain a module variable declared with Dim probe As Object. (It
would probably be better declared as Dim probe as Variant so it could
reference anything. I might use probe in the immediate window by running
set probe=activecell. I can then examine a copy of ActiveCell in
arbitrary detail.


Have you looked at using VB's Put/Get functions?

No! What are they? I think I have confused you into thinking I am using
native VB rather than VBA. Help on get and put are both empty.

Get/Put are standard VB[A] file I/O functions. My understanding of your
post is that you want to write these variables and their respective
values to a text file. That's what Get/Put would be used for to
Read/Write the file, respectively.

Most of VBA's inherent language functionality IS pure VB!

I repeat. I want to grab text which copies information such as that
which appears in the View/Locals window.

e.g. I want to see something like
ActiveCell Range
AddIndent False Variant/Boolean
AllowEdit False Boolean
...

I gather that VBA code can't be used to get a list of the names in an
object referenced by a VBA variable.

I believe there are utility addins out there that will let you do this
within the VBE. You'd be better off to google this for VB as I suspect
you'd get more results.<g


[snip]

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Aug 14, 7:17*pm, GS wrote:
Walter Briscoe presented the following explanation :





In message of Sun, 14 Aug 2011 11:56:58 in
microsoft.public.excel.programming, GS writes
Walter Briscoe pretended :

[snip]

I have two known unsatisfied needs for Excel:

1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;

Can you elaborate more on this? How are the variables declared? (ie:
scope, type, udt...)

No! I think we are at cross purposes. I run VBA code and stop it.
It is usually in PERSONAL.XLS. When I stop, I want to examine variables..
If I have declared the variable, It appears in the Locals Window.
If it is a predefined variable, such as ActiveCell, it does not seem to
be viewable in a similar manner.
I maintain a module variable declared with Dim probe As Object. (It
would probably be better declared as Dim probe as Variant so it could
reference anything. I might use probe in the immediate window by running
set probe=activecell. I can then examine a copy of ActiveCell in
arbitrary detail.

Have you looked at using VB's Put/Get functions?

No! What are they? I think I have confused you into thinking I am using
native VB rather than VBA. Help on get and put are both empty.

Get/Put are standard VB[A] file I/O functions. My understanding of your
post is that you want to write these variables and their respective
values to a text file. That's what Get/Put would be used for to
Read/Write the file, respectively.

Most of VBA's inherent language functionality IS pure VB!

I repeat. I want to grab text which copies information such as that
which appears in the View/Locals window.

e.g. I want to see something like
ActiveCell * * * * * * * * * * * * * * *Range
* * AddIndent * False * * * * * * * * * Variant/Boolean
* * AllowEdit * False * * * * * * * * * Boolean
...

I gather that VBA code can't be used to get a list of the names in an
object referenced by a VBA variable.

I believe there are utility addins out there that will let you do this
within the VBE. You'd be better off to google this for VB as I suspect
you'd get more results.<g



[snip]

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Have you tried the suggestion to combine and remove dups?

Don Guillett wrote :
On Aug 14, 7:17*pm, GS wrote:
Walter Briscoe presented the following explanation :





In message of Sun, 14 Aug 2011 11:56:58 in
microsoft.public.excel.programming, GS writes
Walter Briscoe pretended :

[snip]

I have two known unsatisfied needs for Excel:
1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;
Can you elaborate more on this? How are the variables declared? (ie:
scope, type, udt...)

No! I think we are at cross purposes. I run VBA code and stop it.
It is usually in PERSONAL.XLS. When I stop, I want to examine variables.
If I have declared the variable, It appears in the Locals Window.
If it is a predefined variable, such as ActiveCell, it does not seem to
be viewable in a similar manner.
I maintain a module variable declared with Dim probe As Object. (It
would probably be better declared as Dim probe as Variant so it could
reference anything. I might use probe in the immediate window by running
set probe=activecell. I can then examine a copy of ActiveCell in
arbitrary detail.

Have you looked at using VB's Put/Get functions?
No! What are they? I think I have confused you into thinking I am using
native VB rather than VBA. Help on get and put are both empty.

Get/Put are standard VB[A] file I/O functions. My understanding of your
post is that you want to write these variables and their respective
values to a text file. That's what Get/Put would be used for to
Read/Write the file, respectively.

Most of VBA's inherent language functionality IS pure VB!

I repeat. I want to grab text which copies information such as that
which appears in the View/Locals window.
e.g. I want to see something like
ActiveCell * * * * * * * * * * * * * * *Range
* * AddIndent * False * * * * * * * * * Variant/Boolean
* * AllowEdit * False * * * * * * * * * Boolean
...

I gather that VBA code can't be used to get a list of the names in an
object referenced by a VBA variable.

I believe there are utility addins out there that will let you do this
within the VBE. You'd be better off to google this for VB as I suspect
you'd get more results.<g



[snip]

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Have you tried the suggestion to combine and remove dups?

No, not in the context that you suggested. Albeit viable as is, I'd
usually load the data into dynamic arrays and do it that way (in
earlier versions), but I'll look at your suggestion for xl2=! Thanks
for mentioning it...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Aug 15, 10:04*am, GS wrote:
Don Guillett wrote :





On Aug 14, 7:17*pm, GS wrote:
Walter Briscoe presented the following explanation :

In message of Sun, 14 Aug 2011 11:56:58 in
microsoft.public.excel.programming, GS writes
Walter Briscoe pretended :

[snip]

I have two known unsatisfied needs for Excel:
1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;
Can you elaborate more on this? How are the variables declared? (ie:
scope, type, udt...)

No! I think we are at cross purposes. I run VBA code and stop it.
It is usually in PERSONAL.XLS. When I stop, I want to examine variables.
If I have declared the variable, It appears in the Locals Window.
If it is a predefined variable, such as ActiveCell, it does not seem to
be viewable in a similar manner.
I maintain a module variable declared with Dim probe As Object. (It
would probably be better declared as Dim probe as Variant so it could
reference anything. I might use probe in the immediate window by running
set probe=activecell. I can then examine a copy of ActiveCell in
arbitrary detail.

Have you looked at using VB's Put/Get functions?
No! What are they? I think I have confused you into thinking I am using
native VB rather than VBA. Help on get and put are both empty.

Get/Put are standard VB[A] file I/O functions. My understanding of your
post is that you want to write these variables and their respective
values to a text file. That's what Get/Put would be used for to
Read/Write the file, respectively.

Most of VBA's inherent language functionality IS pure VB!

I repeat. I want to grab text which copies information such as that
which appears in the View/Locals window.
e.g. I want to see something like
ActiveCell * * * * * * * * * * * * * * *Range
* * AddIndent * False * * * * * * * * * Variant/Boolean
* * AllowEdit * False * * * * * * * * * Boolean
...

I gather that VBA code can't be used to get a list of the names in an
object referenced by a VBA variable.

I believe there are utility addins out there that will let you do this
within the VBE. You'd be better off to google this for VB as I suspect
you'd get more results.<g

[snip]

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Have you tried the suggestion to combine and remove dups?

No, not in the context that you suggested. Albeit viable as is, I'd
usually load the data into dynamic arrays and do it that way (in
earlier versions), but I'll look at your suggestion for xl2=! Thanks
for mentioning it...

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Send me your file and I'll show you.

Don,
I am NOT the OP! I do not have a file to send you.

That said, I'm a firm advocate of using built-in (available)
functionality over using VB[A] to do the same task because it's always
more efficient to do so. I just happen to have wrapper procedures for
doing this via arrays, and so I persist this regardless of what version
my projects run in.

I have no problem, though, to make projects version-aware so as to take
advantage of the newer version built-in features.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On 14/08/2011 20:33, Walter Briscoe wrote:
In of Sun, 14 Aug 2011 11:56:58 in
microsoft.public.excel.programming, writes
Walter Briscoe pretended :

[snip]

I have two known unsatisfied needs for Excel:

1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;

Can you elaborate more on this? How are the variables declared? (ie:
scope, type, udt...)

No! I think we are at cross purposes. I run VBA code and stop it.
It is usually in PERSONAL.XLS. When I stop, I want to examine variables.
If I have declared the variable, It appears in the Locals Window.
If it is a predefined variable, such as ActiveCell, it does not seem to
be viewable in a similar manner.
I maintain a module variable declared with Dim probe As Object. (It
would probably be better declared as Dim probe as Variant so it could
reference anything. I might use probe in the immediate window by running
set probe=activecell. I can then examine a copy of ActiveCell in
arbitrary detail.


Have you looked at using VB's Put/Get functions?

No! What are they? I think I have confused you into thinking I am using
native VB rather than VBA. Help on get and put are both empty.
I repeat. I want to grab text which copies information such as that
which appears in the View/Locals window.

e.g. I want to see something like
ActiveCell Range
AddIndent False Variant/Boolean
AllowEdit False Boolean
...

I gather that VBA code can't be used to get a list of the names in an
object referenced by a VBA variable.

[snip]

I don't know of a way to probe the list of names that a given object
supports from VBA, but you can try all names and then output the ones
that have sensible non null values. I think it will drive you mad, to do
this, but the following code will do pretty much what you ask for.

It obviously needs refinements - it just gives the internal numeric code
of VarType() rather than indexing into an array of type names.

The code below is just a proof of concept using error trapping to allow
attempts to fetch non existent fields from an object. Obviously a full
version would read the whole range of possible fields into a array of
variants and then display only the ones that are not empty/null.



Sub Test(o As Object)
On Error Resume Next
sAddIdent = o.Addident
sAllowEdit = o.AllowEdit
sColumn = o.Column
sCountLarge = o.CountLarge
sFormula = o.Formula
sFormulaR1C1 = o.FormulaR1C1
sLeft = o.Left

Debug.Print aAddIdent, VarType(sAddIdent)
Debug.Print sAllowEdit, VarType(AllowEdit)
Debug.Print sColumn, VarType(sColumn)
Debug.Print sCountLarge, VarType(sCountLarge)
Debug.Print sFormula, VarType(sFormula)
Debug.Print sFormulaR1C1, VarType(sFormulaR1C1)
Debug.Print sLeft, VarType(sLeft)
End Sub

Sub Try()
Call Test(ActiveCell)
End Sub

I note in passing that the XL2007 debugger Local and Watches window
reports o.CountLarge being Variant/<Unsupported Variant Type
It would be easy enough to write them as strings to a file instead of
debug.print.

BTW: Is there any interest in a McCabe CCI metric generator for finding
maintenance hotspots in inherited VBA code?

Regards,
Martin Brown

In message of Tue, 16 Aug 2011
11:59:52 in microsoft.public.excel.programming, Martin Brown <|||newspam
writes
On 14/08/2011 20:33, Walter Briscoe wrote:
In of Sun, 14 Aug 2011 11:56:58 in
microsoft.public.excel.programming, writes
Walter Briscoe pretended :

[snip]

I have two known unsatisfied needs for Excel:

1) A means of dumping VBA variables TO A TEXT FILE. (I have lots of
tools which handle text files.) Visual Basic Editor's View/Locals window
shows the information I want, but not in a convenient format. Ctrl-A and
Ctrl-C support would probably give me much of what I want;


[snip]

Thank you for your efforts. In principle, it could be used to reference
a lists of known member names in known types. The effort of generating
such lists is more than I am prepared to do.

I don't know of a way to probe the list of names that a given object
supports from VBA, but you can try all names and then output the ones
that have sensible non null values. I think it will drive you mad, to
do this, but the following code will do pretty much what you ask for.

It obviously needs refinements - it just gives the internal numeric
code of VarType() rather than indexing into an array of type names.

The code below is just a proof of concept using error trapping to allow
attempts to fetch non existent fields from an object. Obviously a full
version would read the whole range of possible fields into a array of
variants and then display only the ones that are not empty/null.



Sub Test(o As Object)
On Error Resume Next
sAddIdent = o.Addident
sAllowEdit = o.AllowEdit
sColumn = o.Column
sCountLarge = o.CountLarge
sFormula = o.Formula
sFormulaR1C1 = o.FormulaR1C1
sLeft = o.Left

Debug.Print aAddIdent, VarType(sAddIdent)
TypeName(sAddIdent) might be useful here

[snip]

BTW: Is there any interest in a McCabe CCI metric generator for finding
maintenance hotspots in inherited VBA code?

I am sorry I don't know what you are talking about.
CCI is an acronym for many terms in Wikipedia.
--
Walter Briscoe

Hey there Walter

I think CCI stands for:

Cyclomatic Complexity Indicator - McCabe Cyclomatic Complexity (aka CCI or
CCN).

http://en.wikipedia.org/wiki/Cyclomatic_complexity

Cyclomatic complexity (or conditional complexity) is a software metric
(measurement). It was developed by Thomas J. McCabe, Sr. in 1976 and is used
to indicate the complexity of a program. It directly measures the number of
linearly independent paths through a program's source code. The concept,
although not the method, is somewhat similar to that of general text
complexity measured by the Flesch-Kincaid Readability Test.

Cyclomatic complexity is computed using the control flow graph of the
program: the nodes of the graph correspond to indivisible groups of commands
of a program, and a directed edge connects two nodes if the second command
might be executed immediately after the first command. Cyclomatic complexity
may also be applied to individual functions, modules, methods or classes
within a program.



HTH

Mick.