Link to home
Start Free TrialLog in
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Flag for New Zealand

asked on

Excel Zone Expert Discussion, Number 26

This thread is for general discussion about all things related to the Excel zone and its Experts. The Excel Regulars participate here as a matter of course, but newcomers are always welcome. Topics might include:

- Asking for assistance with specific questions
- Issues, announcements, and discoveries related to this zone
- Discussing Experts Exchange features and functions
- Recognition of new Experts, amazing posts, and accomplishments
- Getting to know your fellow Experts
- Planning meets and greets with other Experts when traveling

And, occasionally, a little humor and other completely unrelated topics.

Drivel posts such as "First Post" or "Subscribing" will be deleted. There is a link to the right with the text "Monitor". Click it. It does magical things.

This thread must not be used for any of the following:

- Presenting any specific member in a negative light
- Suggesting that any specific member needs to change their behavior
- Suggesting that others blacklist any Asker for any reason

These topics and any others like them should be taken up privately with the site moderators, all of whom are listed here with their email addresses:

   https://www.experts-exchange.com/communityService.jsp?editVolunteerMenuIndex=1

Previous Expert Discussion: http:/Q_26820718.html

Next Expert Discussion: http:/Q_26923339.html

---

Zone Advisors

When this thread reaches a page size of about 75K (about 150 posts):

1) Create a new "Expert Discussion" thread using this content as a template, incrementing the number in the title, and setting the "Previous Expert Discussion" link to this thread.

2) Edit the new thread and set the points to zero.

3) Edit this question and set the "Next Expert Discussion" to link to the new thread.

4) Make a final post in this thread with a link to the new thread "Next Expert Discussion: http:Q_xxxxxxxxx.html" and accept that post as the solution.
Avatar of rspahitz
rspahitz
Flag of United States of America image

New thread already?  We were just congratulating Sid on his inevitable entry into the Genius club and how he may just overtake Rory as the resident guru by year's end...
rspahitz: I am not overtaking any one...

Sid
I would request experts to refrain from such comments as these comments will only create more friction.

Cheers

Sid
Avatar of zorvek (Kevin Jones)
Sid,

I wouldn't worry about posts in the regulars thread. It's intended for communication between the Excel experts without points and the rules of engagement are much more relaxed here.

rspahitz and Rory will be fine with your overtaking them in one or more point categories.

But if you get near me then I may have to revisit the rules...

Kevin
Teylyn,

You moved to a new thread about 10K too early. These threads are filling up fast and connection speeds for most of us are faster than in the past. I would like wait longer. We are already up to 26 of these threads. Let's see how long we can go before people start complaining. It's not like we need to be quick in this thread like we feel compelled to be in Q&A threads ;-)

Kevin
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)

ASKER

Kevin, I think the above should read "connection speeds for most of us are faster than in Kiwiland". Or we're having a particularly slow network day. It took ages for the old thread to appear on my screen. :(

The 75K rule from the boilerplate text at the top must be wrong, though.  Even this thread is already at 175 K, according to FF's page info.
I think you have some other issues. When I load this page with FF 2.6.13 and get the page info I see 29.42 KB (30,123 bytes).

The previous page is at 66.81 KB (68,416 bytes).

I'm going to resubmit a feature request to EE to implement a native regulars blog/thread that has paging capabilities.

Kevin
Are you using the expert skin?
I never knew that Richard. Even though I love fx more than any other browser. :)

Sid
FF...Fx...OCD?
This is most weird. Yes, I'm using the Expert skin.  I'm running FF -- ehrmmmm I mean Fx 3.6.13

 User generated image
If I view source and save to disk, the html file is 218 KB, the same size I see in the page info box.

Anyone have any idea why that is? How come a page is one size for Cyberkiwi and another size for me?????
Odd. When I save the html I get a file size of about 220K as well. But it remains about 30K in the page info dialog.

Kevin
I thought gmail was doing something new with subject lines, then it clicked...

Is there a reason why this question is titled thus?

"Title: Excel Zone Expert Discussion, Number 26"
Could someone with Fx 3.6.13 do me a favour: Please open the page source, save to disk and attach. I would like to compare the file contents with mine and see where the difference is.

cheers, teylyn
>> Is there a reason why this question is titled thus?

Yes.

I stuffed up the copy and paste. Fixed.
I think that the difference are the javascript and CSS files. They are not included in my download but are included when I save the html. Is suspect that they are also included in your saved html and so the files will be similar.

I think the problem is that you are constantly reloading the javascript and CSS files.

Kevin
teylyn - you're not on EE while working are you? :)

Solution : maybe you should tap into the collective knowledge of the Experts on .. EE, ask a question?

Anyway, it looks like your network proxy has disabled gzip/deflate between your workstation and EE.

http://forgetmenotes.blogspot.com/2009/05/how-to-disable-gzip-compression-in.html

If I disable per the link above, I get ~277kB
(re the last comment)

Lucky you have last years's top EE expert to answer your unborn question...
Of course I'm on EE while working. How else would I solve all my technical problems?

That seems to be the explanation, cyberkiwi. I'll talk to our network guys and cash in a favour.

Thanks
I like the compression idea better ;-)
Yup, seems our guys are running a version of WebMarshal that does not support gzip compression. So, I'll better not rely on thread size readings at work until they upgrade to the latest version.
Just a note for all while I'm no longer the mobile poster Addict -- my connection is a laptop with  a tethered cell in the boondocks. My connection is showing coming out of Missouri and I have 1.9-2.4 MBps down and .28-.3 upload. That's before you consider I'm actually in Ohio.

I wonder what the rest of the world looks like.
In this CSV import-into-SQLServer question, it looks like Access was the preferred work-around, rather than Excel.

https://www.experts-exchange.com/questions/26853722/SQL-Import-Comma-Delimited-with-double-quotes-in-value.html

I suggested Excel2007, but the OP used Access to import a CSV file where one of the fields had embedded quote characters.  The sample data below imported correctly in my Excel2003, so it was primarily the row limit.

========
Has MS has fixed some of the Excel CSV shortcomings in 2007/2010?
36,235223,"OREGON-ZEUS MINING CO., INC.",1,"","","","","","",""
123308,346608,"Lawncare LLC.",1,"","","","","","",""
123312,346640,"John C, Reilly, M.D.  ""A Professional Corporation""",1,"","","","","","",""

Open in new window

Is Excel's date calculation wrong?

Enter these:             to get these

=DATE(1901,2,29)   3/1/1901
=DATE(1900,2,29)   2/29/1900

Anyone know what's wrong with this?

The answer is in the first paragraph here if you want to check your answer: http://en.wikipedia.org/wiki/Century_leap_year
Interesting. I first noticed this when I used to work with QuattroPro, which correctly identified 1900 as having no 29th day of February.  I went back and checked Lotus 1-2-3 and saw that the two were inconsistent.  I never thought to go check Excel's calculation.  however, I did check the calendar control in VB and it correctly ignores Feb 29, 1900.  That would make an interesting conflict if you tried to work with older dates.  But since it's corrected for 2100 in Excel, that's probably a minor issue.

Thanks ! :)
Fx was taken years ago for foreign exchange. So ff works fine for me Teylyn.

Although calling multiple instances of firefox ffs might trigger funny reactions from some
To all the MS MVPs participating at the Summit right now:

Can you please relay this message to the Microsoft Office development team(s):

Could you possibly be so kind and co-ordinate your various development teams, so that features available in Word can also be found in Powerpoint and Excel. For example:

Take a screen clipping in Word 2010 or Powerpoint 2010 and you can right-click the clipped image and save as picture. In Excel 2010 this option does not show in the context menu. ***Why not?***

Word 2010 features a rich text content control on the Developer ribbon, but Excel 2010 and Powerpoint 2010 only have the old (yawn!) Forms and ActiveX text field controls. That is so last century! There is no rich text content control for Powerpoint (!!!) or Excel. ***Why not?***

Do you teams talk to each other at all when you come up with the features of a new release? This looks very much like a silo situation. Could you please arrange some brown bag lunches across the dev teams so they can share the features to be included in the next release?

It would be most desirable to have these standard features available in all Office applications, instead of just a few. It's really confusing and not very comprehensible to see features in one app, but not in the other.

Is it not possible to get all development teams on the same page and get the apps to do the same things, like have the ribbons display the same controls across the basic Office apps?

Mind you, this is only about Word, Excel and Powerpoint. Let's start small. Once these three act like a team, it's time to tackle Visio, Project, etc.

Rant over.
>>"Although calling multiple instances of firefox ffs might trigger funny reactions from some"

In FF forums, do they use RTFFM? ;)
fx 3.6.14 is out

Sid
Teylyn...great idea.
And in the VBA word, can we get the tool boxes that same? I often use the calendar control in Access and want to use it in Excel form but it's not there.  I'd also like the "..." button there to add more tools, but I don't even get the same Developer tab in Word and the "Insert tool" option is a tiny thing, hard to find, and they're not even listed in the same order.
Oh, and how about a macro record for PowerPoint and Outlook? Pretty please?
I believe the Calendar / Date time Control doesn't work in 32 bit vista/win7. It does though in 64 bit. I faced this problem few months ago. I searched Google but didn't get an answer. I even emailed MS Support but never got an answer.

Sid
Sid,
I'm not aware of any issues in the 32 bit world (64 bit Office is a whole other issue though!) - what was the problem?
Rory
Sid,
So is it a 16- bit app?  Because I use it in Access (although it's definitely a bit flaky so that might explain a few things...)

I can't wait for the migration of the whole VBA module over to .Net--scary but so much more powerful.
Rory: I was not able to register the MSCAL.OCX or the MSComCtl2.ocx in Office 2007/2010 - Vista/Win7 32 bit systems. It gave an error each time. I was able to register them successfully in 64 bit by copying the above files to c:\windows\sysWOW64\ and then registering them.

rspahitz: What OS and which Office version are you using?

Sid

BTW I wanted to use those controls in Excel.

Sid
Did you have UAC turned off?
I am not sure or I don't remember to be precise. Let me restart in win 7 and re try it now

Sid
Sid, when I had trouble with the cal control, I was on Windows XP with Office 2003
Now on windows 7, 64-bit with Office 2010.  I don't even see the Calendar control any more!
@Rory: Nope. No Joy. Snapshot attached.

Other Details
MSOFFICE 2010
OS: Win 7
UAC: Turned Off
32 bit system

rspahitz: You need to place the MSCAL.OCX in c:\windows\sysWOW64\ folder and not system 32 folder and then register it. Here is one link which might interest you.

http://social.msdn.microsoft.com/Forums/en/sbappdev/thread/91cf3127-70fe-4726-8a27-31b8964430c5

Sid

Sid
Untitled.jpg
And I think you might want to see this as well.

Sid
Untitled.jpg
Did you run cmd as administrator and then regsvr from there? Where did you get the OCX from - MS?
>>> Did you run cmd as administrator and then regsvr from there?

No. Because, this is my laptop and I am the administrator. Do you suggest I should try that?

>>> Where did you get the OCX from - MS?

I am not sure I remember.

Sid
Yes, I would give it a go.
I have Win7 32 bit on my laptop, so I will have a crack at it later on if you haven't got it sorted beforehand.
Actually, no I don't - I changed it to 64bit - but I think I have a 32 bit VM somewhere.
Also would you suggest me a link from MS where I can download the MSCAL.OCX just to be sure that I have the right file?

Sid
>>> Yes, I would give it a go.

Rory: Ha Ha!!! Have you ever considered taking over Microsoft Support!!!!

Hurray! It works :)

Thanks Rory. As usual, you are the best ;)

Sid
I don;t know if they still make it available on the website, but your file details look correct based on the one I have.
Glad it's working! :)

Just be thankful you aren't running 64bit office...
I have heard there are lot of problems. I was especially concerned with the use of API's in 64 bit. But I believe there is a workaround.

Time to get myself a 64 bit system! :)

Sid
APIs are easy enough - it's the ActiveX controls that are the problem. Mind you, I find it hard to believe there are actually that many people that need 64 bit office!
As a part time freelancer, I had my share of building 64 bit apps. I get one such request once in approx two months. :)

If I get stuck now, I know whom to trouble ;-)

Sid
>>>it's the ActiveX controls that are the problem.

I see what you mean.

http://msdn.microsoft.com/en-us/library/ee691831.aspx

Sid
Interesting scenario

Situation: Two friends standing near a window, facing a building right in front of them. The first Guy sees a beautiful chick and says to his friend. "Do you see that beautiful Chick?" The 2nd Friend replies, “where?” How does the 1st guy respond?

Many Scenarios.

If first guy is Barry
Barry: "Right there, if you see =OFFSET(OppositeWindow,-1,-1,FrenchWidowsHeight,FrenchWidowsWidth)

If first guy is Rory
Rory: "Read the MSDN's article. It will tell you which window! And if you read it carefully, it will tell you the version as well!"

If first guy is Kevin
Kevin: Considering the fact that the opposite building is 100 mts in height and each floor is of 10 meters and the window is on the second floor so I can say that the window that I am pointing at is approximately 20 meters from the ground. You might also notice that from where I stand, the window is on the left hand side so the conclusion is the window is on the left hand side and approximately 20 meters from the ground.

If first guy is Tommy
Tommy: Do you see that window on the left hand side which is 3mts X 5 meters? No? Well, if you use Pythagoras theorem then the window is at the height of approximately 20 meters from the ground. Let me know if you want me to give you a sample on how I calculated that.

If first guy is Aiki
Aiki: I have moved this thread to the right section. I am sure someone will definitely tell you where that window is.

I wonder what would be Dave’s, Richard’s, Teylyn’s, Patrick’s, rspahitz’s response be like if they were in the same scenario.

Sid
> I wonder what would be Dave’s, Richard’s, Teylyn’s, Patrick’s, rspahitz’s response be like if they were in the same scenario.

Void as the chick has been obscurred by a flurry of activity
- Sid making 13 approaches in rapid-fire succession within 5 seconds of said chick appearing.
- Kevin suggests Sid should refresh :)
- the other Dave (dlmille) has wandered by a little later than Sid and appears to be debating with himself as much as addressing the chick
- Patrickab has asked for a sample
- some random guy with a vbscript tag is whingeing that the chick should have been standing at another window to begin with and anyhow its unfair that he never gets A grade chicks at his window

And finally Telyn and Tracey assail Sid (deservedly so) for making an inappropriate comment.
I use 64 Bit office and strain the max out of a Quad CPU modeling with Excel, yea!

Dave
I would immediately LOOKUP. And while I may very much desire an ADDRESS to CONCATENATE, I would be sure to use PROPER text--lest I receive the ego-killing #SAND! error message.
>>>> Kevin suggests Sid should refresh :)

Embarrassed :-/

Sid
I just caught up -

Are we all that transparent? lol

Dave
I don't do chicks. Get me an attractive guy or a charting problem to raise my pulse.
>>>Get me an attractive guy or a charting problem to raise my pulse.

There you go. You get both. :)

Chick Replaced by Attractive Guy
Building = Bar Chart
Task: To determine X,Y co-ordinates of the window and explain it to your friend.

Sid
That depends on the gender of the friend.

If the friend is female, reference an offset from a jeweller or clothing store.
If the friend is male, reference an offset from a liquor store or a pub.

If the friend is an Excel geek, we'll overlay with a XY scatter chart that pulls Google map data, and uses the camera tool to create a dynamic image that shows the current position of  the attractive guy even when he moves.

I just sit back and admire how everyone can come up with brilliant solutions to "score points" with a chick.  Nerds have come a long way!
>>>If the friend is female, reference an offset from a jeweller or clothing store.
If the friend is male, reference an offset from a liquor store or a pub.

lolzz...

>>>I just sit back and admire how everyone can come up with brilliant solutions to "score points" with a chick.

That not what the scenario is. The scenario is to describe the window location ;)

Sid
On re-reading the above responses, I just realized that I somehow missed byundt's comments.

The idea is not to CONCATENATE but to FREEZE on the Window location. :)

Sid
In the old ExpertSEXchange data base, you probably will find a lot of different solutions or even an article about "good looking girl/guy in a window location" questions.
Is EE up ? I am having problems in attaching a file and the pages are loading very slow.

Sid
Anyways after 6 attempts, I was able to upload the file.

Sid
aaahhhhh oooo yeeeessss.  E-E is UUUUPPPP!!!! let the adrenaline floooowwww - yes....

The life of an E-E addict...

Dave
Gang,

Please welcome Zack Barresse, aka firefytr.

Don't let the low point total fool you; Zack's been an Excel MVP for a few years now, and is one of the founding members of VBAExpress.

Patrick
One of the surprises when I met the Excel MVPs in 2008 was the generally low respect given to the Experts Exchange web site. In a nutshell, they are bothered by the pay barrier and don't believe that there is any unique or valuable content behind it. Since then, Patrick has been persuading some of these people to pay a visit to the site and gain first-hand knowledge of its quality.

Perhaps as a result of Patrick's marketing, I heard no such negative comments this past time. They may still have issues with the pay barrier, but they also realize that there are some excellent people here trying to answer questions, and that betwixt the hundred or so routine SUMPRODUCT questions there might be a few gems.

Brad
@Everyone,
I'm back but probably part time only... But thanks to everyone who post here as I get a smile in my face when reading the threads.

@Brad
The first and second generations of MVPs were built around MSFT public Q&As. At that time all the web based Q&A forums was viewed as second class forums by the MVPs. I would say that many of the MVPs at that time were highly arrogant... I really like seeing that MSFT nowadays also recognise that it exist highly skilled and knowledgeable individuals around non-MSFT Q&As as well. I believe that You and other long time members here have actually done the job Yourself to remove any barrier around EE by being part of the MVP community representing the EE community. So once again diversity plays a critical role.

All the best,
Dennis
Sid wrote:
Situation: Two friends standing near a window, facing a building right in front of them. The first Guy sees a beautiful chick and says to his friend. "Do you see that beautiful Chick?" The 2nd Friend replies, “where?” How does the 1st guy respond?

Many Scenarios.
...
I wonder what would be Dave’s, Richard’s, Teylyn’s, Patrick’s, rspahitz’s response be like if they were in the same scenario.
--
Rob (oh RSpahitz to the EE world) would say: "She's right there next in the Office next to Window 7, which is next to the Vista and a bit down from Windows 98, 95 and 3.1...Pretty Excel-lent lady, huh?" (moan, groan, ok, ok, I'll stop)
All bad. So lets keep going

I wonder if she AsEasyAs 1-2-3?
Hi Sid,

Congrats on getting the Genius rank. I just noticed that....


- Ardhendu
>>>> Congrats on getting the Genius rank. I just noticed that....

Yes on the 3rd March.

Thanks Ardhendu :)

Sid
I just saw that the Excel Dashboard didn't get updated? Is it because of sire maintainance?

Sid
oops, I meant Site Maintainance.

Sid
Thanks Modalot :)

Sid
Well played Sid, congratulations :)

Dave
Thanks Dave :)

Sid
Modalot: Still showing the 5th figures.

Sid
Actually, I'm glad that the site is up at all. I typed out 2 printed pages worth of step by step instructions, hit Submit and -- Wham -- Offline for maintenance. Duhh.

Note to self: Always copy the content of the comment box before hitting Submit.
If it's more than a paragraph or two, I tend to write it in Notepad to start with. ;)
>>>I typed out 2 printed pages worth of step by step instructions, hit Submit and -- Wham -- Offline for maintenance. Duhh.

teylyn: See if this helps. I just created it on the fly for you :)


Sid
My-Text-Editor.zip
untitled.bmp
Let me know if you want a "Save" and "Open" button as well :D

"Save" will let you save the text to a text file so you may want to resume typing at a later stage :)

Sid
Sorry, Use this file. The above doesn't didn't have scrollbar activated.

Sid
My-Text-Editor.zip
...why can't the browsers recognize that you typed in some text and if you click the back button it restores it?  This is probably one of my biggest complaints about this whole cloud computing: instability (whether caused by network problems or maintenance problems or whatever.)
Why should you have to go out of your way to do 2 steps when the tools are designed for 1 step, just on the slim chance that something out of your control will fail?  It's like stopping at every green light and checking for traffic just in case some idiot decides to go through a red light on the opposite side!

Hmmm...if there's no plug-in for that, I may have to go and finish building my customer browser to handle that...where's my Excel toolbox :)
Thanks N for the update.

Sid
Does anyone remember Excel 95 and the flight simulator module?  Anyone remember how to get into it?
sorry....wrong thread... ignore that :)
FYI, N.  I use Firefox and now mostly Google Chrome and still have the problem of disappearing data (although it's better.)
Thanks for the update NM. Mind you, I'm not complaining. I'm aware of the maintenance work and know that you're all working your fingertips off!

Re Firefox: I never get the screencast to work with Firefox. But I'm happy to flick over to IE when needed.
teylyn did you get a chance to try ID: 35068920 and ID: 35068976?

Sid
Not bad Sid.  I'm working on a nice version that supports the full w3c standard for HTML and has a Javascript builder as well as CSS module, with built-in intellisense.  (Obviously not a simple task so it'll be a while to complete it, especially in my limited spare time.)

Hey...can you add a feature to push the text into the nearest web browser window?  
What language did you build it in?
>>>>Hey...can you add a feature to push the text into the nearest web browser window?  

Yes. That is easy. I used vb6.

Sid
vb6 eh?  so we can push the code into the PERSONAL workbook and have it load on demand from excel :)
It's vb6 and not vba. And yes,it is possible using vba s well :)
From what I've found, the only real difference between VB6 and VBA is that you can't create an executable.  They both seem to use the same VB library.
Sid, I'll have to try this from home. My company firewall does not allow executable downloads, even if Zipped. From the looks of the screenshot, though it must be a very cool app.
rspahitz: Yes, you can’t create independent executable file in VBA as you can with VB6. However in VBA the code runs under the control of a parent application. E.g. Word or Excel.

VB6 executables run independently. The objects are not same but similar. E.g. The forms collection  you see in VBA and VB6 are different, though they have been made as  similar as possible. You can still see some properties in one and missing in other. The VBA has specialized well structured objects for its parent application, while VB6 is a general purpose programming language. E.g. in excel you find Worksheets, WorkBooks, Range, Cells etc. all ready for use with you.

VBA is basically customization of the parent application. You don’t have full control over it. Only the things exposed to you can be programmed. :)

teylyn: Sure no problem :)

Sid
Thanks Sid.  As I recall, both VB6 and VBA use the same "Visual Basic for Applications" dll.  the other parts are auxiliary.  For example, in Excel VBA, there are Excel forms, in VB6, there are Windows forms; that's why they're different; their source is different.  You should be able to load the Excel forms from VB6 (by tapping into the Excel engine) and probably grab the Windows forms if you can find the proper Windows library dll.

And you're right about who controls the pieces.  That's why I call VB6 "VBA for Windows" :)
>>>As I recall, both VB6 and VBA use the same "Visual Basic for Applications" dll.

No they don't. They don't inherit from a common base. The code base for them are different. VB6 is a programming language and VBA is a scripting language.

Sid
OK thx.
Maybe that's why they've had so much trouble migrating VBA to the .Net framework.
Actually that is not the case. They couldn't migrate Vb6 either. The .NET has been built from new base.

I feel they didn't touch VBA because there was not a desperate need for that. Almost everything needed in VBA (remember it is a small scripting language and not full fledged language) is already there.

Sid
Maybe, but I had heard back in 06 that they were going to implement DotNet into Office 2007 so I'm not sure what happened.  Also, VBA/VB6 is becoming outdated with obsolete controls so at some point they will have to figure out how to manage that since people won't want things that look last century :)
>>Maybe, but I had heard back in 06 that they were going to implement DotNet into Office 2007 so I'm
>>not sure what happened.


What I surmise happened is that in the course of getting .Net into Office 2007, at the same time they were also completely rebuilding the UI with the Ribbon, AND completely rebuilding how charts work, AND completely redoing colors and themes, AND greatly expanding PivotTable functionality, AND completely revamping Conditional Formatting, and whatever other crap was happening in Word, Access, and Outlook, not to mention getting VS2008 ready to ship, somebody at Microsoft realized there was a whole lot of stuff going on, and they couldn't do all of it :)

Replacing VBA with <insert .Net language here> is not a step to be taken lightly, and while many of us have been waiting for it for a long time, I for one hope Microsoft will be quick, but does not hurry.

VBA may be a bit dated, but it works, damnit :)
VBA is a specific library set depending on the starting app (Word, Excel, Access, etc.) which ones are defaulted to be used. However via late binding or early binding (<alt>+<F11> --> Tools --> References --> Select from list) that can be changed.

More than once I've added the generic MS Office reference to my Access DBs. Less often I have added Word, Excel, etc. as a reference. But it is easy to do.

VB6 gives you the opportunity to build you own library, use the default VB6 library,or use the default ones from the Office suite, or all of the above. The issue with VB6 is knowing that library is loaded on the target machine(s). So that is why you have the various installer packages. And various versions of within a generation can cause issues.

That can also be an issue with with an Office app as well. I had an Access DB that used the mscal.ocx (Calendar control). It wasn't loaded by default in Acc97. I built a routine that checked for registration, if not copied it from a public share and registered it.

It is always about what you "grew up with".
To the best of my knowledge VBA and VB6 do share a common code base. VBA has nothing to do with the application it is running in, which is why VBA itself does not change from app to app and also why you can build dlls from VBA (if you have a developer edition of Office (pre 2003)
To the best of my knowledge VBA and VB6 do share a common code base. VBA has nothing to do with the application it is running in, which is why VBA itself does not change from app to app and also why you can build dlls from VBA (if you have a developer edition of Office (pre 2003)
I agree with rory here. It's also true that VBA is getting old (launched 1993) but still it's a strong platform for Office development. VB6 is still in use but I have the impression that it has lost its position and been replaced with .NET. But neither of them, VBA & VB6, should be considered as "dead" although MSFT no longer support VB6.


I thought everyone had their say in this question:
https://www.experts-exchange.com/questions/26782557/Expert-Discussion-VB-classic-zoning-for-VBA-questions.html

Do I need to reopen it?!?

The language definition of VB6 and VBA are the same.
Yeah, I guess I re-launched that :)  I love programming VB6/VBA, but also enjoy my time in VB.Net, although some of the things the migrated are simply painful compared to the old ways of doing things.  Of course, other things are far better so often worth the upgrade.
re http:#a35013814: Our network guys have upgraded WebMarshal and gzip compression is now allowed. This site now loads fast and this page currently clocks in with 72.32 KB.

So, if anyone feels the pages are too big and loading slow, check if your network firewall/whatsit allows gzip compression.

cheers, teylyn
Cool. Page size matches mine.

Let's let it go to closer to 100K this time.
100kB ~ 10 days at this rate of chit chat... so that's #70 by end of year? Looking forward to that pagination feature Kevin's asking for
This is why I think it is a good idea to keep the idle chit chat and banter type posts in the Excel Fun thread http:/Q_26866259.html

Then this here thread can be used for the more serious things like the first three or four items in the bullet list at the top.
Anybody can figure out how to do the maximum value of a column of dates as text without an array formula?

https://www.experts-exchange.com/questions/26875028/Minimum-and-Maximum-Dates.html

Thomas
I'm pretty sure it's impossible to do it in a single cell without an array formula. I'd love to see Barry prove me wrong on this, but I don't think even he can. Since the asker posted an xlsx file though, he must be running >=2007 and can use A:A so his worry about needing to update the formula should be satisfied.
At one stage in the creation of the formula, I got an error with the full column and then I probably stuck with a limited range too long.

Thanks for your input Tommy.

T
How'd you even find that question anyway? I would have said something, but Cactus usually puts stuff in the right zone. Anti-Spyware? Really?
yeah, the only way I can see to do it in a single cell is with a custom function.
I got it from a related question link, the original question was posted in the excel zone. I had an array formula and barry came in with a lookup and took it home.
I've added the Excel zone to that question.

A few minutes later, Cactus posted this: http:/Q_26875028.html?#a35100259

:-)))))
Application.WorksheetFunction
Ahh, the numpty factor. May have to wait til tomorrow for Barry.
Prayers out to all those affected by Japan's 8.9 earthquake and all those in the line of the ensuing tsunamis.  Do we have any EE experts from that area?
seeking help:
https://www.experts-exchange.com/questions/26879503/create-column-to-denote-leg.html?cid=1572&anchorAnswerId=35110663#a35110663

Author doesn't seem to want to offer much additional info about request and I can't figure out what is wanted.
EE issue here, confirmed by barry. Formulae copied from the comment boxes into excel replace spaces by non-space spaces (char(160) according to barry).

Is that something that's being addressed?

Thomas

It copies fine for me. I notice that sometimes the question contains the non-breaking space, especially when someone has posted data from a sheet into the question box. Then the non-breaking space is entered instead of a space to pad out the spaces to the next column. HTML will not show more than one consecutive "normal" space, so padding with spaces is forced with non-breaking spaces instead.

The following table was copied and pasted from Excel cells. It will contain non-breaking spaces, shown as &nbsp; in the HTML source code

text      number
a and b      1
c and d      2
e and f      3


The next line is a formula. In this, the spaces should be a real space:

=VLOOKUP("c and d",A7:B10,2,0)

If you use Firefox, select just the contents of this comment, right-click and use "View selection source". You'll see the HTML with syntax highlighing and the &nbsp; will jump out in red.

In the question you linked to, the formula contains other invisible tags, namely the <wbr> tag, which is normally used to denote where the browser can insert a line break, if required. This tag does not get copied across to Excel in my tests, though.
Is it me or the EEples look different? Thee shields are missing

Sid
A better question is why are you not using the Expert skin? Are you not an Expert?
>>>A better question is why are you not using the Expert skin?
Aesthetically I find it "Premium" skin more appealing.

>>>Are you not an Expert?
It's not necessary that experts should only use Expert Skin. :)

Sid
Shields are missing in expert skin, too, when you look at a member profile.
Forgot the brackets.

Aesthetically I find it ("Premium" skin) more appealing.

Sid
Anyone familiar with configuring parts of Excel's settings when an Add-in is installed?

https://www.experts-exchange.com/questions/26885244/Excel-2010-VBA-ActiveX-DLL-and-Ribbon.html
Further to my post in ID: 35114028, I am sure the relevant dept is looking into it but just to flag it down.

Two Updates:

1) Shields are still missing.
2) Scores not getting updated in the Excel board. It was getting updated till yesterday.

Sid
Did they remove the shield on purpose so askers wouldn't give preference to higher ranked experts? That seems silly, but also seems like something they might do.
>>>Did they remove the shield on purpose so askers wouldn't give preference to higher ranked experts? That seems silly, but also seems like something they might do.

I doubt it as the asker can still view the profile.

Sid
Modalot, thanks a lot for always getting back :)

Much appreciated.

Sid
I'm stuck and would appreciate help in http:/Q_26888462.html  This is a question on retrieving the font and fill color on the active worksheet when they are probably set by Conditional Formatting. The Asker has Excel 2007. If he had Excel 2010, the solution would be dead simple (see the thread).

In http:/Q_26860897.html Dave (dlmille) modified some code for Excel 2007 that I had posted in VBA Express (for Excel 2003). I then failed miserably trying to apply it in Excel 2010.  My copy of Excel 2010 is very unwilling to return properties of a FormatCondition when it is a "value between x and y" type of criteria.

The code ought to work as-is. It might be a bug, or it might be corruption of my Excel 2010 installation. Either way, I'd like to know.

Thanks to everyone who takes a flyer at it.

Brad
Brad - what a bear.  I posted a solution which should work, but its for sure not the general purpose solution and testifies to the FACT that all information on formatconditions is not readily apparent.

It did turn up one condition I hadn't accounted for in modifying your original code which I added (the case of CELL_VALUE OPERATOR VALUE, which is now handled).  What a test of the logic, given the convoluted settings in the conditional formats!  Fortunately, there was SOME method to the madness, and by (LUCK?) tripping to the LAST good evaluation the conditional formats ended up resolving correctly.

whew!  Not one to brag on - a definate patch - but should work for this "one off" solution - we'll see and hopefully the OP has lots of spreadsheets to run this on because I could have probably manually formatted in the time it took to write the solution, lol.

Another good collaboration, I think.


PS - as I still have my old library of Office, I'm planning on installing multiple virtual machines with each (Excel at least) so when I move to Excel 2010 (about to), I can still test the older stuff.
Dave
Dave,
Virtual PC on my Dell laptop is dog slow. I use it only for beta software where you must completely uninstall the beta when the production release comes out--I just wipe out the virtual machine instead.

For regular versions of Office, I install them all in chronological order in different folders on the main instance of Windows.

Virtual machines on a Mac are fast (I'm running Parallels v 5). I have Excel '97, 2000, 2002, 2003, 2007 and 2010 all running in the main instance of Windows XP on my virtual machine plus Excel 2004, 2008 and 2011 on the Mac side (native).

Brad
Thanks for that.  I'm on a Quad and seems to be OK but I've not really put it to the test (trying out VMWARE now).

Dave
Dave,
On the Conditional Formatting thread, I wonder if the better solution might be to remote into a box that runs Excel 2010. That way, you only need one of them.

Brad
Not sure.  I've fixed the memory error issue.  It was associated with many, many calls to the Excel 4 Macro Application.Evaluate and I've written my own evaluate function using Defined Names.

Its working from a macro, but I want the worksheet function to work as well, so I'll choose to use the Defined Name to evaluate if running from a macro, or Application.Evaluate if running as a worksheet function.

My quandry - how do I tell who called the function?  Is there a way in VBA to know whether the calling app is a worksheet function call as opposed to being called from a macro?

Dave
Well, never mind on that, except for curiosity.  Its easy enough to add an optional parameter to the function to inform if run from a macro, lol.....

Dave
If typename(application.caller) = "Range" Then

Probably need error handling round it too. :)
Folks,

this Q "extract values from embedded form controls" -- http:/Q_26895692.html -- should be something you VBA mavericks do with your left hand only. Can you have a look, please?

cheers, teylyn
Help!

Context: https://www.experts-exchange.com/questions/26891751/ActiveWorkbook-SaveAs-saving-as-FALSE-when-selecting-No.html

Request: Open your various versions of Excel and tell me what this produces in the Immediate window:

   ?CStr(False)

I am only interested in non-English WRS systems.

Kevin
It is translated (German: "Falsch")     (Excel 2003, 2010)
It's my understanding that if you treat it as a string, it's usually translated but if you treat it as a boolean it's always the English spelling since it's just the keyword False.
I need the Excel version in addition to the results. The more versions the better.

>It's my understanding that if you treat it as a string, it's usually translated but if you treat it as a boolean it's always the English spelling since it's just the keyword False.

What does this mean? Examples?
Wayne, Dave and Teylyn: Do you yahoos speak English down there or just drive on the wrong side of the road? What is Aussie or Kiwi for "False"?
If you compare the result to "False" then you will only get correct results if you are using English but if you compare them to False then it will always work.
e.g.
If someFunction = "False" 'volitile
If someFunction = False 'stable

Since (assuming the function returns a string) the boolean will be promoted to the language dependent string that the function is returning, it will always match.
The keyword "False" never changes between languages, just like the keyword "If" never changes.  So the boolean value for 0=1 is False.  However, CStr(0=1) will be "False" for English and other text strings for other languages.

One other thing I've seen (many years ago and probably still current) is that the settings vary based on (1) installed language; (2) Windows language setting.
For example, if you install the German version of Window, things are "natively" in German; if you install the English version of Windows and use the German language settings, things are Natively in english and translated to German as needed.  Often they are the same but not always.
I'm not sure if this applies to the "False" discussion.
It's a programming language thing. The = operator is defined to work if you send it two ints, two doubles, two strings, two bools, etc. But it's not defined to work on a string and a bool. If you send it something it doesn't have a definition for, it looks to see if it can promote one or both until it has a definition. Since VB defines a promotion for bool to string, when you send it one of each, the bool is promoted to a string using whatever the regional specific version promotes it as. But since the return value of the function was promoted the same way, it always matches.
OK, OK...Let me try this again...

The problem: I can't find reliable and consistent information on what the CStr function returns when passed a Boolean.

The request: Open your VBEs in your various Excel versions and tell me what you get when you enter "?CStr(False)" in the Immediate window. I am only interested in non-English WRS systems.

I don't need subjective analysis and assumptions. I have plenty of that already. It's time for some hard core empirical evidence.

Kevin
See http:#a35168000 (added versions). It IS translated, not matter what "they" say.
A boolean expression in a Excel cell is translated, too.
Anyone with 2000 or 2002?
Swedish version 2000/2002/2003/2007/2010: Falskt
Thank you sir! The issue is now closed.
Guys/Ladies,

I'm interested in your opinions on either (or both) sides of this "who owns the code" debate, and normal commercial pratices when comissioning code

I have put a person in my company in touch with a code provider to create an addin for a particular chart.  The price quote was relatively hefty (under 4 figures for what might be 3-4 hours work), but I figured it worthwhile on my belief that
- my employer would own the code (my experience some years back on Rentacoder)
- as such it would not be protected
- we would have unlimited usuage
- and it would not be available to anyone else
- there would be future support for xl15 etc

I reverted on three of these points (ie querying whether this contract did mean my employer would own the code for our exlusive use, and the ongoing support)

The response was polite but interesting

In short the thrust was that the rate quoted was below cost on the basis that the addin would be sold to other users later (a secondary price wasn't mentioned). And that for exlusive use the addin cost would cost us10x as much.

So for option 1 the initial construct seems a little unfair to me in that presumably any later users would buy the addin on a cheaper basis (the programming cost being sunk)
And for option 2 (exclusivity) I'm not going to let my employer pay a substantial 4 figure sum for a few hours work

So what is "normal" from a buying/developer perspective?

Cheers

Dave
@Dave

Laws differ by country and region, so I'm speaking as if I were advising you in the eastern US.

* Everything is determined by the contract.  If a contractor doesn't want to abide by the terms, then they aren't obligated to do the work or receive compensation for the work.
* Competition will minimize the price to your client -- shop it around, even internationally.  Try American workers.
* You might have to have the contractors act as temporary workers (on site, using your client's equipment) in order to fully own the source code.  If there are only a few hours of work, it might be worth the cost of renting a motel suite in a remote location for cheaper developers to create the code in another city.
* Explore non-compete clauses
Dave,
I'm sympathizing with the developer on this issue.

Unless this were a very unusual type of chart, I would be very reluctant to sign away all rights to a tool that develops that type of chart. This would be especially true if I were in the business of selling chart-making add-ins. I might, however, be willing to forsake all sales to other companies in the mining business for a specified period of time--say five or seven years.

Typically when I code for hire, the client wants the source code so they can maintain it if I get hit by the proverbial bus. Usually the coding implementation is so specific to their way of doing business that no other firm would be able to use the workbook without major modification. I would push back, however, if the client insisted on owning the algorithms involved as there are only so many different ways of doing task xyz and I don't want to burn one of them up.

If the code was a modification of a significant add-in I had previously developed, I might want to protect the source code. The issue there is to protect my code building blocks from someone who might want to go in competition with me.

I would also be reluctant to guarantee prepaid (or free) support for a version of Excel that Microsoft hasn't published yet. I would, however, offer to make that support available with fee to be determined in the future.

Since the amount of support depends on the number of people using it, there needs to be some kind of agreement on both number of users and time duration. If I thought there might be a market with other firms for the add-in, I might offer some financial concessions to your firm on the support for helping me identify the bugs, missing features and selling points that one naturally discovers through providing tech support.

The requests you listed may seem reasonable to you, but they aren't to someone in the business of making generic software to produce charts. I would expect the developer to be willing to give in on them only if there were no other accessible market for the software.

Brad
Thanks guys

The chart itself is standard, the work is in the automation of transforming the data series to vary the width of various columns. We had previously purchased a similar addin from the same vendor but there were issues with the addin in xl2007.

>Everything is determined by the contract.
Understood. But are there generally accepted standards (on the five points I raised) for this contract though?

>Competition will minimize the price to your client -- shop it around, even internationally.  Try American workers
Thx, I will consider that. Although as it now stands I will probably hand the task to a graduate engineer to create a manual (non-VBA) alternative

> I would, however, offer to make that support available with fee to be determined in the future.
That makes sense Brad.
Given the history here already - ie current addin has weaknesses in xl2007 - this is a concern. But I accept that additional coding should be covered by an agreement, whereas bugs should just be fixed
 
Kevin, your view?

Cheers

Dave
On the "False in other languages issue": Sorry, I can't help out here. I only drive on the wrong side of the road, but otherwise use English Office, which leads to enough trouble when you have a US keyboard and want NZ spell checking. But that's another problem. I'm glad I do not have to work in German Excel. VLookup = SVerweis (ughh).

"False" in Kiwi is "no sheep", by the way.
Dave,

First of all, I resent being referred to as a guy among ladies. I'm a gentleman with occasional lapses into douchedom and dickiness.

Tell the developer to pound sand. If you really believe the solution is that easy to build, find some yahoo who can do it in twice the time and 1/4 of the price (have you asked Wayne? He knows .net and does stuff for free or maybe some beer.)

When I am paid for code the purchaser owns the code. If I use pre-existing libraries I reserve the right to maintain ownership of those libraries for my own unlimited use.

When I pay for code I expect to own it unless the developer states otherwise in which case I start shopping around.

When I develop solutions I guarantee that work to be free of bugs forever which means free support. I have been doing this for years and the cost is minimal at most. This does NOT cover new versions of Excel and certainly not new features.

Why are you concerned about who owns what anyway? Does this widget give your company any strategic advantage if the developer is not allowed to shop same around for a profit? If not then don't worry about it as it is not a relevant issue. Perhaps it's time to review what your real objectives are. For example, perhaps you just want the source code and a guarantee that it will work. Perhaps ownership is an emotional issue versus a practical issue.

Here is an idea: allow the vendor to shop the solution around with the condition that you get so many years of free support including support for new releases. This is called aligning goals since the developer will be interested in keeping the solution compatible with future versions of Excel if they can realize profit beyond your relationship and into the future. As long as there are no competitive issues then this should be an easy decision. Even so, you should be getting a hefty discount because you are basically funding their R&D.
Thanks Kevin.

That reinforces to me that my orginal expectations were fair on onwership and support. I like your proposal "allow the vendor to shop the solution around with the condition that you get so many years of free support including support for new releases" and will go with that idea for the original quoted number as on this question:

>Does this widget give your company any strategic advantantage?
Nope.

Although we don't give our competitors any free kicks either

>have you asked Wayne
Funny you mention that, I went looking at Wayne's profile the other day as I can't recall the last time I came across him

Cheers
Dave
When it comes to Excel and developing it seems that clients want to have access to the code as well as legal rights to it. Very few clients to me ask about the code when I develop standalone solutions based on VB6/VB.NET.

Kind regards,
Dennis
Think of it from the developer's standpoint.
Company A asks me to do an app that does x,y, and z.
I write the app.
Company B some time later asks for an app that does x, y, and w.
I'm still the same guy, so even if I start over from scratch (which seems foolish) it's going to end up looking very similar. Now, even if I try to make it different, Company A could probably sue me and win since it looks like I plagurized their code.
If I write code for someone, I want to be able to use it too. I don't care what they do with it. They can bundle it into an app and sell it or whatever, but if someone else asks for a similar thing, I'll reuse the code. I'll still charge Company B full price though.
I 've only worked for a few companies (a couple manufactring plants and an Air Force research facility), but so far they've all been fine with that system.
Going beyond Excel - I don't do Excel for a living - we have always produced code on a co-ownership (object code at least) basis.

- my employer would own the code : both would, exception for use in competition
- as such it would not be protected  : see above
- we would have unlimited usage : royalty free since you [co]own it
- and it would not be available to anyone else : no dice here *
- there would be future support for xl15 etc : we don't price for unknowns #

* Sometimes, we can give a 6-month lead time before reselling or reusing it elsewhere.  6 months is a long time in software development.  Imagine revealing the innards of some IE8 magic 6 months later - the market's already way past that.  For an Excel add-in, I would imagine more longevity, so something like 12 months maybe.

# Commodity products have free support, such as Office with millions of installed base.  Custom development, even if it is resold 10 times, is less so - there is not enough volume to cover the unknowns.
I think that typically the look and feel are not the issue since you are not selling your "style" unless the company dictated the way it looks.
More likely, the company wants to know that the tough algorithms that they paid you to figure out will not go to someone else.  Often this is because of some proprietary information that they gave you (like how their database is structured or some trademarked/patented information that they shared to solve a problem.)

The problem with the computer world is that it's a combination of predefined procedures and new intellectual property.  For example, if I create a form that has a close button on the bottom center, is that a copyright infringement?  If, when you click it, it asks if you'd like to "Close this awesome form?" then does that mean you can't use that wording in someone else's app?

Trying to define a balance of what did you (developer) write specifically for the company and what can you carry with you to someone else is tough.
BTW, as far as I know, at least here in the U.S., when you create something, even if you are paid to create it for someone else, you retain copyright on it.  However, if you have a contract that indicates that you will not use it elsewhere, that means that you can only use it for yourself at home for personal use.
Thanks guys, that's all useful input giving me some sense of whats normal, fair or expected

Cheers

Dave



Help!  I can't find that great article that shows what shortcuts you can use for post, question, article, etc...

Anyone got a link to that?

Dave
Hey Thomas!

Congratulations on 2 million points in the Excel TA! Here's to the next million....

regards, barry
Thomas Bhai Mubarnka!!! Great Job!
Whoa, Thomas! Good sprint!! Congratulations!
If anyone has any ideas as to why a non-pivot chart might lose its formatting, please feel free to jump in here as I'm stumped: http:/Q_26916436.html
Ta,
Rory
Has anyone figured out why the code blocks in these posts sometimes has a massive amout of blank lines at the end? It only happens sometimes and I can't figure out what does it. I just had a post that did both in the same post.
http:Q_26917757.html#a35245037
I asked the question in the community support zone too because it's driving me nuts. I guess you should post the answer there if you know it. I'll follow up here with the answer if I get one.
http:Q_26920298.html
Tommy, the CS zone is not for free discussion. Experts are only allowed to participate in their own Requests for Attention. You could post it as a bug in https://www.experts-exchange.com/Community_Support/EE_Bugs/

cheers, teylyn
Ok - aside from "depends on how large or complex...", does anyone have an idea on page editor turnaround on an article submitted?

I "bit the bullet" and wrote one (and yea, teylyn, it was around 3-4am, lol - I had nothing else to do but sleep, and who'd want to do that?).

Cheers,

Dave
Dave, have you been contacted by a PE yet? If so, you could simpy ask them how long they feel it will take. I guess it also depends a bit on the workload of the PE. My first 700 word article was published within a few days, and the PE was not an Excel expert.
Yes I do.  I'll give it a couple more days and prompt accordingly.

Tks

Dave
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry, I'm not sure who is the moderator for the Excel zone. Anyway, I want the moderator to take a look at the following thread:

https://www.experts-exchange.com/questions/26923010/Excel-VBA-referenvce.html?cid=748&anchorAnswerId=35274103#a35274103

The OP split the points in a questionable way.

Thanks in advance.
Thanks :-)
Thanks Sid, Inge and barry! And congrats to teylyn for your second million (I managed to get there just before you, but you're back in your well deserved spot).

Thomas