IP control with Excel + VBA

Asked

Viewed 2,156 times

0

I’m building some functions to analyze an IP table. I just used VBA for the first time in my life and in my research I found nothing that could help me.

I need to create a function that looks for the smallest missing number in an Excel column. The user must pass the initial and final value, for example 5777 and 6287. The function should search in the predetermined column (which will be sorted) between these indexes and, when finding a non-existent index, inform the user.

These indexes represent a transformation in the IP and the interval between them represents a subnet. When it returns me the lowest available index, the inverse transformation will show me the lowest free IP in that range.

I know I could create iterations from the initial index and with each iteration compare the index with all available indices. Only this would require two iterations and in case there are no addresses available, it would do n (m = Qtd of Ips used across the network and n = Ips quantity in the subnet). That for a 512 IP subnet would be an absurd number of iterations.

Would there be some other way to do this without putting too much strain on the execution of the code?

excel vba networks

  • I didn’t understand the "when finding a non-existent index, informing the user." part, could explain better what I meant?

  • The index is nothing more than a more "computable" IP. One of the problems I have is that Ips are dispersed within the subnet interval. This makes management very difficult. The purpose of this function is to make the new machines cover the holes by assuming smaller Ips. That is, I want to increase the concentration of machines at the beginning of the interval. ?

  • To illustrate, one of the subnets here has 510 addresses, in theory. I use only 246 of them. The machines should concentrate in the first half and so it would be easy to find a free address: it would always be the last used +1. In my current situation, the coaches think up a number and kick until they get it right. Since I can’t force the technician to do a search for the smallest IP available (it would take a long time), I want the system to indicate the best address for it. That way, in some time, we will have the ideal situation of Ips being in a continuous interval.

  • If I understood correctly, in theory, you wouldn’t even need to use VBA for this. If no more pronounce, later put a "possible" solution to the proposed problem.

  • Don’t wait no, I need it with a certain urgency.

1 answer

2

See in the image if this would be enough.

To facilitate possible gluing, the formula is:

=SE(É.NÃO.DISP(PROCV( <número de IP da lista completa>; <lista de IPs já utilizados, pode ser um intervalo nomeado>; 1; FALSO)); "Texto para Livre"; "Texto para em uso")

Detail, although you cite that the listing of Ips in use is in ascending order, the way the formula has been configured, this has no importance.

Recorte de planilha de exemplo

  • Yes, it is not as elaborate as what I had imagined but it solves and is quite efficient. Thank you very much!

  • I’m glad it helped. Anyway, if you need it, you can use it as a basis for something more "personalized".

Browser other questions tagged excel vba networks

You are not signed in. Login or sign up in order to post.