Month: October 2005

Triangle Fractal

Posted on by Dick Kusleika

Chip described a way to create a fractal. I paraphrase: Start with an equalateral triangle. Find the current point by picking a vertex at random. Put a mark at the current point (the vertex you just picked). Change the current point by picking a vertex at random and making the current point the midway point between the old point and the current point. Put a mark there and repeat. Do that about 50,000 times and see what you get. Or paste this code into a workbook and run SheetTriangle. Make sure you run it from Excel and not the VBE – it’s cooler to watch than just see the end result.

I started doing this with shapes instead of colored cells. It didn’t look quite as good and I’d end up with 10,000 shapes on a sheet, which can’t be too stable. I changed to coloring cells and upped the iterations to 50,000, which is roughly 256 x 227. I don’t know if 50k is too many or too few, but it definitely gives you a good idea. The 227 is what I calculated the height in cells to be to make the legs of the triangle 256. You don’t need an equalateral triangle, though, it’s just the way it was first presented to me. I also heard that the randomness isn’t necessary, but I couldn’t make it work otherwise. I’m sure it’s possible, I’m just not up to it.

I asked Chip to whom credit was due, and he said he came up with it himself. I’m not too shocked, he’s a pretty smart guy. I think 1,000 monkeys on a 1,000 typewriters would have a better chance coming up with this than me. Some guy named Sierpinski beat him to it by about 90 years, albeit via a different method.

Excel Jobs

Posted on by John Walkenbach

I get a fair number of emails asking about Excel-related jobs. I’m so far out of the corporate loop that I have absolutely no idea how an Excel freak would find a job.

There’s a job site called Indeed, where you can search for jobs using keywords. For example, here’s a link to their Excel jobs sorted by relevance. I’m sure there are dozens of other job search sites. I found Indeed by accident.

How about some suggestions on how an Excel wiz would find a job? More generally, what’s the job market like for Excel gurus?

Excel 12 Formula Autocomplete

Posted on by John Walkenbach

Did you see the latest entry at David Gainer’s blog? Formula Autocomplete.

Finally, it will be possible to write a SUBTOTAL formula without consulting the Help! But one of the commenters makes a good point about the SUBTOTAL function:

So… please help me understand why you’re using a function number rather than just the name of a function. When we’re skimming through a sheet, or looking at a print out of formula view, we’ll have to have learned which numbers refer to which functions, won’t we? Or am I missing the point completely?

Automating pastevalues

Posted on by Juan Pablo Gonzalez

There are a lot of good comments on the mouse shortcut entry to Paste Values. I am curious though, because I haven’t seen one variation of a macro to Paste Values that I thought was more straightforward.

The commented method involves two steps, one, to copy the range, and two, to perform the Paste Special, something like:

Sub CopyPasteValues()
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues
End Sub

The other alternative, is to use the .Value property of the range, like this:

Sub ValueToValue()
    Selection.Value = Selection.Value
End Sub

So, what’s your take, is one better than the other? In theory I would think that the second one would be faster. It’s only one operation, and Excel doesn’t have to keep the range in memory to perform the paste special. I’m not sure how either method works with discontinuous ranges.

AutoFill Macro

Posted on by Dick Kusleika

I’ve recently added a new macro to my Personal.xls (that’s four now!). This one is to replace the cumbersome Edit > Fill > Series > Autofill (alt-e-i-s, alt-f, enter).

Sub FillSeriesAutoFill()
    If TypeName(Selection) = “Range” Then
        Selection.DataSeries , xlAutoFill
    End If
End Sub

This uses the DataSeries method of the Range object. Honestly, I expected there to be a FillAuto method, but it turns out it’s called AutoFill. That really is a better name, so I don’t know what I was thinking. I was probably thinking along the lines of FillDown and FillRight. I’m not sure the AutoFill method would work in this capacity, however, because I wouldn’t know on which Range to perform the AutoFill. For instance, if I had the numbers 1 through 7 in A1:A7, I select A1:A10 and run the above macro to fill the series down to get 1 through 10. The equivalent AutoFill would look like

Range(“A1:A7”).AutoFill Range(“A1:A10”), xlFillSeries

I can replace Range(“A1:A10?) with Selection, but I don’t know how to replace Range(“A1:A7?). Maybe that’s why they have two methods for this. It works for me, and that’s what’s important. Here’s a rundown of the arguments for the DataSeries method:

Rowcol: You can specify whether to fill by rows (xlRows) or columns (xlColumns). I’ve never changed Excel’s guess in the user interface, so I didn’t see the need to include my own logic in this macro. I omit the argument and take Excel’s guess.

Type: This corresponds to the four option buttons on the Fill Series Dialog; Linear, Growth, Date, AutoFill. Linear is the default, which was surprising to me. I thought AutoFill would be. I’m not sure I understand what these mean, but my best guess is that AutoFill determines the proper type of fill based on the data that’s already selected. That works for me most of the time.

And the rest: The Professor and Mary Ann of the DataSeries arguments. You can determine how to increment the series, when to stop it, and whether to create a trend. I always enter the first two cells which determines the increment, I stop selecting when I want the series to stop, and I leave the trend setting to Old Navy.

Mouse shortcuts

Posted on by Juan Pablo Gonzalez

So Dick is the keyboard guru here, but there’s a lot of people that use the mouse a lot. I know I do. And I was reading Jensen Harris‘ blog today about how a Paste Special operation takes 6 clicks right now, but only 3 in Office 12.

However, there are a number of ways in which you can shorter this operation. One way, is to customize the toolbars, and display the ‘Paste Values’ button as shown in the screenshot.

The steps required to add this button are:

  1. Right click on any toolbar, and click ‘Customize’
  2. Click on the ‘Commands’ tab
  3. Select the ‘Edit’ category
  4. Scroll down in the commands list until you see the ‘Paste Values’ button
  5. Click and drag this button to the toolbar where you want it.
  6. Click on ‘Close’ and you’re done!

This reduces the number of clicks needed to 3 (Select Range, click on Copy, click on Paste Values).

However, there’s another method that blows people away each time I use it… it’s that cool factor I guess.

Take this data for example, I’m using =RAND(), just like Jensen is.

Now, select the range, right click and hold on one of the borders (*not* in the fill handle), drag the range as if you were going to move it to a different place, and then drag it back to its original position. Just offseting one row or column willl do the job.

When you release the mouse button, a popup menu will appear, giving you some pretty cool options, one of those being ‘Copy here as Values only’. Basically doing the Copy and Paste in one single operation.

I guess technically this is still a 3 click operation, but there’s a lot less mouse movement involved, and, you can use it to create links or hyperlinks for example.

Why I’m Looking Forward To Excel 12

Posted on by John Walkenbach

The top-10 reasons I’m looking forward to Excel 12.

  1. A new name. Will it be called Excel Vista? If so, I want to go on record as being the first person to point out that the name contains the word Elvis.
  2. Updating my books will be easier. It’s no fun trying to update an Excel book when there’s very little that’s new. In other words, readers can look forward to scads of those little “NEW FEATURE” icons in the margins.
  3. New newsgroup questions. Everybody knows that 95% of the newsgroups questions have already been asked and answered. When Excel 12 is released, that number may drop as low as 90% (at least for a while).
  4. Terminology challenges. I love a good challenge, and mastering Excel 12’s terminology definitely qualifies. In past, I could write: Select Format – Conditional Formatting to display the Conditional Formatting dialog box. With Excel 12, it will probably require two pages to describe.
  5. A new splash screen. It’s the little things that make life worthwhile. I’m really looking forward to seeing something different during those 3-4 seconds while Excel loads.
  6. New Help system. Everybody complains about the Help system. Now we’ll be able to come up with an entirely new set of complaints.
  7. Fiddlin’ with the XML files. I think it will be fun to come up with utilities that work directly with the XML files. It opens the door to a whole new class of pranks.
  8. Updating my stack of paper calcs. In many of my books, I include a sidebar that calculates how tall the stack of paper would be if you filled every cell in a worksheet and printed it out. With Excel 12, I may also be able to calculate the number of trees required.
  9. The diagrams. Playing with the new diagram features is a great way to waste time, yet still look productive. I can’t wait to animate those suckers.
  10. Finding the object model lapses. You just know that some of the new features won’t be fully accessible using VBA. Who will be the first to find them?

What else?

Reading the internet

Posted on by Juan Pablo Gonzalez

Web queries are one of the most useful and coolest things for me in Excel. I use them *a lot*, because they allow me to combine the front end of Excel with back end information stored in different web servers.

However, sometimes they can be a little limiting. There’s the option of automating Internet Explorer (which I try to use as the ultimate resource, because it’s the slowest option from what I’ve seen), but there are some different alternatives as well.

The XMLHTTP object (which you can use in early binding by adding a reference to ‘Microsoft XML, vX.X’, where X.X is one of the available versions (I have versions 2.6, 3.0 and 5.0 installed on this computer). I’ve used 3.0 (for no particular reason), and I’m not sure what the difference between the versions is, but that one seems to work for what I’ve needed so far.

Ok, so, let’s get the links from a web page using a POST method. One page that I know uses this is MrExcel’s message board (I tried using this blog’s search page, but I got an error…). The sub that I used is this:

Sub TestReadHTML()
   ‘We’ll search the MrExcel board here
   Dim Url As String
   Dim HtmlBody As String
   Dim Col As Collection

   ‘The url of the page
   Url = “http://www.mrexcel.com/board2/search.php”
   Url = Url & “?search_keywords=querytable+refresh”
   
   ‘Get the html document
   HtmlBody = UseXMLHTTP(Url)
   
   ‘You can parse the HTML here, for example, retrieve all the links
   Set Col = New Collection
   Do While InStr(1, HtmlBody, “<a href”) > 0
      HtmlBody = Mid$(HtmlBody, InStr(1, HtmlBody, “</a><a href”))
     
      Col.Add Left$(HtmlBody, InStr(1, HtmlBody, “</a>”) + 3)
     
      HtmlBody = Mid$(HtmlBody, InStr(1, HtmlBody, “</a>”) + 3)
   Loop
   
   MsgBox “Found “ & Col.Count & ” links !”
   
End Sub

Of course, the function that really matters here is the UseXMLHTTP, which follows:

Function UseXMLHTTP(ByVal Url As String) As String
   Dim oHttp As Object
   Dim Parameters As String
   Dim i As Long
   On Error Resume Next
   ‘create the object, error out if not found in the machine
   Set oHttp = CreateObject(“Microsoft.XMLHTTP”)

   If Not oHttp Is Nothing Then
      ‘Make sure we have some parameters in the URL
      i = InStr(1, Url, “?”)
      If i > 0 Then
         ‘Extract the parameters from the URL
         Parameters = Mid$(Url, i + 1)
         
         ‘Now remove them from the URL, so we can send it to the XMLHTTP object
         ‘I guess we could receive two variables, but I’m lazy tonight <g>
         Url = Left$(Url, i – 1)
         
         ‘Use GET or POST as the first parameter of the .Open method (depends on the webpage)
         oHttp.Open “POST”, Url, False
         
         ‘Using the .setRequestHeader you can add more headers as needed
         oHttp.setRequestHeader “Content-Type”, “application/x-www-form-urlencoded”
         
         ‘Send the parameters (similar to the .Refresh method of the querytable)
         ‘it’s here where the page is actually loaded
         oHttp.Send CStr(Parameters)
         
         ‘Get the response back
         ‘You can check the .status or the .statusText of the oHttp object, to see if the
         ‘request was succesful or not
         If oHttp.statusText = “OK” Then
            UseXMLHTTP = oHttp.responseText
         End If
      End If
     
      ‘Destroy the object
      Set oHttp = Nothing
   End If
End Function

Reading the HTML body is one of the things that you can do (most useful to me when the webpage returns a text file, because you don’t have to mess around with the HTML), and is very simple to do so. But there are two other things that you can do. One, is read an XML document directly from the request, using the .responseXML method, like I showed here. I’ve been using this feature (since I finally decided to jump in the XML train…) and I really enjoy the simplicity that this provides. You get everything in one single object.

The other thing is that you can (at least in theory, because I haven’t tried it live…) is that you can retrieve a file using the .responseStream method, that could be sent from an ASP’s IO.Stream (keep in mind that I’m starting to talk about stuff that I don’t really know here, ok ?)