You can use the TIME() function to add or subtract hours, minutes or seconds in Excel. The TIME function takes 3 parameters; HOURS, MINUTES, SECONDS. To use the TIME function, consider the following examples;
If Cell B14 Contains 12:00AM December 30, the formula =+B14-TIME(13,0,0) would produce 11:00PM December 29
or
If Cell B14 Contains 12:00AM December 30, the formula =+B14+TIME(1,0,0) would produce 1:00AM December 29
Simple?
Want to clear your current worksheet? Use this:
'Select the whole spreadsheet
Cells.Select
'Delete Everything
Selection.ClearContents
'Make Cell A1 the Currently Selected Cell
Range("A1").Select
Want to quickly view your Excel Macro's? Click ALT-F11. This will pull up the Microsoft Visual Basic Editor with all of your current workbooks' macros. Quick, simple access to your macros!
I frequently import a specific data-set of text data into Excel. After I import the data, I need to delete the last line of data. Following is a cool macro to do this.
Range("A1").Select ‘Select cell A1
Selection.End(xlDown).Select ‘Press the END-DOWN keys to move to the last row of data
Selection.EntireRow.Delete ‘Delete that row
Here is a quick script to check if a file exists.
Sub TestForFileSubroutine()
‘Create a File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
‘Set a variable that points to the File
cFilePathAndName = “c:\TEMP\MyFileName.TXT”
‘Does the file exist?
If Not objFSO.FileExists(cFilePathAndName) Then
MsgBox "FILE DOES NOT EXIST!"
Exit Sub
End If
‘Do a little clean up and delete the File System Object
Set objFSO = Nothing
End Sub
To get the name of the currently logged in user, you need to first, create a function to call a Windows API function to get the user name. The function looks like this;
'Define the Window API Function
Public Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
'This is Function Returns the user name of the currently logged in user
'This works in Windows 95, XP and Vista
Function ReturnUserName() As String
Dim rString As String * 255, sLen As Long, tString As String
tString = ""
On Error Resume Next
sLen = GetUserName(rString, 255)
sLen = InStr(1, rString, Chr(0))
If sLen > 0 Then
tString = Left(rString, sLen - 1)
Else
tString = rString
End If
On Error GoTo 0
ReturnUserName = UCase(Trim(tString))
End Function
Next, within your macro code, call the function and the username will be returned in a variable. For example;
cUser = ReturnUserName()
...will return the username in a variable called cUser.
