Wednesday, 14 March 2012

Using VBA

In Excel for example you can use VB code to manipulate certain things within the rows/columns and objects or even work with external data sources to import data and display it on the sheet. For example here is a simple bit of code to press a button on the sheet, bring up a dialog box where the user inputs a new sheet name and then a log is written in the spreadsheet of when the name was last changed and to what value. Here I am using 2003 Excel.

'New sub routine created with name reflective of the work to be done

Sub ChangeSheetName()

'Define variable to hold input from user

Dim NewName As String

'Firstly populate the variable with the user input then process

NewName = InputBox("Enter the new name of the sheet")

'Check if the variable is empty if so exit the sub
'If the value input by user is not empty then set the active sheet to this value
'Put a line of text in the first cell of the sheet
'then add current date to next cell
'then add the value that the sheet name was set to in the next cell
'in each of those processes do an autofit for the column so that log note
'looks like a sentence

If NewName = "" Then
 Exit Sub
 With ActiveSheet
  .Name = NewName
  Selection.Value = "This sheet's name was last changed on the :"
  Selection.Value = "=now()"
  Selection.Value = "To: " & NewName
 End With
End If
End Sub