disable mailto

[Henry]

Folks:

I'm copying a bunch of names, phone numbers, and email addresses from a
sign-up sheet.

Excel thinks it is being helpful by bringing up a new, empty Entourage email
form with that address every time I get near a single entry in the "email
address" column.

How do I disable this behavior?

Yes, I checked the MVP site (no clear place to look) and the Excel help
file.

Thanks,

Henry

(e-mail address removed) remove 'zzz'

 

[J.E. McGimpsey]

Folks:

I'm copying a bunch of names, phone numbers, and email addresses from a
sign-up sheet.

Excel thinks it is being helpful by bringing up a new, empty Entourage email
form with that address every time I get near a single entry in the "email
address" column.

How do I disable this behavior?

Yes, I checked the MVP site (no clear place to look) and the Excel help
file.

To disable XL's automatic hyperlink feature, take a look here:

http://www.mcgimpsey.com/excel/nohyperlinks.html

To remove existing hyperlinks, type Opt-F11 to enter the Visual
Basic Editor. Type CMD-G to open the Immediate window. Paste or type
the following into that window:

ActiveSheet.Hyperlinks.Delete

Hit Return, then type Opt-F11 to return to XL.

 

[Henry]

J.E. McGimpsey:

Thanks for your response to my post:

To disable XL's automatic hyperlink feature, take a look here:

http://www.mcgimpsey.com/excel/nohyperlinks.html

Right. I knew such comprehensive information must exist _somewhere_ but
sometimes it is difficult to find it.

Your excellent write-up offers at least 5 suggestions:

The first, pre-formatting cells as text, doesn't seem to work. We're
talking about Format-->Cells-->Number-->Text, right?

Neither does post-formatting as text, which has the added side effect of
generating one or more unwanted email forms unless I'm very careful.
(Workaround: the hyperlink is not activated if the select is more than one
cell. A bit awkward, but do-able.)

The second is using a leading tick "'", as in "'(e-mail address removed)". This seems to
work best. Hmmm, I guess the tick is a ghost character: it doesn't show up
in a csv export, as I first feared. This method is bit like a secret
handshake, a little difficult to remember, but do-able.

The third fix, backing out of the hyperlink by undoing "the parser's"
intermediate step is very interesting. If Excel is going to be extra
helpful, it makes sense that each cell entry would be processed after I hit
"return". Now, is there a way to tell Excel, "Thanks, but I really don't
want you to post-process my entries, so Mr. Parser can take a break for the
duration, thank you very much"?

About three more of the fixes you give require me to understand code and/or
Excel internals. I'm a software developer, and doing so doesn't necessarily
bother me...

I realize that you've listed these for the interest of people doing a lot
more complex tasks than mine. That said, I'd still ask you to please
consider that I'm trying to do something as simple as jumping in the car to
go to the neighborhood store to get bread and milk; doing code or externals
in this context makes as much sense as adjusting the engine timing or
changing the transmission gearing ratios on the way. The fix should not be
orders of magnitude more complex than the initial task.

With reference to all these fixes: What's wrong with a simple on/off switch
in the preferences: "Enable Active Hyperlinks"?

To remove existing hyperlinks, type Opt-F11 to enter the Visual
Basic Editor.

Wow, the result of hitting Opt-F11 like tapping a hidden button under my
truck's dashboard with my secret decoder ring and getting a whole new set of
sophisticated flight instruments. I've never seen all this stuff.

(Apparently the designers don't want me to write about what I see, because
when I switch focus to my email client these windows disappear, and I have
to reactivate them with Opt-F11 every time I switch back to Excel.
Strange.)

Hmmm, there it is, a properties menu for a number of global items for this
worksheet. Why isn't one of them "Enable Active Hyperlinks" which I would
gladly set to "false"?

Type CMD-G to open the Immediate window.

Wow, cool, a command line!

Paste or type the following into that window:

ActiveSheet.Hyperlinks.Delete

Hit Return, then type Opt-F11 to return to XL.

OK, that works, thanks very much for giving me a global fix for the entries
I already have. I had already tried multiple methods of removing the
hyperlink attributes from each of them; as far as I can tell, nothing
accessible from any any pulldown menu would do the job. Nor could I find
anything in Excel Help.

Could you help me understand how a normal user, who might need this fix once
every few years, can remember this incantation or, at least, reliably find
it?

Thanks,

Henry

(e-mail address removed) remove 'zzz'









J

 

[J.E. McGimpsey]

I knew such comprehensive information must exist _somewhere_ but
sometimes it is difficult to find it.

I've just started putting XL things up on my web site - mostly
answers to FAQs or things that interest me. Other general resources
you can use:

Newsgroup archives:
http://google.com/advanced_group_search?q=group:*mac.office*

(for general XL questions, change the *mac.office* to *excel*, which
will include all the non-Mac-specific newsgroups.

Mac MVP Jim Gordon has good stuff on database connectivity and web
tables:
http://www.agentjim.com/MVP/Excel/ExcelHome.htm

My three favorite general XL sites, in no small part because I know
and respect the authors, who are also MVPs, (but in no particular
order):

Chip Pearson:
http://cpearson.com/excel/topics.htm
John Walkenbach:
http://j-walk.com/ss/excel/index.htm
David McRitchie:
http://www.mvps.org/dmcritchie/excel/excel.htm

Your excellent write-up offers at least 5 suggestions:

The first, pre-formatting cells as text, doesn't seem to work. We're
talking about Format-->Cells-->Number-->Text, right?

Hmmm...it worked once...really!...hmmm...? Thanks for the correction!

Neither does post-formatting as text, which has the added side effect of
generating one or more unwanted email forms unless I'm very careful.
(Workaround: the hyperlink is not activated if the select is more than one
cell. A bit awkward, but do-able.)

Since the hyperlink is inserted after the cell is parsed, but before
control is released to the user/VBA, post-format won't do anything.
In general, except for preformatting, cell format has nothing to do
with how the values stored in the cells, merely how they're
displayed.

The second is using a leading tick "'", as in "'(e-mail address removed)". This seems to
work best. Hmmm, I guess the tick is a ghost character: it doesn't show up
in a csv export, as I first feared. This method is bit like a secret
handshake, a little difficult to remember, but do-able.

It's a good way to enter numbers as text, too, as when you need the
leading zeros as part of the value, rather than formatting them
(e.g., for exporting to csv)

The third fix, backing out of the hyperlink by undoing "the parser's"
intermediate step is very interesting. If Excel is going to be extra
helpful, it makes sense that each cell entry would be processed after I hit
"return". Now, is there a way to tell Excel, "Thanks, but I really don't
want you to post-process my entries, so Mr. Parser can take a break for the
duration, thank you very much"?

Unfortunately not.

About three more of the fixes you give require me to understand code and/or
Excel internals. I'm a software developer, and doing so doesn't necessarily
bother me...

I realize that you've listed these for the interest of people doing a lot
more complex tasks than mine. That said, I'd still ask you to please
consider that I'm trying to do something as simple as jumping in the car to
go to the neighborhood store to get bread and milk; doing code or externals
in this context makes as much sense as adjusting the engine timing or
changing the transmission gearing ratios on the way. The fix should not be
orders of magnitude more complex than the initial task.

True. OTOH, VBA allows you to make XL sing very different tunes than
it does out of the box.

With reference to all these fixes: What's wrong with a simple on/off switch
in the preferences: "Enable Active Hyperlinks"?

Nothing, except it's not provided by MS. WinXL users finally got the
option in XL02, two versions after auto-hyperlinks first showed up
on that platform. So there's hope...

(Apparently the designers don't want me to write about what I see, because
when I switch focus to my email client these windows disappear, and I have
to reactivate them with Opt-F11 every time I switch back to Excel.
Strange.)

If you switch back to XL via the icon on the dock you should stay in
the VBE. Clicking on the (background) worksheet brings it to the
front and closes the VBE windows.

OK, that works, thanks very much for giving me a global fix for the entries
I already have. I had already tried multiple methods of removing the
hyperlink attributes from each of them; as far as I can tell, nothing
accessible from any any pulldown menu would do the job. Nor could I find
anything in Excel Help.

Could you help me understand how a normal user, who might need this fix once
every few years, can remember this incantation or, at least, reliably find
it?

The Google Archives! (No, I can't explain why it was not provided as
a preference. I just work with the product as released!)

 

[Henry]

J.E. McGimpsey:

Thank you for your response on this thread:

I've just started putting XL things up on my web site - mostly
answers to FAQs or things that interest me. Other general resources
you can use:

Newsgroup archives:
http://google.com/advanced_group_search?q=group:*mac.office*

Right. I use these archives routinely. Between lack of standardization of
terms and (shall we say) "creative" writing, it is often difficult to paw
through all the postings to find an answer to a particular issue, such as
this one.

(for general XL questions, change the *mac.office* to *excel*, which
will include all the non-Mac-specific newsgroups.

Since you bring that up, I've never been sure about how to deal with issues
that might be Mac-specific and might not be. Cross-post?

Mac MVP Jim Gordon has good stuff on database connectivity and web
tables:

http://www.agentjim.com/MVP/Excel/ExcelHome.htm

Right. Jim Gordon's name is well-known to me.

I use the MVP site first, looking at FAQs and then links.

My three favorite general XL sites, in no small part because I know
and respect the authors, who are also MVPs, (but in no particular
order):

Chip Pearson:
http://cpearson.com/excel/topics.htm
John Walkenbach:
http://j-walk.com/ss/excel/index.htm
David McRitchie:
http://www.mvps.org/dmcritchie/excel/excel.htm

Right. Keep in mind that by the time I go through all this, I've likely
forgotten my original question.

There really ought to be a better way.

At the risk of going off-topic, here's another example: how to get a top,
title row to show up on the screen no matter how far down one scrolls in a
big spreadsheet. I could not for the life of me figure out the right search
term to locate this function in the Excel help (or the newsgroups or the MVP
FAQs, for that matter.) "Sticky"? No. And so on. Eventually, I did get
to "freeze panes" which didn't work very well. I ended up using
Format-->Row-->Hide creatively.

Hmmm...it worked once...really!...hmmm...? Thanks for the correction!

Hmmm, indeed. Well, nothing in life is guaranteed, is it?

Since the hyperlink is inserted after the cell is parsed, but before
control is released to the user/VBA, post-format won't do anything.

Makes sense.

In general, except for preformatting, cell format has nothing to do
with how the values stored in the cells, merely how they're
displayed.

That's a bit subtle. I think this might be made more visible, e.g. by
animating the intermediate steps for troubleshooting. But I'm not going to
hold my breath for that.

It's a good way to enter numbers as text, too, as when you need the
leading zeros as part of the value, rather than formatting them
(e.g., for exporting to csv)

Right, I think was doing this (maybe with double-quote, but on the same
principle) in the first spreadsheet I ever used. It seems no one has come
up with a better way of handling this particular literalization
specification problem.

Unfortunately not.

I can't say that any better. Anyone from MS listening?

True. OTOH, VBA allows you to make XL sing very different tunes than
it does out of the box.

Well, the partitioning of what can be done in the GUI versus in VBA might
could use a bit of adjusting.

For example, you previously suggested using ActiveSheet.Hyperlinks.Delete
from the command line to remove any active hyperlinks. It should be
possible to customize Excel to make this command visible via the GUI --
that's the right answer to my query further below about how someone could
remember such a command over the years. This customization must be
scriptable, read by Excel at initialization, so that the customizations are
visible and (more or less) portable and independently documented.

Nothing, except it's not provided by MS. WinXL users finally got the
option in XL02, two versions after auto-hyperlinks first showed up
on that platform. So there's hope...

I hope so.

If you switch back to XL via the icon on the dock you should stay in
the VBE. Clicking on the (background) worksheet brings it to the
front and closes the VBE windows.

Right, except no other application I know of works that way.

The Google Archives! (No, I can't explain why it was not provided as
a preference. I just work with the product as released!)

I've already discussed the problem with the Archives (no offense to Google
-- I'm very grateful for them) and suggested a more robust solution above.

Thanks,

Henry

(e-mail address removed) remove 'zzz'

 

[J.E. McGimpsey]

Since you bring that up, I've never been sure about how to deal with issues
that might be Mac-specific and might not be. Cross-post?

That's up to you. I have no problem with cross-posting, as long as
you're truly cross-posting (multiple groups in the To: header), not
what I call multi-posting (same message to multiple groups). Every
once in a while someone gets a bug in their shorts about it (usually
an Outlook Express user, for some reason), but I just ignore them.
The general gestalt I've found on the regular XL groups is that two
groups is usually fine (e.g., m.p.e.programming and
m.p.e.worksheet.functions, if you want a worksheet function
solution, but think that you probably need a UDF), five groups
usually isn't, and in between depends on how appropriate the
question is to each group.

I can't say that any better. Anyone from MS listening?

Perhaps, perhaps not - they don't post very often. However, in
Office v.X you have a built in path to the MacBU feedback site -
Help/Send feedback on X...

You can also send an email to

(e-mail address removed)

making sure you put something like "Mac XL" in the subject line to
make sure it gets routed to the appropriate program manager.

Well, the partitioning of what can be done in the GUI versus in VBA might
could use a bit of adjusting.

For example, you previously suggested using ActiveSheet.Hyperlinks.Delete
from the command line to remove any active hyperlinks. It should be
possible to customize Excel to make this command visible via the GUI --
that's the right answer to my query further below about how someone could
remember such a command over the years. This customization must be
scriptable, read by Excel at initialization, so that the customizations are
visible and (more or less) portable and independently documented.

How about this:

Save this macro in a regular code module in your Personal Macro
Workbook (from within the VBE, select Personal Macro Workbook and
choose Insert/Module):

Public Sub RemoveHyperlinks()
ActiveSheet.Hyperlinks.Delete
End Sub

Choose Tools/Customize. Select the Commands tab and in the
Categories listbox, choose Macros.

Drag the Custom Button to toolbar or the Custom Menu Item button to
a menu. Click OK.

CTRL-click the new button and choose Assign Macro... Select your
macro and click OK.

If you want to change the button face (and you will), CTRL-click the
button and choose Properties. Select another face from the dropdown
on the top-left of the dialog, or paste a picture using the Paste
Face option from the dropdown. 16x16 pixels work best - XL tries to
scale larger pics, but the results aren't usually as good as you can
get by hand editing.

In that same dialog, you can change the buttons name, which appears
in the tooltip when the cursor hovers over it. Unless you use VBA,
that's about the extent of the documentation. WinOffice has a Help
application that lets you add documentation via HTML - it hasn't
made it to the Mac side.

The PMW, since by default it's stored in XL's Startup folder
(Microsoft Office X:Office:Startup:Excel) opens automatically
(unless you have the Shift-key down) so the macro will be available
for all worksheets.

Note that the toolbar button is stored in
~:Libraryreference:Microsoft:Excel Toolbars (10)

 

[Henry]

J.E. McGimpsey:

Thanks for your response on this thread:

Perhaps, perhaps not - they don't post very often. However, in
Office v.X you have a built in path to the MacBU feedback site -
Help/Send feedback on X...

You can also send an email to

(e-mail address removed)

making sure you put something like "Mac XL" in the subject line to
make sure it gets routed to the appropriate program manager.

Sorry, but not once in my long years as a Microsoft customer have I ever had
confirmation that my communications are received, much less considered.

But I'll honor your patient help (and the encouragement of one of your
colleagues on the Word side) by attempting to use these methods once again.
I half-retract this statement, based on your example below.

How about this:

Save this macro in a regular code module in your Personal Macro
Workbook (from within the VBE, select Personal Macro Workbook and
choose Insert/Module):

Right. I had to do a bit of googling to figure out why I had no PMW and how
create one. No big deal.

I guess a PMW is effectively a spreadsheet that's by definition devoid of
data or formulae, and simply acts as a carrier of one's macros from launch
to launch.

Once established, through the initialization process, each "personal" macro
_is_ visible through the VBA mechanism, which is close to what I asked for,
I guess, close enough.

Is it transportable? Fire up Office XP... the process of creating a PMW is
parallel, but I can't find any such file, and the data at the online help
"Managing Macros with the Visual Basic Editor" topic in a table below
"Managing modules and projects" seems to say that one can't store macros
except in a specific worksheet. So apparently a collection of one's
macro's is not transportable to XP.

Public Sub RemoveHyperlinks()
ActiveSheet.Hyperlinks.Delete
End Sub

This all makes sense, but how in the world would I discover that such a
function as ActiveSheet.Hyperlinks.Delete exists, except by posting a rather
pathetic plea on the NG and relying on the generosity of an MVP?

I'm an experienced software developer, though not in object-oriented
techniques. Two comments:

#1 I'm guessing that if you happen to know that the identifier "Hyperlinks"
refers to all the hyperlinks in a specified sheet (or that the modifier
"ActiveSheet" makes it so), and you know the rudiments of VBA syntax, you
can synthesize "ActiveSheet.Hyperlinks.Delete" even if this construct had
not ever been uttered before. So far so good? But active hyperlinks in
the spreadsheet are labeled "mail to:" not "hyperlink", and "mail to"
doesn't lead to "hyperlink" in the VB help, so --again-- how would one
figure this out without knowing it beforehand?

#2 I'm accustomed to opening an unfamiliar, "brand x" GUI Integrated
Development Environment (IDE) and being productive within a few minutes.
This VBA environment doesn't follow a familiar pattern, and I'm finding I'm
having trouble getting my mind around it. Can you recommend a top-level
description of this environment, especially one that stresses the
commonality of such environments across all Office apps, and even across
platforms?

Choose Tools/Customize. Select the Commands tab and in the
Categories listbox, choose Macros.

Drag the Custom Button to toolbar or the Custom Menu Item button to
a menu. Click OK.

CTRL-click the new button and choose Assign Macro... Select your
macro and click OK.

If you want to change the button face (and you will), CTRL-click the
button and choose Properties. Select another face from the dropdown
on the top-left of the dialog, or paste a picture using the Paste
Face option from the dropdown. 16x16 pixels work best - XL tries to
scale larger pics, but the results aren't usually as good as you can
get by hand editing.

OK, thanks to your instructions I actually accomplished all this in short
order and without mishap.

In that same dialog, you can change the buttons name, which appears
in the tooltip when the cursor hovers over it. Unless you use VBA,
that's about the extent of the documentation.

Right. One of my difficulties with all this is mode switching. My cool
custom icon for this macro is visible in "normal" mode but not visible in
VBA whatever-you-call-it mode. It's a bit awkward.

WinOffice has a Help application that lets you add documentation via HTML - it
hasn't made it to the Mac side.

No hurry. All I want is to see the code -- should be self-documenting

The PMW, since by default it's stored in XL's Startup folder
(Microsoft Office X:Office:Startup:Excel) opens automatically
(unless you have the Shift-key down) so the macro will be available
for all worksheets.

Right. Got that. Hmmm, a multi-user MacOS X might actually want this
stored in a user folder. Never mind, (thus far) there's only one of me.

Note that the toolbar button is stored in
~:Libraryreference:Microsoft:Excel Toolbars (10)

OK, thanks. I'm not sure I want to know why it's numbered 10.

Thanks,

Henry

(e-mail address removed) remove 'zzz'

 

[J.E. McGimpsey]

Sorry, but not once in my long years as a Microsoft customer have I ever had
confirmation that my communications are received, much less considered.

But I'll honor your patient help (and the encouragement of one of your
colleagues on the Word side) by attempting to use these methods once again.

Please do. You won't get an acknowledgement this time, either, but
you will get your message read - having met some of the people at
the Mac Business Unit (MacBU), I know they are eager to get feedback
and suggestions.

Right. I had to do a bit of googling to figure out why I had no PMW and how
create one. No big deal.

Sorry - shouldn't have assumed.

I guess a PMW is effectively a spreadsheet that's by definition devoid of
data or formulae, and simply acts as a carrier of one's macros from launch
to launch.

The Personal Macro Workbook is just like any other workbook. PMW is
just the name that Excel gives by default when it creates a workbook
for your macro. You could create a workbook named "fred" and it
would work the same, as long as it was put in the Startup Folder (by
default, the PMW is hidden - you can unhide it, or hide "fred",
using Window/(Un)Hide...).

Once established, through the initialization process, each "personal" macro
_is_ visible through the VBA mechanism, which is close to what I asked for,
I guess, close enough.

Is it transportable? Fire up Office XP... the process of creating a PMW is
parallel, but I can't find any such file, and the data at the online help
"Managing Macros with the Visual Basic Editor" topic in a table below
"Managing modules and projects" seems to say that one can't store macros
except in a specific worksheet. So apparently a collection of one's
macro's is not transportable to XP.

In WinXL, the file is named Personal.xls (a throwback to earlier
Windows' 8.2 limitation) and it's stored in a folder named XLStart
in the Office directory (the actual path depends on the version, I
believe).

This all makes sense, but how in the world would I discover that such a
function as ActiveSheet.Hyperlinks.Delete exists, except by posting a rather
pathetic plea on the NG and relying on the generosity of an MVP?

First you have to get to macros and the VBE. And here's where MS has
done Mac users a real disservice over the years. If you've not used
the built-in Help, start - it's not as good as the comparable WinXL,
but it's light-years better than it was in MacXL4/5, which is when
Mac users gave up and never looked back...

The very first topic in Help that you should search for is "help" -
it will give you a bunch of topics that will assist you in using
help.

If you look in XL Help, you'll find the topic "Macros: Automating
tasks you perform frequently", which will lead you to other topics
that introduce the VBE and editing macros.

Note that VBA Help is not XL Help - you have to install it (but
that's explained in one of the topics in the "help" search.

I'm an experienced software developer, though not in object-oriented
techniques. Two comments:

#1 I'm guessing that if you happen to know that the identifier "Hyperlinks"
refers to all the hyperlinks in a specified sheet (or that the modifier
"ActiveSheet" makes it so), and you know the rudiments of VBA syntax, you
can synthesize "ActiveSheet.Hyperlinks.Delete" even if this construct had
not ever been uttered before. So far so good? But active hyperlinks in
the spreadsheet are labeled "mail to:" not "hyperlink", and "mail to"
doesn't lead to "hyperlink" in the VB help, so --again-- how would one
figure this out without knowing it beforehand?

OK - in an ideal world, you'd look up mailto in XL help and be given
a page which describes hyperlinks. You can then look up hyperlinks
in VBA help to see how to use VBA to manipulate them.

The absolute bar-none best way to learn about VBA is to record a
macro. The macro recorder writes absolutely terrible code (partly
because it's based on the particular cells you select/manipulate,
partly because it records what you did, but can't anticipate what
you mean to do), but it lets you get an idea what objects and
methods are involved. When I recorded a macro of deleting a
hyperlink in XL (ctrl-click the cell, choose Hyperlinks, then click
the Remove Hyperlinks button) this is what it came up with:

Selection.Hyperlinks.Delete

This gets you 80% of the way there - you can use this macro as is on
an entire sheet - just select all the cells in the sheet first.

There's a conceptual issue that you need to grasp when using VBA.
Nearly everything in XL is made up of objects. Each object has
Methods and Properties. In this case, the macro recorder recorded
the use of the Delete method of the Hyperlinks collection object
(which is just a container for hyperlinks) of the Selection object.
If you look at the Hyperlinks collection object in Help, and click
on Applies To... you'll see that there are dozens of objects that
can have a Hyperlinks Collection object, among them a Worksheet
Object.

The last bit is realizing that ActiveSheet is a property of the
Worksheets collection object that refers to the currently active
workbook object. That's not necessarily intuitive, but it is
documented in the Worksheets object Help topic.

It's a bit of an iterative process, just like learning any other
language/technique

#2 I'm accustomed to opening an unfamiliar, "brand x" GUI Integrated
Development Environment (IDE) and being productive within a few minutes.
This VBA environment doesn't follow a familiar pattern, and I'm finding I'm
having trouble getting my mind around it. Can you recommend a top-level
description of this environment, especially one that stresses the
commonality of such environments across all Office apps, and even across
platforms?

I can't really - the Mac VBE is definitely not an IDE. Perhaps MS's
VBA101 (for Windows, but much of the info is the same - though Macs
lack Intellisense and the Local and Watch windows):

http://support.microsoft.com/default.aspx?scid=/support/excel/content
/vba101/default.asp

David McRitchie has a good, really basic, introduction to using VBA
with good links to other sites:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Right. Got that. Hmmm, a multi-user MacOS X might actually want this
stored in a user folder. Never mind, (thus far) there's only one of me.

Yup - I store my PMW in my Microsoft User Data folder and put an
alias to it in the Startup:Excel folder. If using it in a multi-user
environment, you just need to make sure permissions are set
correctly.

OK, thanks. I'm not sure I want to know why it's numbered 10.

It's the version number: Office 98 is version 8, Office 2001 is
version 9, and, fortuitously, Office v.X is version 10. The next
version will be 11, though I've no idea what it will be called.

On the WinOffice side, version 8 is Office 97, version 9 is Office
2000 and version 10 is Office XP. Office 2003, which was just
(partially) released, is version 11.

 

[Henry]

J.E. McGimpsey:

Thank you for your reply on this thread:

The Personal Macro Workbook is just like any other workbook. PMW is
just the name that Excel gives by default when it creates a workbook
for your macro. You could create a workbook named "fred" and it
would work the same, as long as it was put in the Startup Folder (by
default, the PMW is hidden - you can unhide it, or hide "fred",
using Window/(Un)Hide...).

I think you are saying that opening a workbook containing any macros makes
all those macros available for the balance of the session. Makes sense.

In WinXL, the file is named Personal.xls (a throwback to earlier
Windows' 8.2 limitation) and it's stored in a folder named XLStart
in the Office directory (the actual path depends on the version, I
believe).

I searched on permutations of "personal" and "workbook" etc., and didn't
seem to find anything such.

It is strange that the help entry says "you can't do that" but if Excel XP
operates according to the principle given above ("any macros ...are
available for the balance of the session), then...well, the help file is
wrong or at least misleading.

First you have to get to macros and the VBE.

First there's a bit of confusion about what a Macro is, exactly. To me, it
is a named concatenation of atomic operations, as a programmer sees in a
Macro Assembler environment. If you do the same series of simple,
indivisible operations often enough it makes sense to reference them
symbolically.

I've been programming in one version of Basic or another for ...a very long
time, and I think I know roughly what that means.

I guess that Macros are composed of Basic commands, and the "VBA" implies a
lot of application-specific operators have been added to the list of "basic"
Basic commands. Thus a Macro is a function subroutine, more or less, one
that can be invoked in immediate mode via a command line or included in a
program that will be executed "later".

And here's where MS has done Mac users a real disservice over the years. If
you've not used the built-in Help, start - it's not as good as the comparable
WinXL, but it's light-years better than it was in MacXL4/5, which is when Mac
users gave up and never looked back...

OK, I've loaded all the VBA help for both Win and OS X. Never needed it
before.

Historically, I've found Office help to be useless or worse. So much so that
I came to the conclusion that MS must be producing bad help completely on
purpose -- no one could do so badly by accident.

I've cited a few problems in this thread where it is misleading, but
--true-- it seems to be improving.

In general, I believe the design of modern GUI-based application should
reduce the need to make references to Help subsystems to a bare minimum.
Clearly, if you need detailed syntactical information, you need a reference
source. Even this can be partly covered by in-context help.

The very first topic in Help that you should search for is "help" -
it will give you a bunch of topics that will assist you in using
help.

OK. How about "index"?

If you look in XL Help, you'll find the topic "Macros: Automating
tasks you perform frequently", which will lead you to other topics
that introduce the VBE and editing macros.

Right. OK.

Note that VBA Help is not XL Help - you have to install it (but
that's explained in one of the topics in the "help" search.

Right.

Note: One must be able to make a leap from the Excel X error message
regarding the non-installation of VBA help to find the installer in the
Value Pack, a bit awkward, but surmountable.

Second note: Regular Excel X help doesn't even define the term "VBA"
according to Clippy. The least it could do is refer you to the VBA "mode"
help. There's that really difficult mode switching problem I mentioned
earlier. Do the Excel folks and the VBA folks work for the same company?

OK - in an ideal world, you'd look up mailto in XL help and be given
a page which describes hyperlinks. You can then look up hyperlinks
in VBA help to see how to use VBA to manipulate them.

Yup, exactly.

The absolute bar-none best way to learn about VBA is to record a
macro. The macro recorder writes absolutely terrible code (partly
because it's based on the particular cells you select/manipulate,
partly because it records what you did, but can't anticipate what
you mean to do), but it lets you get an idea what objects and
methods are involved.

This seems parallel to coding something in c so you can produce assembler
code that's usually equally terrible, so it is a familiar concept.

The problem for me is that I really have no idea why I would want to use
macros. This is the first case in which I have been given a good reason to
do so in Excel. This is a poor example, I think, because writing a macro
in this case is simply putting a wrapper around a single construct to do a
particular task, namely deleting all hyperlinks in the active worksheet.

Not exactly a coding challenge, but a conceptual one -- I simply can't
understand why the designers didn't implement hyperlink control in parallel
with a number of other preference-controlled items, a much simpler solution
and one that is much more accessible to the less technically adept.

When I recorded a macro of deleting a hyperlink in XL (ctrl-click the cell,
choose Hyperlinks, then click the Remove Hyperlinks button) this is what it
came up with:

Selection.Hyperlinks.Delete

This gets you 80% of the way there - you can use this macro as is on an entire
sheet - just select all the cells in the sheet first.

Right. This would be right-mouse click in Win, right?

There's a conceptual issue that you need to grasp when using VBA.

So it would seem.

Nearly everything in XL is made up of objects.

That's what I was afraid of, I'm willy-nilly falling into OOP.

Each object has Methods and Properties.

Terms which I have never succeeded in finding particularly descriptive, even
though the underlying concepts are quite reasonable.

In this case, the macro recorder recorded the use of the Delete method of the
Hyperlinks collection object (which is just a container for hyperlinks) of the
Selection object.

You just sneaked in the "container" metaphor... without definition. (I've
been through this with one of your colleagues on the Word side.)

I find a Mass in Latin more penetrable than this, and I'm not even Catholic.

Nevertheless, let me string together what you've expressed in summary form:

a) "hyperlink: ~= "mail to" thingie,

b) a hyperlink has properties (such as the value of the link itself), and

c) a hyperlink has standard things you can do it it, such as Delete

d) you can define scope of an operation, e.g. "Selection" or "WorkSheet"

e) there's a syntax for stringing these keywords together

If you look at the Hyperlinks collection object in Help, and click
on Applies To... you'll see that there are dozens of objects that
can have a Hyperlinks Collection object, among them a Worksheet
Object.

It is a bit difficult to do this because Excel Help goes out of context when
I switch to this context.

Yes, Clippy's first suggestion is "Hyperlinks Collection Object", but no,
there doesn't seem to be any "Applies to..." link in the reference page.

The last bit is realizing that ActiveSheet is a property of the
Worksheets collection object that refers to the currently active
workbook object. That's not necessarily intuitive, but it is
documented in the Worksheets object Help topic.

I guess.

It's a bit of an iterative process, just like learning any other
language/technique

Well, certainly, but it is one that requires a higher motivation that I
currently have.

I can't really - the Mac VBE is definitely not an IDE. Perhaps MS's
VBA101 (for Windows, but much of the info is the same - though Macs
lack Intellisense and the Local and Watch windows):

http://support.microsoft.com/default.aspx?scid=/support/excel/content
/vba101/default.asp

Sorry to say, that's like coming into a movie after it has already been
playing for an hour.

David McRitchie has a good, really basic, introduction to using VBA
with good links to other sites:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Same response. Different movie.

It's the version number: Office 98 is version 8, Office 2001 is
version 9, and, fortuitously, Office v.X is version 10. The next
version will be 11, though I've no idea what it will be called.

On the WinOffice side, version 8 is Office 97, version 9 is Office
2000 and version 10 is Office XP. Office 2003, which was just
(partially) released, is version 11.

I'm sorry I asked.

Thanks,

Henry

(e-mail address removed) remove 'zzz'

 

[J.E. McGimpsey]

First there's a bit of confusion about what a Macro is, exactly. To me, it
is a named concatenation of atomic operations, as a programmer sees in a
Macro Assembler environment. If you do the same series of simple,
indivisible operations often enough it makes sense to reference them
symbolically.

Back in XL4, macros were very much that - a list of user operations.
XL4Macros still run on v.X, but there's no support for them. The one
place I find useful is with Page Setup - XL4M can set all the
parameters at once and seems instantaneous compared to VBA which
makes a seperate call for each parameter and takes seconds. See


Since XL97/98, "Macros" have been written (and recorded) in VBA.
They are formally subroutines rather than macros, but the name stuck.

I've been programming in one version of Basic or another for ...a very long
time, and I think I know roughly what that means.

I guess that Macros are composed of Basic commands, and the "VBA" implies a
lot of application-specific operators have been added to the list of "basic"
Basic commands. Thus a Macro is a function subroutine, more or less, one
that can be invoked in immediate mode via a command line or included in a
program that will be executed "later".

VBA is the driver for the Visual Basic Language. It is not a
standalone application, but needs to be implemented in conjunction
with another application. VBA provides the syntax and structures,
the applications provide the objects that are worked on and with.

Do the Excel folks and the VBA folks work for the same company?

As in most large, geographically extensive companies, yes and no...

The problem for me is that I really have no idea why I would want to use
macros.

Macros are useful for, among other things, a) performing repetitive
tasks, like importing data, formatting it and moving it from one
place to another, b) performing automatic operations via events, and
c) extending the capabilities of the application, including
automation of other applications (e.g., producing a word document
based on an XL spreadsheet).

In addition, VBA can extend the functionality of the application -
you can write a user defined function to perform functions that are
not built into XL, and call them as any other XL function.

Not exactly a coding challenge, but a conceptual one -- I simply can't
understand why the designers didn't implement hyperlink control in parallel
with a number of other preference-controlled items, a much simpler solution
and one that is much more accessible to the less technically adept.

I no longer ask why - I just lobby to change things.

That's what I was afraid of, I'm willy-nilly falling into OOP.

I won't try to give a seminar - there are lots of resources out
there if you have the desire to learn them. OTOH, it probably is at
least as effective to get OJT - learn by doing.

You just sneaked in the "container" metaphor... without definition. (I've
been through this with one of your colleagues on the Word side.)

Yup - sorry. It's hard in snippets such as this to be comprehensive.

Nevertheless, let me string together what you've expressed in summary form:

a) "hyperlink: ~= "mail to" thingie,

yes (though that's only one type of hyperlink)

b) a hyperlink has properties (such as the value of the link itself), and
yes

c) a hyperlink has standard things you can do it it, such as Delete

right - the things you can do to it are it's methods.

d) you can define scope of an operation, e.g. "Selection" or "WorkSheet"

yes - the "parent" object.

e) there's a syntax for stringing these keywords together

right - e.g., the "dot" syntax indicates that the property, method,
or object on the right belongs to the object on the left.

Yes, Clippy's first suggestion is "Hyperlinks Collection Object", but no,
there doesn't seem to be any "Applies to..." link in the reference page.

I'd recommend you send Clippy packing (via the Help/Use the Office
Assistant menu toggle), and use the Help dialog (via the Help/Search
Help menu item, or the toolbar icon) to search - it's more efficient
in most cases.

Well, certainly, but it is one that requires a higher motivation that I
currently have.

I know what you mean - I have *no* motivation to start learning
about .Net, even though it might possibly be useful for my
cross-platform development someday.

Sorry to say, that's like coming into a movie after it has already been
playing for an hour.

If you decide you want to plunge in, I can make some recommendations
on good books that are probably starting at about the right level.
Two that I really like, though they're expensive for just satisfying
curiosity:

"Excel 2002 Power Programming with VBA" by John Walkenbach

and

"Excel 2002 VBA Programmer's Reference" by Bovey, Bullen, Green
and Rosenberg

You may be able to find them, or their editions for earlier versions
in your library - anything Office97 or later will do you well -
they're about 90% Mac compatible.

 

[Henry]

J.E. McGimpsey:

Thanks for your response on this thread:

Back in XL4, macros were very much that - a list of user operations.
XL4Macros still run on v.X, but there's no support for them. The one
place I find useful is with Page Setup - XL4M can set all the
parameters at once and seems instantaneous compared to VBA which
makes a seperate call for each parameter and takes seconds. See

Sorry, your reference link didn't seem to make it.

Since XL97/98, "Macros" have been written (and recorded) in VBA.
They are formally subroutines rather than macros, but the name stuck.

The name may have stuck, but it is confusing, especially if "true" macros
are still available as you describe above.

VBA is the driver for the Visual Basic Language. It is not a
standalone application, but needs to be implemented in conjunction
with another application. VBA provides the syntax and structures,
the applications provide the objects that are worked on and with.

Right.

From what I can tell, thus far, the term "Basic" itself is also a bit
misleading. I've yet to see any commonality with the original BASIC
language or any extensions I've seen.

As in most large, geographically extensive companies, yes and no...

It shows.

Macros are useful for, among other things, a) performing repetitive
tasks, like importing data, formatting it and moving it from one
place to another, b) performing automatic operations via events, and
c) extending the capabilities of the application, including
automation of other applications (e.g., producing a word document
based on an XL spreadsheet).

Sure, a) makes sense if you have repetitive operations, e.g. every week you
get new data in some format that needs to be converted to Excel. I don't.

As for b), I guess there are a bunch of applications events that I can
intercept, but I've never seen any reason to do so. This doesn't differ too
much from c), extending capabilities.

What we started out discussing was a method for clearing all hyperlinks
(actually mail-tos) and this is conceptually different.

Excel has a set of controls, simplified as much as possible to be convenient
to and relevant for the general population.

When you told me how to build and execute a macro, in effect you showed me
an alternate set of controls that's capable of much more sophisticated and
detailed control of the application.

My particular goal is to master these controls enough to do what I need to
do.

Believe it or not I simply don't have a need for any of the tasks you've
described as solvable via actual coding in VBA.

It isn't that I am averse to so such solutions or incapable of them. I once
wrote a script in an PC IDE command line environment using mixed Tcl and
custom IDE commands to download binary code to an embedded target board and
re-program a flash memory chip with the data. (This was a technical tour de
force... and a thoroughly perverse exercise. Due to overhead everywhere,
this operated so slowly that it would be almost more practical to dig sand
at the beach, refine new silicon, and build new chips containing the same
data.)

In addition, VBA can extend the functionality of the application -
you can write a user defined function to perform functions that are
not built into XL, and call them as any other XL function.

I've yet to see a function I needed that is not already available in Excel
or one of the add-on packages (e.g. statistics), though I might have
quarrels with implementation details. I could probably think up something,
but only as an academic exercise. Excel is already pretty complete in this
regard.

I no longer ask why - I just lobby to change things.

I recommend asking "why".

Especially asking why you know longer ask "why" in this particular case.

I won't try to give a seminar - there are lots of resources out
there if you have the desire to learn them. OTOH, it probably is at
least as effective to get OJT - learn by doing.

No need, not your job.

yes (though that's only one type of hyperlink)

OK, yes.

OK.

right - the things you can do to it are it's methods.

I suppose I can re-map the term "method" to this meaning. I don't like it,
but ...

yes - the "parent" object.
OK.


right - e.g., the "dot" syntax indicates that the property, method,
or object on the right belongs to the object on the left.

Right. This isn't exactly a very informative syntax, as there is ambiguity
about which item is which. But I can accept it as a form of shorthand.

I'd recommend you send Clippy packing (via the Help/Use the Office
Assistant menu toggle), and use the Help dialog (via the Help/Search
Help menu item, or the toolbar icon) to search - it's more efficient
in most cases.

I've heard that before.

I know what you mean - I have *no* motivation to start learning
about .Net, even though it might possibly be useful for my
cross-platform development someday.

Right. Actually I cite its predecessor as the epitome of a development
environment, and it is a bit jarring to give that up in favor of an entirely
new metaphor.

If you decide you want to plunge in, I can make some recommendations
on good books that are probably starting at about the right level.
Two that I really like, though they're expensive for just satisfying
curiosity:

"Excel 2002 Power Programming with VBA" by John Walkenbach

and

"Excel 2002 VBA Programmer's Reference" by Bovey, Bullen, Green
and Rosenberg

You may be able to find them, or their editions for earlier versions
in your library - anything Office97 or later will do you well -
they're about 90% Mac compatible.

Thanks for the references, but --as I said earlier-- all I want to do is
know how to use the more complex set of controls to do what I regard as
necessary without doing any actual coding.

Thanks,

Henry

(e-mail address removed) remove 'zzz'

 

[J.E. McGimpsey]

Sorry, your reference link didn't seem to make it.

Sorry - got distracted and forgot to paste it in:

http://google.com/groups?threadm=VA.00000b2f.0028c7e5@mara9

Believe it or not I simply don't have a need for any of the tasks you've
described as solvable via actual coding in VBA.

Certainly I believe it - the vast majority of spreadsheet users
don't need much beyond SUM(), and XL has incredible power in its
native functions for the majority of the rest.

I've yet to see a function I needed that is not already available in Excel
or one of the add-on packages (e.g. statistics), though I might have
quarrels with implementation details. I could probably think up something,
but only as an academic exercise. Excel is already pretty complete in this
regard.

I've built hundreds of UDFs for things that XL didn't do, or didn't
do easily, but its mostly been in specialized applications. Macros
can be essential in large applications with relatively untrained
users - allowing automation of tasks that, while each step may be
trivial, the combination is both tedious and error prone when done
manually.

I recommend asking "why".

Especially asking why you know longer ask "why" in this particular case.

The reason isn't that I'm no longer curious or interested in getting
MS to change - it's simply that (a) for many things, especially
since XL is a code base that's been evolving since the first release
(for Mac!) in 1984 - even the original designers, if they were still
around, probably wouldn't be able to remember, and (b) a lot of the
functionality in MacXL is dictated by what WinXL has done, not that
there's been any real change in the basic functions for years.
Fortunately, that's less and less true as MacBU has at least a
limited license to give Mac customers what they're looking for
without being a slave to WinXL. (And WinXL is better for it - some
of the "new" features in Office2003 are borrowed from Office
2001/v.X.)

In this particular case, I guess, I don't ask why because the
solution, it seems to me, has to be trivial, and therefore I lobby
to have it implemented.

 

[Henry]

J.E. McGimpsey:

Thanks for your response on this thread:

Sorry - got distracted and forgot to paste it in:

http://google.com/groups?threadm=VA.00000b2f.0028c7e5@mara9
Thanks!

Certainly I believe it - the vast majority of spreadsheet users
don't need much beyond SUM(), and XL has incredible power in its
native functions for the majority of the rest.

Agreed.

Let me ask what should be final follow-up: The VBA you gave me to clear
the worksheet of all hyperlinks is, I suspect, one of a small number of
useful commands based on trivial VBA programming -- a code "wrapper" to
make the command accessible, no more.

In effect, this VBA code gives us a chance to use a control that might have
been made directly available through the GUI but didn't for some reason
Can you think of any further commands/controls in this class?

For example (see below), only half-joking, if there is a "no-crash"
operator, I'd sure like to know about it. As I've commented earlier in this
thread, it isn't always easy to find what you need using Help.

I've built hundreds of UDFs for things that XL didn't do, or didn't
do easily, but its mostly been in specialized applications. Macros
can be essential in large applications with relatively untrained
users - allowing automation of tasks that, while each step may be
trivial, the combination is both tedious and error prone when done
manually.

I can't argue with that, I just don't need to do it.

About the only time I came close had to do with doing decimal--hex
conversions for some reason I've now forgotten. The built-ins would not do
exactly what I needed.

The reason isn't that I'm no longer curious or interested in getting
MS to change - it's simply that (a) for many things, especially
since XL is a code base that's been evolving since the first release
(for Mac!) in 1984 - even the original designers, if they were still
around, probably wouldn't be able to remember, and (b) a lot of the
functionality in MacXL is dictated by what WinXL has done, not that
there's been any real change in the basic functions for years.

Since you bring it up, I think there's been quite a bit of tinkering with
cosmetics to justify new sales, and perhaps a bit of under-the-hood
adjusting for new OS functions or even a new OS, but --in fact-- not much
has really changed functionally.

As far as not having the original designers around, well, that's no excuse
for not leaving a sensible documentation trail for everything from the
broadest concept (maybe several books' worth) to why a particular minor
adjustment was made (maybe one sentence).

Fortunately, that's less and less true as MacBU has at least a
limited license to give Mac customers what they're looking for
without being a slave to WinXL. (And WinXL is better for it - some
of the "new" features in Office2003 are borrowed from Office
2001/v.X.)

Well, OK, they can do that, but I'm not sure if there is a lot to improve
on.

Other than basic stability. Excel crashes on me about once a day with rather
light use --just simple list manipulation, not even a single function in
use-- and at during I consider to be fundamental operations.

In this particular case, I guess, I don't ask why because the
solution, it seems to me, has to be trivial, and therefore I lobby
to have it implemented.

OK, that seems pragmatic.

Thanks,

Henry

(e-mail address removed) remove 'zzz'

 

[J.E. McGimpsey]

Let me ask what should be final follow-up: The VBA you gave me to clear
the worksheet of all hyperlinks is, I suspect, one of a small number of
useful commands based on trivial VBA programming -- a code "wrapper" to
make the command accessible, no more.

In effect, this VBA code gives us a chance to use a control that might have
been made directly available through the GUI but didn't for some reason
Can you think of any further commands/controls in this class?

A few I use all the time:

Display the entire filepath in the workbook's window header:

Public Sub CaptionFullPath
ActiveWindow.Caption = ActiveWorkbook.FullName
End Sub

Set the Enter key to not move when used to terminate an entry,
leaving Return to move down/to the right/etc. as set in the
Preferences:

Public Sub SetUpKeys()
Application.OnKey "{ENTER}", ""
End Sub

Add an item (in this case Paste Values) to the contextual menu that
pops up when you ctrl-click in a cell (many/most menus and toolbars
are not accessible via the GUI):

Sub AddPasteValues()
CommandBars("Cell").Controls.Add _
Type:=msoControlButton, _
Id:=370, _
before:=5, _
Temporary:=True
End Sub

Enter times with a keyboard shortcut with seconds (CMD-; only does
the minute):

Public Sub EnterTime()
ActiveCell.Value = Time
ActiveCell.NumberFormat = "hh:mm:ss"
End Sub

That last is cheating a bit, since it's two lines, but there's no
way to hard-enter the current time with seconds from the GUI.

Other than basic stability. Excel crashes on me about once a day with rather
light use --just simple list manipulation, not even a single function in
use-- and at during I consider to be fundamental operations.

I don't remember back to the beginning of this thread, but if you
haven't already, make sure you've got Office updated:

Mactopia downloads:
http://www.microsoft.com/mac/download/default.asp

and that you've repaired disk permissions:

OS 10.2.x: Run the Disk Utility (in the ./Applications/Utilities
folder). From the First Aid tab, click "Repair Disk Permissions".

OS 10.1.5: Run Repair Privileges (same folder, or download from
Apple: http://docs.info.apple.com/article.html?artnum=106900

I put XL through pretty strenuous use every day without a crash
(unless I'm trying to use VBA to manipulate the VBE - then I crash
about twice an hour - I'm still working on that...).

 

[Henry]

J.E. McGimsey:

Thanks for your post on this thread:

A few I use all the time:

Display the entire filepath in the workbook's window header:

Public Sub CaptionFullPath
ActiveWindow.Caption = ActiveWorkbook.FullName
End Sub

OK. I've wanted this function in Word, but never had a reason to do it in
Excel -- but I can see why some would want it.

Set the Enter key to not move when used to terminate an entry,
leaving Return to move down/to the right/etc. as set in the
Preferences:

Public Sub SetUpKeys()
Application.OnKey "{ENTER}", ""
End Sub

There's an "enter" key?

Sorry, I know there is; I am simply not coordinated enough to use a keypad.
This makes sense.

Add an item (in this case Paste Values) to the contextual menu that
pops up when you ctrl-click in a cell (many/most menus and toolbars
are not accessible via the GUI):

Sub AddPasteValues()
CommandBars("Cell").Controls.Add _
Type:=msoControlButton, _
Id:=370, _
before:=5, _
Temporary:=True
End Sub

OK. This looks like non-trivial coding -- not the pure wrapper I specified,
but I can see why you would want this.

Enter times with a keyboard shortcut with seconds (CMD-; only does
the minute):

Public Sub EnterTime()
ActiveCell.Value = Time
ActiveCell.NumberFormat = "hh:mm:ss"
End Sub

That last is cheating a bit, since it's two lines, but there's no
way to hard-enter the current time with seconds from the GUI.

Maybe it is cheating, but it is a whole lot more obvious the the one above.
(How would one know to set the ID to 370, for example?)

I don't remember back to the beginning of this thread, but if you
haven't already, make sure you've got Office updated:

Mactopia downloads:
http://www.microsoft.com/mac/download/default.asp

Yes, of course, I am fully updated.

and that you've repaired disk permissions:

OS 10.2.x: Run the Disk Utility (in the ./Applications/Utilities
folder). From the First Aid tab, click "Repair Disk Permissions".

OS 10.1.5: Run Repair Privileges (same folder, or download from
Apple: http://docs.info.apple.com/article.html?artnum=106900

Yeah, well, I do that periodically, and what I've found is that the few
changes made are in truly obscure files -- demonstrably nothing to do with
Office or anything else I use regularly.

I put XL through pretty strenuous use every day without a crash
(unless I'm trying to use VBA to manipulate the VBE - then I crash
about twice an hour - I'm still working on that...).

Your first statistic is mildly reassuring, but your second is certainly not.
("Other than that, Mrs. Kennedy, did you enjoy your trip to Dallas?")

Thanks for all your help -- you've certainly gone beyond the call of duty.
I hope your MVP salary is doubled; no, wait, tripled.

Thanks,

Henry

(e-mail address removed) remove 'zzz'

 

[J.E. McGimpsey]

Sub AddPasteValues()
CommandBars("Cell").Controls.Add _
Type:=msoControlButton, _
Id:=370, _
before:=5, _
Temporary:=True
End Sub

<snip>

(How would one know to set the ID to 370, for example?)[/QUOTE]

IIRC, I placed the command on an accessible toolbar and grabbed the
ID off that.

Your first statistic is mildly reassuring, but your second is certainly not.
("Other than that, Mrs. Kennedy, did you enjoy your trip to Dallas?")

Yeah, the Mac VBE is a big black sucking hole in many ways - fewer
features and much less stable to programming that its Windows
counterpart. However, I don't expect that to change - what # of Mac
users do you think try to fine-tune their VBE menus, toolbars and
window behavior? That number times the full price of Office v.X
probably wouldn't cover the cost of a week of programmer
time...<sigh>. And they've got much better things to spend that
money on (say a Preferences checkbox for automatic hyperlinks?)

Besides, it forces me to code more efficiently, since debugging is
more tedious. And I know that if it works on my Mac, it should work
on my clients' Win machines, too.

Thanks for all your help -- you've certainly gone beyond the call of duty.
I hope your MVP salary is doubled; no, wait, tripled.

You're very welcome.

 

[Henry]

J.E. McGimpsey:

Thanks for your post on this thread:

Yeah, the Mac VBE is a big black sucking hole in many ways - fewer
features and much less stable to programming that its Windows
counterpart. However, I don't expect that to change - what # of Mac
users do you think try to fine-tune their VBE menus, toolbars and
window behavior? That number times the full price of Office v.X
probably wouldn't cover the cost of a week of programmer
time...<sigh>. And they've got much better things to spend that
money on (say a Preferences checkbox for automatic hyperlinks?)

Besides, it forces me to code more efficiently, since debugging is
more tedious. And I know that if it works on my Mac, it should work
on my clients' Win machines, too.

Ummm, sorry, this is just too tempting a target. Please understand, this is
not directed at you personally.

The two environments _should_ be identical with respect to features, syntax,
and stability; at this level of abstraction, the identity of the the host
processor or OS is irrelevant. That's just common sense and how software
works.

Apparently, common sense doesn't prevail when there's a clear commercial
advantage in keeping the Mac side just barely good enough -- as you say,
with fewer features and much less stability.

Granted, there may have been platform-specific hacks in the earlier history
of these products. Tell me the two products were originally supported by
two distinct code bases? There's been plenty of time to remove these
differences, plus the reasons for them should also have diminished over time
and technological advances.

With sensible design, the User Interface --VBE menus, toolbars, window
behavior as well as all the rest-- is a thin veneer over a largely identical
code base supporting the functional "guts". (This consistent with how the
"look" of Office applications has changed with the OS over the past years,
while old familiar bugs outside the UI have remained.) And much of the UI
code can be shared --should be shared-- over all Office applications, so the
incremental cost for any one application is reduced. Most of this has to be
done anyway.

I've heard the riff about the differential between the number of Mac and PC
users. It simply isn't a credible story.

You're very welcome.

Quadruple! You have to span two different environments that should be the
same.

Thanks,

Henry

(e-mail address removed) remove 'zzz'

 

[J.E. McGimpsey]

Ummm, sorry, this is just too tempting a target. Please understand, this is
not directed at you personally.

The two environments _should_ be identical with respect to features, syntax,
and stability; at this level of abstraction, the identity of the the host
processor or OS is irrelevant. That's just common sense and how software
works.

One caveat - MacOS not running ActiveX controls makes MacBU's
options more limited.

Apparently, common sense doesn't prevail when there's a clear commercial
advantage in keeping the Mac side just barely good enough -- as you say,
with fewer features and much less stability.

I honestly don't think that there's *currently* any attempt to
sabotage or otherwise keep the Mac versions second rate. Having met
with many of the program managers, programmers and testers, to me
MacBU seems to be given fairly free rein to develop outstanding
software. The only requirement is that they, like most other
divisions, show a reasonable profit...

That said, there's a long way to go, and resources for MacBU,
however big Bill's net worth, are not unlimited.