Methods In Excel

VBA for iG_Syntax_Hiliter

2006-02-19T21:43:00.000Z

I produced a .php file for VBA. I got most of the "Keywords" from ostrosoft.com, and added a few myself. I also changed the colour system. The VBA file has abou 160 "keywords" vs about 300 for the VB one. This is beacuse the "keywords" are infact "Bluewords", that is to say the code produced by my VBA .php file should be the same as if it where in then VBA IDE (theres green, blue and black and

VB timer as Double

2006-02-17T16:22:00.000Z

Ok so this is a lazy post, but has anyone used the timer with double type?Try it, I'm looking into this and hope to get a better understanding of the whole thing, but it seems to use 3 decimal places when loading the time, and more when subtacting (more sdp than singles). In documention it says Timer returns a single. Thus i would have thought (FLW!) that if the result was getting put in a double

Go Sub, to go?

2006-02-15T11:17:00.000Z

I was flicking through John's recently opened Hard Core XL VBA site (well forum), theres a lot of content there, it's a nice way to store a lot of code. One thing that i had never seen before was the go sub methodBasically you goto a Lable, excute whatever code follows and finish with a return line, vizSub Test()x = Array(1, 10, 100)For i = 0 To UBound(x)If x(i) > 10 ThenGoSub ValueErrorEnd

Moving a captionless userform

2006-02-12T10:36:00.000Z

Yesterday I was playing around with Jan Karel Pieterse Watch Other Cell tool. I wanted to make the user form captionless and transparent. That’s easy enough to do but a problem arose. With the caption removed the form could not be moved.One work around was to use the forms mouse move event and an API Call to hook the cursor location. Another API call is made to reposition the form. The Down and

Programming to die for?

2006-02-11T14:39:00.000Z

This is funny! i got 8 out of 10

Office and Vista Beta experience

2006-02-10T09:12:00.000Z

In the past the beat experiences have been free, and quite good :-), sign up now, you've got nothing to lose!!!!The new generation of Microsoft Windows© and Office© is about to be launched. Get in on the act right from the start and follow the new products as they go through to the finals. As a member of the Vista and Office Beta Experience you will benefit from valuable resources, specialist

An Excel Front loader (Dot Net, dot slow! )

2006-02-08T23:16:00.000Z

Last night I jumped onto the .Net train. So I’m a little late? I’m using VB 2005 express. I knocked up a little “front loader” app. Very simple, it makes a new excel instance and loads a .xls file called “App.xls” from the same dir into that excel instance then exits.I had a number of problems:The form does not display correctly (on my PC’s), and there does not seem to be a

Learn Visual Studio

2006-02-08T16:49:00.000Z

Microsoft are offering up quite a lot of decent resource for.net developers/or cross over developers. The Visual Studio Learning home page has a lot of links to virtual labs each aimed at different parts of VS. There are even a few books that can be downloaded for free! (sroll the learning home page or, just the VB ones) – I think I’ll print out a chapter at a time ;-)!Credit where it’s due well

That's some expensive visual basic!

2006-02-06T18:33:00.000Z

wow, VB 2005 express is free, but for a mere 25'000 dollars you can get your hands on this bad boy! Amazingly, no takers as yet...

Excel User Conference

2006-02-01T21:33:00.000Z

This year the Excel users conference is coming to the uk (london). It should have course been held in Northampton - it beeing the centre of England and all that ;-).Anyway it's a 2 day event, the second day being more advanced that the first.The list of speakers is quite impressive (unconfirmed):Patrick O'Beirne Bob Phillips Nick Hodge Andy Pope Ken Wright Simon Murphy Martin GreenDates and

Speed basic Update 2

2006-01-29T18:33:00.000Z

Mike Martin the aurthor of SpeedBasic recently contacted me about his product. It turns out that development has not in fact stopped. Mike took a look at B++ and noticed some areas he thought could be improved. He did the same with his own Speed basic. Taking all this on board and re wrote SpeedBasic to incorporate much more syntax checking prior to converting the code into C.The latest

Display full menus

2006-01-28T11:25:00.000Z

While we're on the subject of toolbars and the customise dialog, I would recommend to all Excel/Office users the following tip. Turn OFF the show menu after short delay option and turn ON the always show full menu option. Again this can be found in the customise dialog, under the options tab. Checking this option will force Excel to display the whole menus and lets you see all of the menu

Streamline your Excel toolbars

2006-01-28T10:24:00.000Z

Microsoft hold the average user has 4 or more toolbars displayed at any one time. This represents quite a lot of the screen “real-estate”. This is especially pertinent with the advent of “wide screen” type set ups popular on many laptops. A colleague of mine has about 25 rows on display, that’s on a 17” screen! (well, “wide screen”). I believe is that many uses pull up a menu bars and end up

Hidden data and Charts

2006-01-25T10:31:00.000Z

Sometimes we want to hide our data but still display the values in a chart. By defult Excel will remove the chart content when we hide rows or colunms.The answer is to change a setting in the Excel Maze, sorry options box.Select the chart you wish to change then goto: Tools > Options >Chartthen unselect the "Plot Visible Cells Only" option.Another option to consider is putting the data in another

Filling In - Working with lists and tables

2006-01-24T11:23:00.000Z

Often we end up with a sheet that looks like this:And we want to fill in the empty cells, so it looks like this:MS MVP Zack Barresse suggested the following cunning method:Select the entire range of data, including blanks. Hit F5 | Special | Blanks, click OK. Now hit the = key, then hit your up arrow once, then hit Ctrl + Enter. If you wish the values to stick, select the entire range again,

Captionless worksheets

2006-01-22T13:28:00.000Z

Big Dick Kusleika over at Daily Does of Excel made some suggestions for Excel 12. One of which was to have captionless worksheets. The following example sort of does it, it’s a modification of Steven Bullen’s and Tim Clem’s Form fun class. I didn’t spend very long on this as it’s not really of any use to me, I just wondered if it could be done. To use it in anger you’d have to

Lexar POS

2006-01-20T20:53:00.000Z

POS has nothing to do with memory locations.I bought this (although it was the 1g version) for £60, not that cheap really. I got it about 10 months ago. After about 3 months the plastic cum rubber key hook thing broke, in fact the casing was rubbish from the off.After about 8 months the whole thing (now bandaged up with glue and nylon strips) stopped working: Not to worry, the handy old soldering

Speed Basic Update!

2006-01-18T21:55:00.001Z

So i installed and played around with SpeedBasic. I chose to install the Borland C++ compiler, even though i have MSVC. I can honestly say it was a breeze. Much easier that installing VS6 or VB express! (This is stupid, how can it be that one man working by himself can do it better that MS????!!!!, lol!)Anyway, I don't really know much about none MS BASIC syntax, but it was easy enough to build

New’s good, but what about improvements?

2006-01-18T00:24:00.000Z

New ideas capture peoples imagine, no doubt about it. But this struck a chord when I re-visited J-Walks old blog.As you might expect, you'll find lots of predictable comments and jokes by people who don't know what they're talking about. But there are a few interesting comments. For example:Someone need to start OVER and rethink what a word processor needs to do. Basics like multi level numbering

In-Line Help Methods, some examples

2006-01-15T22:00:00.000Z

Following a question over at JMT forums, I have literally thrown together some examples of methods for displaying “in-line” help. By “in-line” I mean the sort of help that’s displayed right next to the area where it’s needed/used. I don’t believe any of these methods should form the base for a well structured help system, and some boarder on the silly. Still, there is clearly a place for such

Blogger Blogging from Word?

2006-01-12T18:43:00.000Z

I have just installed a word addin that lets me post to my blog from word. Very easy to setup, just enter your user name and pass word!But how well does it work!!!!! Well with this post we shall find out!BOLDUnderlined ItalicLink (to addin page) First problem! does not support images!!!!!!!!!, but i can edit the HTML before i post it, see!NOTE: here a screen shot!

Free (Visual ) BASIC and more!!!!

2006-01-12T12:16:00.001Z

So I’m looking for a free BASIC complier that I can use commercially. There are quite a few around , but I really need a GUI. JustBASIC seems ok, if not a little lite weight , hey you get what you pay for!!!.FreeBASIC looks very impressive, but I need to check out the licensing. Importantly FreeBASIC is supported by the FBIDE and JellFish Pro IDE’s. The latter of which looks quite powerful.This

Concise XML

2006-01-09T15:07:00.000Z

A large spread sheet saved as XML can lead to some big files, there hard to understand - for me anyway: Eric Bachta has a cool solution, he writes his own simple XML!I wish there as some kind of add-in for pre 2003 excel which allows XML to be used in it - properly! Any one know of such a thing?

XLL update

2006-01-06T19:20:00.000Z

David Gainer was kind enough to answer the mulit thread .xll questionExcel 97 does not support multi-threading. I can see what I wrote was confusing. What I meant was that if you had some XLLs that you have been using for some versions that you want to participate in mult-threaded calc, you can recompile them after adding the flag and they will work – you don’t need to update anything to use the

Differences between these two types of Add-ins.

2006-01-05T23:19:00.000Z

http://support.microsoft.com/default.aspx?scid=kb;en-us;291392this is for me really. :-)

2 xll type links

2006-01-05T17:04:00.000Z

XL dennis posted these links over at Dave Gainer Blog, useful, I must read PED also.http://blogs.msdn.com/eric_carter/archive/2004/12/01/273127.aspxhttp://support.microsoft.com/default.aspx?scid=kb;en-us;Q178474

Opening Access Tables Directly in Excel

2006-01-05T09:51:00.000Z

Maybe I've missed the boat on this one but I never knew you could open an Access table directly inside excel, I've always exported them to .xls files in Access, or used Get Extrenal Data. In Excel, file open (pick all from the type drop down) and click on the Access DB, give you this:clciking the table will import it into excel - i guess it errors out when you go over 65537!

Excel 97, supports multi-threading??????????

2006-01-04T09:51:00.000Z

I'm not really up to speed with c programing etc, although it is somthing i'm gonna start to look at. Anyway, i dont understand this, from David Gainer Excel 12 blogNote, the Excel4 API will also respond appropriately to the “$” character, so if an XLL author wants to take advantage of multi-threading, but doesn't care about the big grid, they do not have to use the Excel12 API.So does this

This is for Andrew!

2005-12-28T13:57:00.000Z

I've seen this sort of thing in excel before, some better than others. I tested it in a live journal account i set up and it work perfectly. Not sure about pictures though!

P -code??????

2005-12-26T14:05:00.000Z

I'm working on a big dictor app at the moment. It's starting to crash big time! - Often after making a small change to a VBA module it will refuse to open again in excel. I have to do a “detect and repair” to get the workbook to open. I'm not sure if it's a registry problem or something else - but I'll find out!Anyway all this lead me to finding this post, which I think I have read before, but

Lets Split!

2005-12-22T21:51:00.000Z

Came across the VB Split fucntion which I had ever used before today, fom help: (anoted)DescriptionReturns a zero-based, one-dimensional array containing a specified number of substrings.SyntaxSplit(expression[, delimiter[, limit[, compare]]])Expression,Required. the stringDelimiterOptional. String character used to identify substring limits. If omitted, the space character (" ") is assumed to

gbCodeLib

2005-12-20T09:43:00.000Z

I’ve been using this for about 6 months and it’s a brilliant tool! Gary has also been kind enough to make the full version available for free too. Excellent! Not only can you manage all you code snippets, but you can store example file right along side them; there’s even a version that runs right off your flash drive. Give it a whril and never find yourself scratching though old workbooks

Exporting to excel from owc (and pivot tables)

2005-12-20T09:20:00.000Z

From a white paper at MSDN: EXPORTING TO EXCEL FROM OWCSome customers have reported difficulties in exporting data from Office PivotTable Components to Excel. Microsoft has investigated these reports and determined that:• OWC does not pass the expand state to Excel correctly, so Excel expands everything. Because all rows are set to visible, all data must be downloaded and all empty rows

Get RGB value of control at runtime

2005-12-16T10:12:00.000Z

Theres a lot of code out there for getting the windows colour picker dialog, but it can be hard to get a RGB code out of this. For example, if you want to set the colour of a form text box, then get it's RGB value to colour a worksheet cell. (That's what i used it for anyway!)Heres one method:Private Declare Function GetSysColor Lib "user32" _(ByVal nIndex As Long) As Long Function GetRGBColors(

Find Lower Case

2005-12-15T09:58:00.000Z

Following a question in JMT I wrote this UDF, it finds the first lower case letter in a cellPublic Function FindLower(cell As Object)'''Finds the first lower case letter in a cellIf cell.Cells.Count <> 1 ThenFindLower = "Error - enter only one cell"Exit FunctionEnd IfFor i = 1 To Len(cell.Text)If Asc(Mid(cell.Text, i, 1)) > 90 Then '90 = "Z"FindLower = iExit FunctionEnd IfNextFindLower = "No Find

Beyond Menus and Toolbars in Microsoft Office

2005-12-15T09:37:00.000Z

This is interesting! mostly I don’t agree with it. Office 12 is an upgrade I wouldn't mind paying for, that is, assuming that work didn't let us get free copies. Those are big words for me, considering my "I Hate Microsoft" series of blog entries. I can imagine making documents faster with Office 12, or at least I can imagine making better looking documents in the same amount of time. Excel,

Not hum Drum!

2005-12-15T09:25:00.000Z

This is quite cool some kind of drum machine build in excel! Although it does use a .dll. still cool all, the same!

First Post

2005-12-14T22:19:00.000Z

My web site is methods in excel (www.methodsinexcel.co.uk), but i find that it's not always the best way for me to put info up, so I'm giving this a whril too. If i like it i think i'll move to a blog full time.