Excel VBA - Hostname to IP Address?

Question

I have a list of domain names in column A. And, in Column B, I would like to show the IP address of the site in the cell from column A.

I have a VBA function that gets the IP address:
GetIPFromHostName

So, if I want to retrieve the IP address for yahoo.com, I would type yahoo.com in A1, and in Cell B1, I would enter:

=GetIPFromHostName(A1)

This should (theoretically) display the IP for yahoo.com. Mind you, don't get hung up on multiple IPs here - that's not the problem.

The function does not work when it's in the Excel cell. It has worked in the past, infrequently and on different occasions. But, other times it does not work at all.

Complicating things further, this piece of a sub function in VBA pops a message box works and it works great, reads right from the Excel sheet:

MsgBox GetIPFromHostName( Range("A1").Value)

I'm so pwned by this. Help if you can!

BTB, I do not believe timeouts are the problem either -- since the alerts provide instant replies to the IP requests.

Answer 1

There is a few things you can look into,



1. firewall install or settings change recently, Excel or the dll used in the code might be block from accessing the web. If yes, check the firewall traffic log and make necessary change.



2. the dll or activex component stated in the code might be removed/overwritten, either by uninstallation or installation of other web accessing program recently



3. corruption of excel program that require repair/reinstall of Ms Office, go to menu help, detect and repair to have a try

The msgbox that still pop up means, in most case, the code up to that part is still good, In some case, the error can be code to suppress error, so error might be hidden.

Answer 2

Cheap Online Shop : http://market2.w3org.pw/vfn2h

Answer 3

I could tell you how to do it in C/C++, C#, but not a clue in VBA. Considered using one of these?

Answer 4

Hurrah, that's what I was exploring for! Thanks to author of this question.