Posts by PaulBrown

Thanks for the feast Jack. There's a lot to digest, so I might just fork but a few morsels :

> - Click the attachment hyperlink and Excel will open live in web council mode, you will need > to click file save as in the normal fashion to save as this is for viewing and save if need
> be.
This is what Opera does, but IE loaded the file without requesting confirmation. I am not used to opening an attachment without confirmation, I must be getting rusty with IE having switched to Opera.

> - Second is right click the link and save target as this will save to where you want, and
> name accordingly / required.
This works with both Opera and IE.

>> Does this mean that any macros in the attachment will also be downloaded and potentially
>> active in the IE session?

> This depends on the programmer:
> - if set to run a macro on opening then yes this is correct
> - If set to run on command then no this is incorrect
> - If set to run depending on variable, yes / no depend if variable criteria is met – then yes > this is correct, else no this is incorrect.

Programmer being the gog (guy or gal) that created the excel page - so I cannot affect how it behaves when I open it. Or can this be controlled from [Tools [Options]] ?

Paul.
Fri, 2003-02-14 14:45:54.68 (JD 2'452'685.04)

I am running

Win-2000 Ver 5.0 Build 2195 SR-2
Excel-97 SR-2
Opera Ver 6.0 Build 1010 Platform Win32
MS IE Ver 5.00.3315

I had a look for "Virus" in previous posts but I see none - this seems an obvious question to pose.

This is for info only - intro to the problem
--------------------------------------------
If I download an Excel attachment using Opera (for example at the URL
http://www.ozgrid.com/forum/vi…ttachment&tid=155&pid=729
the download page indicates Unknown size and progress but seems to download correctly.
The "Unknown" indicators were initially disconcerting but I can live with that.
Opera is my preferred browser, and I always save and virus-scan attachments before opening which you will see IE does not do.

Question - potential virus vulnerability using IE
-------------------------------------------------
HOWEVER, if I download using IE (internet explorer), the excel file is opened directly in the IE session.
Does this mean that any macros in the attachment will also be downloaded and potentially active in the IE session?
This particular attachment has no macros so I cannot determine whether this occurs.
Also, since a macro can be invoked by a cell formula, is it possible that an auto-calculate may run on the sheet that gets loaded and so invoke macros without one being aware of this?

Best Regards,
Paul
Tue, 2003-02-11 11:02:04.42 (JD 2'452'681.88)

-------------------------------------------------------------------------------------
If we cannot universally agree on ISO 8601 for our date format, consider the neutral alternative that the Julian date offers!
-------------------------------------------------------------------------------------

CONTAMINATION IS REACHING EPIDEMIC PROPORTIONS!

Quantities of dihydrogen monoxide have been found in almost every
stream, lake, and reservoir in America today. But the pollution is
global, and the contaminant has even been found in Antarctic ice. In
the midwest alone DHMO has caused millions of dollars of property
damage.

Despite the danger, dihydrogen monoxide is often used:
* as an industrial solvent and coolant.
* in nuclear power plants.
* in the production of styrofoam.
* as a fire retardant.
* in many forms of cruel animal research.
* in the distribution of pesticides. Even after washing, produce
remains contaminated by this chemical.
* as an additive in certain "junk-foods" and other food products.

Companies dump waste DHMO into rivers and the ocean, and nothing can
be done to stop them because this practice is still legal. The impact
on wildlife is extreme, and we cannot afford to ignore it any longer!

THE HORROR MUST BE STOPPED!

Dave,

> 6: If you get help from a person, take the time to post back to them with 2 words, "Thank You".

Thanks for your reply - and this is standard in all electronic communication from me.
BTW - you actually need bullet point 7: on this in your terms and conditions.

I posted back before I saw you had replied - sorry we are out of sequence now.

> Welcome to the OzGrid Excel Forum
Thanks, may it be a mutually edifying membership.

> How many of the custom functions do you have in the Workbook?
Probably 10 - 20, all variations on the above theme. The problem only occurs if I invoke that particular function - if I take out reference to it, execution speed for the whole workbook reverts to < 1sec.

&gt; Also, why use a Custom Funtion and not an IF funtion?
Because in cell functions blow up in size when the repeated argument (B4) is not a cell reference but an expression (I very often use long lookup formulae). I graduated from all cell formulae to macros when I saw how much easier it became to manage complex expressions by encapsulating them as arguments to macros.

&gt; It looks like you may be causing your UDF's to loop, so try changing them to:
&gt; If str_test = VbNullString Then
&gt; vResult = str_blank
&gt; SW_Blank3 = vResult
Thanks for the tip, it is typically something like this that will fix the problem. I will try it out when I can get back to the slow workbook.

Last question, why did all the spaces get scrunched out of
REVERSE ORDER
.A.....B.....C....D......E.....F
101....................
102....YES........13....YES....
.......14....YES........14....YES
.......15....YES........15....YES

It appeared as follows in my post

REVERSE ORDER
A B C D E F
101
102 YES 13 YES
14 YES 14 YES
15 YES 15 YES
16 YES 16 YES

My previous post used white space in the indented code fragment and it came through OK?

I went to a lot of effort to convert the TABs that excel generates in the cell range dump, and my lovely formatted text now looks horrible in perpetuity!

&gt; Dave Hawley
&gt; Super Administrator
&gt; *******************

That's a lot of brass stars.
Do all prolific posters get promotion to such levels? (_o^o_)

Best Regards,
Paul
Tue, 2003-02-11 8:11:20.97 (JD 2'452'681.84)

(In case this doesn't add to the correct thread, it is
Problem - Spreadsheet update takes (relatively) forever using a simple macro ?
http://www.ozgrid.com/forum/vi…hp?tid=236&page=1#pid1043 )

Win-2000 Ver 5.0 Build 2195 SR-2
Excel-97 SR-2

I forgot another very useful workaround to speedup calculation by only updating a range of cells - and this spawns a new question.

3) To update a range of cells, mark the range and modify the cells in this range, all cells selected will be updated (ONLY IF IT IS A RECTANGULAR RANGE)

I had only attempted this with rectangular ranges of selected cells before this post, mark the range, modify the cells using

Ctrl-F = Alt-R = Alt-A

which is actually
[Edit [Find {=} [Replace] {=} [Replace All] ]]

This will update only the cells in the range selected.

Then I tried selecting disjoint ranges, and I have found strange behaviour.

In the first fragment, 101 in col A is the cell off which all other cells are keyed so changing this should cause all other cells to change. Remember to set calculate to manual to try this
[Tools [Options [Calculate [Manual]]]]

I changed cell A1 from 11 to 101 in the first test

In the "REVERSE ORDER" case I selected cells as follows (they are indicated as they appear after the update) - these cells have the text YES next to them
Col E : 14:20 Now hold down the Ctrl key to make a disjoint range selection
Col B : 14:20
Col D : 13
Col A : 102

Then I did the "=" -&gt; "=" text change - observe that only the 102 changed

REVERSE ORDER
A B C D E F
101
102 YES 13 YES
14 YES 14 YES
15 YES 15 YES
16 YES 16 YES
17 YES 17 YES
18 YES 18 YES
19 YES 19 YES
20 YES 20 YES
21 NO 21 NO
22 NO

Then I updated the whole page (F9), set A1 to 11 and
I marked exactly the same range, but in reverse order :

Col A : 102
Col D : 13
Col B : 14:20
Col E : 14:20

Then I did the "=" -&gt; "=" text change - observe that 1005 to 1010 in the E column were not updated.

FORWARD ORDER
A B C D E F
11
12 YES 13 YES
14 YES 14 YES
15 YES 1005 YES
16 YES 1006 YES
17 YES 1007 YES
18 YES 1008 YES
19 YES 1009 YES
20 YES 1010 YES
1011 NO 1011 NO
1012 NO

Ideas ?

Best Regards,
Paul
Tue, 2003-02-11 07:47:17.47 (JD 2'452'681.82)

I have the following function that displays two alternative text strings based on whether a control cell is blank or not, a typical use is
= SW_blank3( B4, "N/A", B4 &C4 &D4)

Public Function SW_Blank3( str_test, str_blank, str_nonblank)
If str_test="" Then
SW_Blank3 = str_blank
Else
SW_Blank3 = str_nonblank
End If
End Function

The problem is that this introduces a huge time overhead - everytime the workbook has to update cells, it can take about 15-20s to do so, and on a 1GHz Celeron that is like forever. I have switched to manual calculation updates - so some of the pain is gone, but I end up needing to update (I use F9 for a complete workbook update) quite often during development anyway.

I have found the following workarounds help somewhat

1) A single cell can be updated by simply modifying this cell (re-type last char in formula).

2) When I do need to update the whole workbook, I have found that setting a breakpoint in the first line of SW_blank3 can help. The first time that it executes, VBA pauses at the breakpoint. Remove the breakpoint and then continue (F5) - it runs noticeably faster.

Any ideas?

Best Regards,
Paul
Tue, 2003-02-11 06:57:11.14 (JD 2'452'681.79)