Author: Juan Pablo Gonzalez

Excel security flaws out in the open

Posted on by Juan Pablo Gonzalez

You have to love this quote:

“IT guys should tell end users right off the bat that if they see an unrecognizable Excel document in their inbox, they should treat it like porn — it’s not something you should be opening up at work.”

Full article here.

I guess this could probably used as another excuse to kill VBA in future versions of Excel (and Office).

Code Satisfaction

Posted on by Juan Pablo Gonzalez

Reading the comments in Dick’s latest post got me thinking about how we, as programmers, are really never satisfied with what we have/write.

And I was reminded of this paragraph from one of the chapters in Joel on Software:

There’s a subtle reason that programmers always want to throw away the code and start over. The reason is that they think the old code is a mess. And here is the interesting observation: they are probably wrong. The reason that they think the old code is a mess is because of a cardinal, fundamental law of programming:

It’s harder to read code than to write it.

I know I’ve been guilty of that several times. I just *know* that I can write it better the second time, when I understand all the complexities and subtle issues and objects that *really* interact in the program, but the effort required to do so is just too great.

So I was curious, are you ever satisfied with your code? is it even worth to look at old code just to always say: “Wow, this is ugly!” ? or is it just part of who we are and we have to rely on other people telling us to NOT do something?

BTW, if you haven’t read that book, I highly recommend it.

Async XmlHttp calls

Posted on by Juan Pablo Gonzalez

Based on a recent interaction with Jan Karel, I thought about resurrecting this subject which I find very interesting.

Currently the web community is all hyped about Ajax, because of the enhancements that the combination of javascript, DHTML and the XMLHTTP object can bring to the user experience.

In Excel development we can also take advantage of the XMLHTTP object, but it is generally used in a synchronized way, that is, the code must wait until the object finishes loading the page before it can continue. This is usually not a problem, but when you are reading a big page, this wait can become a problem.

The only direct way of dealing with this problem is to put the XMLHTTP object inside of a Do / Loop cycle, but we can create a truly async solution by using a helper class module.

Let’s start with a simple example. This is the RSS feed of the recent tracks that I have listened to.

http://ws.audioscrobbler.com/1.0/user/juanpg/recenttracks.rss

Now, open a new workbook in Excel, go to the VBE and insert a new standard module.

From there you can see that we’ll create an object called CXMLHTTPHandler. This is the class module that handles the status change of the XmlHttp object. So, insert a new class module and rename it ‘CXMLHTTPHandler’ and add the following code to it

We’re almost done at this point. There is something that needs to be addressed first though; note this line in the standard module

In Javascript we can assign functions without a problem, but this is not as easy in VBA. We first need to add a default property to our own class, which will be, of course, the OnReadyStateChange() sub.

Chip Pearson explains how to do just that here. But it is just a matter of following these steps:

  1. Export and remove the ‘CXMLHTTPHandler’ class to a known directory
  2. Open that file with notepad
  3. Find the ‘OnReadyStateChange’ sub, and add this text after the signature:Attribute OnReadyStateChange.VB_UserMemId = 0
  4. Save, close and reimport the class module into the project

And that’s it ! when you run the ‘Test’ sub you should see a message box with an xml document, similar to this one (Anyone up for ‘Pastora’ ?)

Async call to XMLHTTP

It is possible to expand this concept by including a Public Event in the class module that can be fired when the OnReadyStateChanged() sub finishes, that can be captured by another object.

VBA Userform tip

Posted on by Juan Pablo Gonzalez

I don’t know many people know this, but I find it extremely useful when I’m designing a form.

If you select a control, you can then press the TAB key to cycle to the next control (and on) to see what the flow of the form is, just like if you were using the form live. It’s very easy to spot errors in the TabIndex properties this way.

But sometimes I wish we also (and note the *also*, I don’t want that to be the *only* way) had a feature like Adobe has for its forms, where you just click the tab order of the controls.

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.

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.

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 ?)