Step‑by‑Step Guide to Encoding Data in Excel Using VBA

Step 1 – Making the Dataset with Proper Parameters

You can see our dataset in the image.

Step 2 – Using the Developer Tab

The Excel built-in Developer tab offers the tools required to use Visual Basic for Applications (VBA) to execute a macro. Typically, the tab is disabled. To make it visible on the toolbar at the top of the Excel window, it has to first be activated in the Options section of the Menu bar.

Open the Developer tab.
Select the Visual Basic command.

Step‑by‑Step Guide to Encoding Data in Excel Using VBA

Step 3 – Creating a VBA User-Defined Function

The Visual Basic window will open.
From the Insert option, choose Module to write the VBA code.

Step‑by‑Step Guide to Encoding Data in Excel Using VBA

Paste the following VBA code into the Module.
Save the code.

Function Encoding(text$)
Dim i
With CreateObject("ADODB.Stream")
.Open: .Type = 2: .Charset = "utf-8"
.WriteText text: .Position = 0: .Type = 1: i = .Read
With CreateObject("Microsoft.XMLDOM").createElement("b64")
.DataType = "bin.base64": .nodeTypedValue = i
Encoding = Replace(Mid(.text, 5), vbLf, "")
End With
.Close
End With
End Function

Step‑by‑Step Guide to Encoding Data in Excel Using VBA