Add GUI control
1. Enable Developer menu
- Right click anywhere in toolbar, and click customize this Ribbon...
- In main tabs, select developer and click OK
2. In developer menu,
- Click Insert - GUI control (like, button)
3. Draw the GUI control on the sheet.
4. Right click on button, choose Assign Macro... and click on New button.
5. Write VBScript code to perform any action for the new function
Sub calculateSum()
. . . . .
End Sub
WRITING MACRO CODE
COPY DATA FROM ONE CELL TO OTHER
Cells(RowIdx, ColIdx).Value = Cells(RowIdx, ColIdx).Value
Cells(2, 3).Value = Cells(2, 6).Value
GET CELL VALUE TO STRING VAR
Dim Name As String
Name = Cells(2, 3).Value
REPLACE STRINGS
Name = Replace(Name, "SE", "Software Engineer")
' Replace New line with space
Name = Replace(Name, vbLf, " ")
COUNT NUMBER OF SHEETS IN THE WORKBOOK
Dim sheetCount As Integer
sheetCount = ActiveWorkbook.Worksheets.Count
GET THE SHEET NAME
sheetName = ActiveWorkbook.Worksheets(sheetIdx).Name
' sheetIdx starts with 1 and goes till n
COUNT TOTAL ROWS USED (WITH DATA) IN A SHEET
Dim EndRow As Long
With ActiveWorkbook.Worksheets(sheetIdx).UsedRange
EndRow = .Cells(.Cells.Count).Row
End With
GET DATA FROM ANY CELL OF ANY SHEET
Data_Name = ActiveWorkbook.Worksheets(sheetIdx).Cells(RowIdx, ColIdx)
TRIM THE STRING
Data_Name = Trim(Data_Name)
DISPLAY MESSAGE BOX
MsgBox "Sheet : " & sheetName
GET SHEET NAME AND DATA (CANDIDATE NAMES) FROM ALL SHEETS
Assuming, Candidate Name column is 4th on each sheet.
Dim sheetCount As Integer
Dim EndRow As Long
ColIdx_Name = 4
sheetCount = ActiveWorkbook.Worksheets.Count
For sheetIdx = 1 To sheetCount
sheetName = ActiveWorkbook.Worksheets(sheetIdx).Name
With ActiveWorkbook.Worksheets(sheetIdx).UsedRange
EndRow = .Cells(.Cells.Count).Row
End With
For RowIdx = 2 To EndRow
Data_Name = Trim(ActiveWorkbook.Worksheets(sheetIdx).Cells(RowIdx, ColIdx_Name))
Next RowIdx
Next sheetIdx
CREATING / USING FUNCTIONS
' Assign macro to Button1
Sub button_Export_Name_click()
exportData columnIdx:=2, FileExt:=".txt"
End Sub
' Assign macro to Button2
Sub button_Export_Dept_click()
exportData columnIdx:=4, FileExt:=".txt"
End Sub
' Common function called by both the buttons
Public Sub exportData(columnIdx As Integer, FileExt As String)
. . .
End Sub
FILE AND FOLDER OPERATIONS
Set objFSO = CreateObject("Scripting.FileSystemObject")
' Check if a folder (location = parellel to the workbook) exists ; if not, create it
strFolder = ThisWorkbook.Path & "\exportedKeyFiles"
If objFSO.FolderExists(strFolder) = False Then
objFSO.CreateFolder strFolder
End If
' Remove file if already exist
FName = strFolder & "\projectConfig.txt"
If objFSO.fileExists(FName) Then
objFSO.DeleteFile (FName)
End If
' Open a text file for Write with append
Open FName For Append Access Write As #FNum
' Write to text file
line = "Hello this is first line" & vbCrLf
Print #FNum, line
| 
