Tag Archives: Excel

Microsoft Excel

Maybe SkyNet Will Be Written In Excel VBA After All … OR … Why ThisWorkbook Can Save You Headaches With Your Macros

Posted on by
Reply


In my last post, I joked that SkyNet from The Terminator would not be written in Excel VBA because you cannot make Excel VBA code aware that it exists within a particular workbook.

Turns out that I may have been wrong about SkyNet and that I was very wrong about needing to create a workbook object to ensure your code looks at the workbook in which it exists.

There is a way to force your macros to look at the workbook in which they exist.

You use the ThisWorkbook property.

OK, Give Me An Example

Of course. For the example below, create a workbook called “WhyThisWorkbookIsOKWithMacroCode.xlsm” and place the code below in a VBA module.


Sub NotSoConfusingThisWorkbook()

Dim whatYouThinkIsActiveWorkbook As Workbook
Dim whatActuallyIsActiveWorkbook As Workbook

Set whatYouThinkIsActiveWorkbook = Workbooks("WhyThisWorkbookIsOKWithMacroCode.xlsm")
Set whatActuallyIsActiveWorkbook = Workbooks.Add

whatYouThinkIsActiveWorkbook.Worksheets("Sheet1").Cells(1, 1) = "this is what you think you will see"
whatActuallyIsActiveWorkbook.Worksheets("Sheet1").Cells(1, 1) = "this is what you actually see since activeWorkbook confused you"

whatActuallyIsActiveWorkbook.Activate
Debug.Print ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1)

' show how ThisWorkbook works
Debug.Print "Now Let's try it with ThisWorkbook"
Debug.Print ThisWorkbook.Worksheets("Sheet1").Cells(1,1)

Set whatYouThinkIsActiveWorkbook = Nothing
Set whatActuallyIsActiveWorkbook = Nothing

End Sub

As you can see after running this code, while ActiveWorkbook looks at the last activated or clicked workbook, ThisWorkbook refers back to the workbook in which the VBA Macro code exists.

How To Fix New England, New Jersey, and Caribbean Zip Codes in Excel

Posted on by
Reply

Assuming the zip codes are in column E, place the following code in column F:


=IF(LEN(E2)<5,REPT("0",5-LEN(E2))&TEXT(E2,"#"),TEXT(E2,"#"))

Drag the formula down the column.

If you want to know why this works, click here.

If you want to know why this problem exists, click here.

facebooktwittergoogle_plusredditpinterestlinkedinmail

Related books picked - and if possible read - by me. Sponsored by Amazon Associates.

How To Turn Your Excel Worksheet Into A Simple HTML Table

Posted on by
1

As I said in my NFL Playoff post, here is a brief description on how to convert an Excel worksheet into a simple HTML table.

What makes this tricky is that Excel tries to preserve all of the formatting from Excel in the HTML file, which adds an enormous amount of clutter that you do not need.

Here is what to do:

The Really Short Version

Save the worksheet as a “Single File Web Page”. Open it up in a text editor and erase everything above the <table> tag and below the </table> tag. Erase all of the attributes in the remaining tags.

The Longer And More Detailed Version

  1. Go to File, Click Save As.
  2. In the “Save as Type” drop box, choose the format “Single File Web Page (*.mht,*mhtml)”.
  3. Click Save.
  4. Click “Yes” in the pop up box to lose formating.
  5. Find the file where you saved it.
  6. Open it in a text editor like Notepad, though I highly recommend getting the free Notepad++
  7. Man there is a lot of code. Don’t worry. Just Search for the string “<table”. This is where the actual data from the worksheet is. It should contain a lot of <tr> and <td> tags. You should be able to spot your actual data inside the <td> and </td> tags.
  8. Erase all of the code before “<table”.
  9. Search for “</table”.
  10. Erase all of the code after “</table … > full tag.
  11. Erase all of the attributes inside all of the remaining tags such as “width=” and “style=”. A good short cut at this point is to a find and replace for what you are trying to erase and replace it with nothing since it is repeated in many of the tags.

That should do it.

Related books picked – and if possible read – by me. Sponsored by Amazon Associates.

Walkenbach & Mr. Excel: The Two Books to Get Stared in Excel VBA

Posted on by
3

If you want to get begin using Excel VBA, the best way to do so is to read these two books:

They will give you the foundation that you need to use Excel in powerful and just as importantly in reliable ways. If you choose to read them, make sure that you type out all of the examples in these books yourself in the Excel VBA IDE (what you see when type Alt-F11).

Excel VBA Programming For Dummies by John Walkenbach

This is the book with which to get started, especially if you do not know the first thing about programming. Walkenbach does an excellent job of holding your hand through the basic concepts that you need to know. As well, he sells a power utility pak that uses VBA to give Excel powerful and useful features. For extra money, you can see the code for it. I strongly recommend purchasing the pack and paying to see the code. You will see lots of examples of how to write practical applications for Excel in VBA.

VBA and Macros for Microsoft Excel Bill Jelen and Tracy Syrstad

WARNING: They have written similar books written since then, but previewing them on Amazon makes me think that they are missing key content. You need to buy the one written in 2004.

This book will take you to the next level. It is aimed at people who are more intermediate than beginner. It will show you just how powerful VBA in Excel can be. This is the book that allowed me to start getting my head around the VBA Object Model and writing VBA Classes.

While you should read the whole book, you should pay very close attention to the following chapters:

  • R1C1 Style Formulas
  • Names
  • Event Programming
  • User Forms
  • Automating Word
  • Event Programming
  • Creating Classes Records and Collections – this one is the most important
  • Handling Errors

I feel that these books are essential for getting started. You can buy them from the links below or at the top of the post: