Friday, May 18, 2018

Days Gone [Addendum]

Hard to believe that I haven't posted an update on this project for almost six months. Wow. Time flies when your full-time career starts consuming almost all of your waking hours with commute and work. But I think the big hurdle in that particular project has been passed, so I'm trying to get a handle on time again so I can get back to work on Days Gone #3.

As a side note to this project, I found myself needing a back cover illustration for the Collectors' Club Newsletter #122, so I quickly pulled out two separate illustrations from the Days Gone project and combined them into the illustration below.

Dragon Spire
© 2018 Mike Mitchell

I made the color choices because I wanted to emulate the old three -color printing process that was frequently used in fanzines back in the 1970s and early 1980s. I also chose the framing effect (cropping the background smaller than the figure so we could see paper on the sides) based on a Conan or Kull portfolio piece I saw in an old Savage Sword of Conan magazine.

Speaking of which, I've really been enjoying reading those old issues. I've been buying lots of them this year, and have about half of the series. I'll write more about this in a future blog.

Thursday, May 10, 2018

Excel - Get URLs from a range

I'm storing this info here so I can find it later. I have a spreadsheet with a lot of URLs stored in a column (more than 150). The URLs are embedded in files stored on a remote server so when you click on the file it will take you to the file.

Great, except I need the actual hyperlink info. I could get the URL by right clicking then cutting/pasting each file, but that would take hours. So I need a custom function or macro to extract them for me.

The basic info is copied from this location:

The cure for tedium—like them or not—is a macro. With a macro, getting at the underlying URL for a hyperlink is child's play. All the macro needs to do is pay attention to the Address property of the hyperlink. The following is an example of a macro that will find each hyperlink in a worksheet, extract each one's URL, and stick that URL in the cell directly to the right of the hyperlink.
Sub ExtractHL()
    Dim HL As Hyperlink
    For Each HL In ActiveSheet.Hyperlinks
        HL.Range.Offset(0, 1).Value = HL.Address
End Sub
Instead of a "brute force" macro, you could also create a user-defined function that would extract and return the URL for any hyperlink at which it was pointed:
Function GetURL(rng As Range) As String
    On Error Resume Next
    GetURL = rng.Hyperlinks(1).Address
End Function
In this case you can place it where you want. If you want, for example, the URL from a hyperlink in A1 to be listed in cell C25, then in cell C25 you would enter the following formula:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3281) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Extracting URLs from 

How to create a custom function
That's all good and fun, but it doesn't tell me how to create a custom function. So, here's another source:

BEFORE YOU PROCEED, you will need to go into your Excel settings and, in the File > Options you need to edit the Auto correct setting that automatically turns URLs into hyperlinks.
  1. Press Alt + F11.
    This gets you to the Visual Basic Editor, where VBA is written.
    You can also click the Visual Basic button on the Developer tab of the Ribbon. The Developer tab is visible only if the Developer checkbox is checked on the Customize Ribbon tab of the Excel Options dialog box.
  2. Choose Insert→Module in the editor.
    You have an empty code module sitting in front of you. Now it’s time to create your very own function!
  3. Type this programming code, shown in the following figure:
    Writing your own function.
    Writing your own function.
  4. Function GetURL(rng As Range) As String
        On Error Resume Next
        GetURL = rng.Hyperlinks(1).Address
    End Function
  5. Macros and VBA programming can be saved only in a macro-enabled workbook.
    After you type the first line and press Enter, the last one appears automatically. This example function adds two numbers, and the word Public lists the function in the Insert Function dialog box. You may have to find the Excel workbook on the Windows taskbar because the Visual Basic Editor runs as a separate program. Or press Alt+ F11 to toggle back to the Workbook. Save the file as a Macro-Enabled Excel file (necessary).
  6. Return to Excel.
  7. Click the Insert Function button on the Formulas tab to display the Insert Function dialog box.
    Finding the function in the User Defined category.
    Finding the function in the User Defined category.
  8. Click OK.
    The Function Arguments dialog box opens, ready to receive the arguments. Isn’t this incredible? It’s as though you are creating an extension to Excel, and in essence, you are.
    Using the custom Add function.
    Using the custom Add function.

BTW: I am not copying this here to rip off anybody's content. I'm just storing it here in case the original links ever go away.