Assign IP Address by VBA-Excel

Assign IP Address by VBA-Excel

What is Excel VBA

VBA stands for Visual Basic for Applications. Excel VBA is Microsoft is programming language for Excel and all the other Microsoft Office programs, like Word and PowerPoint. The Office suite programs all share a common programming language.

Why Use Excel VBA?

While users cannot directly manipulate the main Excel software through VBA, they can, however, master the art of making macros to optimize their time in Excel. There are two ways to make Excel macros.

The first method is to use the Macro Recorder. After activating the recorder, Excel will record all the steps a user makes and save it as a “process” known as a macro. When the user ends the recorder, this macro is saved and can be assigned to a button that will run the exact same process again when clicked. This method is relatively simple and requires no inherent knowledge of the VBA code. This method will work for simple processes.

However, the downfall of this method is that it is not very customizable, and the macro will mimic the user’s input exactly. By default, recorder macros also use absolute referencing instead of relative referencing. IKt means that macros made in this way are very hard to use with variables and “smart” processes.

What is an IP Address – Definition and Explanation?

What is an IP?

An IP address is a string of numbers separated by periods. IP addresses are expressed as a set of four numbers — an example address might be 192.168.0.1 each number in the set can range from 0 to 255. So, the full IP addressing range goes from 0.0.0.0 to 255.255.255.255.

Types of IP addresses

Consumer IP address

Every individual or business with an internet service plan will have two types of IP addresses: their private IP addresses and their public IP address. The terms public and private relate to the network location — that is, a private IP address is used inside a network, while a public one is used outside a network.

Private IP addresses

Every device that connects to your internet network has a private IP address. This includes computers, smartphones, and tablets but also any Bluetooth-enabled devices like speakers, printers, or smart TVs. With the growing internet of things, the number of private IP addresses you have at home is probably growing. Your router needs a way to identify these items separately, and many items need a way to recognize each other. Therefore, your router generates private IP addresses that are unique identifiers for each device that differentiate them on the network.

Public IP addresses

A public IP address is the primary address associated with your whole network. While each connected device has its own IP address, they are also included within the main IP address for your network. As described above, your public IP address is provided to your router by your ISP. Typically, ISPs have a large pool of IP addresses that they distribute to their customers. Your public IP address is the address that all the devices outside your internet network will use to recognize your network.

Public IP addresses

Public IP addresses come in two forms – dynamic and static

Dynamic IP addresses change automatically and regularly. ISPs buy a large pool of IP addresses and assign them automatically to their customers. Periodically, they re-assign them and put the older IP addresses back into the pool to be used for other customers. The rationale for this approach is to generate cost savings for the ISP. Automating the regular movement of IP addresses means they do not have to carry out specific actions to re-establish a customer's IP address if they move home, for example. There are security benefits, too, because a changing IP address makes it harder for criminals to hack into your network interface.

Static IP addresses

In contrast to dynamic IP addresses, static addresses remain consistent. Once the network assigns an IP address, it remains the same. Most individuals and businesses do not need a static IP address, but for businesses that plan to host their own server, it is crucial to have one. This is because a static IP address ensures that websites and email addresses tied to it will have a consistent IP address — vital if you want other devices to be able to find them consistently on the web.

netsh

Netsh is a command-line scripting utility that allows you to, either locally or remotely, display or modify the network configuration of a currently running computer. Used without parameters, netsh opens the Netsh.exe command prompt (that is, netsh>)

Syntax

netsh[ -a AliasFile] [ -c Context ] [-r RemoteComputer] [ -u [ DomainName\ ] UserName ] [ -p Password | *] [{NetshCommand | -f ScriptFile}]

Example

Private sub setip(name,ip,su,gw,dns1,dns2 as string)
dim cmd1,cmd2,cmd3 as string
cmd1="netsh interface IPv4 set address name=""" & name & """ static " & " " & ip & " " & su & " " & gw
cmd2="netsh interface IPv4 set dns name=""" & name & """ static " & dns1
cmd3= "netsh interface IPv4 add dns name=""" & name & """" & "" & dns2 & "index=2"
with CreateObject("Wscript.Shell")
.Run "Cmd /c" & cmd1,0, True
.Run "Cmd /c" & cmd2,0, True
.Run "Cmd /c" & cmd3,0, True
End With
End Sub

Sub Exsetip()
dim name,ip,su,gw,dns1,dns2 as string
name=Sheet2.Range("B12")
ip=Sheet2.Range("C17")
su=Sheet2.Range("C18")
gw=Sheet2.Range("C19")
dns1=Sheet2.Range("C20")
dns2=Sheet2.Range("C21")
End Sub

Please Watcing My Video is Below