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.

This VBA code was light and easy... and worked at one point. I might be able to work with C# and had not thought of that at all. If you don't mind, what would you recommend?

Ok, so .to my amazement.. it is working again.

Nothing has changed, nothing at all. I have been repeating the same functions / tests and at once, I got a IP of my own host.

And I haven't done a thing to get it this way.

This is really bizarre and I know it will break again. Guess I'll just blame it on Microsoft. ;)

Ok so it's broken again. This is amusing.

*bangs head on desk*

The last time it broke, I ran a SocketsInitialize() function and tried the GetIP function again. Well, that worked. That might be the route I try from now on when I find this IP look up breaking.

Public Function SocketsInitialize() As Boolean
Dim WSAD As WSADATA
SocketsInitialize = WSAStartup(WS_VERSION_REQD, WSAD) = IP_SUCCESS
End Function

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.