Author Topic: Excel (VBA) macros  (Read 5330 times)

ronaldo818

  • New Community Member
  • Posts: 1
  • Hero Points: 0
Excel (VBA) macros
« on: October 31, 2011, 05:58:51 pm »
Yes/No
There are times you may want users to click Yes or No. Just insert this line of code. Here the Select Case statement is used.

YesNo = MsgBox("This macro will ... Do you want to continue?", vbYesNo + vbCritical, "Caution")
Select Case YesNo
Case vbYes
'Insert your code here if Yes is clicked
Case vbNo
'Insert your code here if No is clicked
End Select

Counting Rows & Columns & Sheets
When you have selected a range, it is sometimes useful to know how many rows or columns you have selected as this information can be used in your macros (for eg when you have reached the end, you will know it is time to stop the macros. This macro will do the trick.

Sub Count()
myCount = Selection.Rows.Count    'Change Rows to Columns to count columns
MsgBox myCount
End Sub

Close all Files
Sometimes you may want to close all files without saving. Doing it manually is a hassle with the question "Do you wanna save?"

Sub CloseAll()
Application.DisplayAlerts = False
myTotal = Workbooks.Count
For i = 1 To myTotal
    ActiveWorkbook.Close
Next i
End Sub

Current Cell Content
Sometimes we need to know what the cell contains ie dates, text or formulas before taking a course of action. In this example a message box is displayed. Replace this with a macro should you require another course of action.

Sub ContentChk()
If Application.IsText(ActiveCell) = True Then
MsgBox "Text"             'replace this line with your macro
Else
If ActiveCell = "" Then
MsgBox "Blank cell"    'replace this line with your macro
Else
End If
If ActiveCell.HasFormula Then
MsgBox "formula"         'replace this line with your macro
Else
End If
If IsDate(ActiveCell.Value) = True Then
MsgBox "date"               'replace this line with your macro
Else
End If
End If
End Sub

Deleting Empty Rows
To delete empty rows in a selected range we can use the following macro. The macro here uses the For Next Loop. First the macro counts the rows in a selected range to determine the when the macro should stop. The For Next statement acts as a counter.

Sub DelEmptyRow()
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value = "" Then    'You can replace "" with 0 to delete rows with 'the value zero
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
Application.ScreenUpdating = True
End Sub

Deleting Range Names
To delete all the range names in your workbook, this macro will do the trick.

Sub DeleteNames()
Dim NameX As Name
For Each NameX In Names
ActiveWorkbook.Names(NameX.Name).Delete
Next NameX
End Sub   

Current Cell Position
Sometimes we need to know the current cell position. This would do the trick.

Sub MyPosition()
myRow = ActiveCell.Row
myCol = ActiveCell.Column
Msgbox myRow & "," & myCol
End Sub
« Last Edit: October 31, 2011, 06:07:24 pm by ronaldo818 »

Phil Barila

  • Senior Community Member
  • Posts: 742
  • Hero Points: 61
Re: Excel (VBA) macros
« Reply #1 on: October 31, 2011, 07:10:20 pm »
@Ronaldo818, you've put so much work into this, I'm a bit reluctant to tell you that this is a contest for SlickEdit Slick C macros.  Maybe @Lyndsey will throw you a bone and enter you into the competition anyway, but you should not expect that.
Nice work on the VBA macros, though.

ehab

  • Senior Community Member
  • Posts: 285
  • Hero Points: 15
  • coding with SE is like playing music
Re: Excel (VBA) macros
« Reply #2 on: November 01, 2011, 09:20:08 am »
i can start adding my Scala code :D

lclevesy

  • Guest
Re: Excel (VBA) macros
« Reply #3 on: November 01, 2011, 03:05:46 pm »
@ronaldo818 unfortunately this does not qualify for the contest, so you are not in the running for the iPad 2, however, I'm happy to offer you a SlickEdit t-shirt. Email your size and address to marketing@slickedit.com and I'll have it sent out!