This vs Active .workbook Question

[IvenBach]

I have been working with a simple macro that I have recreated several times. I normally put it into Thisworkbook or into its own Module for the file I'm working with. Each time I needed to check against a new manufacturer I exported my required information and retyped the macro.

    For Each r In Selection
        ThisWorkbook.FollowHyperlink "http://www.aemintakes.com/search/product.aspx?Prod=" & Cells(r.Row, 2)
    Next

Since I have started using it daily I tried adding it to my Personal macro workbook. I did that, went to run it and it wasn't functioning properly. When I run the macro normally it only opens the 1st page properly. I added the Debug.Print and noticed that when I ran the macro only the 1st number was printed in the Immediate Window and the rest were blank lines. If step though the code with F8 it opens each page appropriately and without problem (debug is printed properly as well). Here is the what was used.

Sub HYPERLINK()
Debug.Print ThisWorkbook.Name
For Each r In Selection
    Debug.Print Cells(r.Row, 1)
    ThisWorkbook.FollowHyperlink "http://www.aemintakes.com/search/product.aspx?Prod=" & Cells(r.Row, 1)
Next
End Sub

I noticed that the workbookname might have something to do it and switched my code to have Activeworkbook instead of ThisWorkbook. I ran the macro and it worked (glad it worked but wanting to learn). My question is why it works with Activeworkbook and not Thisworkbook. I think I understand but would like some direction from those that actually know what they are doing.

These are some part numbers that should load up product information when the macros run.

21-3059DK,21-202DK,21-2049DK,21-2059DK,21-2259DK,21-2277DK,21-2279DK,21-3059DK
IE [URL]http://www.aemintakes.com/search/product.aspx?Prod=21-3059DK[/URL]

 

[taigovinda]

Not sure I qualify ("those who know what they are doing") but I'll try to answer your question anyway

Yes, thisworkbook will refer to the workbook housing the code, whereas activeworkbook will refer to whatever workbook happens to be active at the moment the code is executed.

If you want to keep referring to one workbook, but don't know that it will continue to be the active workbook, then you could set a variable to refer to that book, something like this:

dim wkBk as workbook
set wkBk = activeworkbook

'do a bunch of other stuff, switch between various workbooks
wkBk.close true 'save and close the original workbook, don't need its name or whether it is active

 

[Norie]

If the code is in the Personal.xls workbook ThisWorkbook will refer to that workbook - not the workbook where you are actually using the code.

By using ActiveWorkbook then the code will refer to, obviously, the active workbook which is probably going to be the workbook you call the code from.

That's how I understand it anyway, perhaps someone else can offer a better (correct?) explanation.

 

[IvenBach]

Thanks to both of you. I figured it was something along those lines. I think it was coming up with a blank for the Cells(r.Row, 2) portion because Sheet1 in Personal is completely empty and trying to pull the empty value and use it.