Thursday, February 24, 2005
How to loop through a list of data on a worksheet by using macros in Excel
How to loop through a list of data on a worksheet by using macros in Excel: "How to loop through a list of data on a worksheet by using macros in Excel
Article ID:299036
Last Review:September 23, 2004
Revision:1.0
This article was previously published under Q299036
SUMMARY
When you write a Microsoft Visual Basic for Applications (VBA) macro, you may have to loop through a list of data on a worksheet. There are several methods for performing this task. The 'More Information' section of this article contains information about the methods that you can use to search the following types of lists: �A list that contains a known, constant number of rows.
�A dynamic list, or a list with an unknown number of rows.
�A list that contains a specific record."
Article ID:299036
Last Review:September 23, 2004
Revision:1.0
This article was previously published under Q299036
SUMMARY
When you write a Microsoft Visual Basic for Applications (VBA) macro, you may have to loop through a list of data on a worksheet. There are several methods for performing this task. The 'More Information' section of this article contains information about the methods that you can use to search the following types of lists: �A list that contains a known, constant number of rows.
�A dynamic list, or a list with an unknown number of rows.
�A list that contains a specific record."
Wednesday, February 23, 2005
Isworksheetopen and Isworkbookopen functions
Here's a IsWorkbookOpen function to check if a workbook is open or not. Returns TRUE or FALSE
Public Function IsWorkbookOpen(ByVal wkbkname As String) As Boolean
Public Function IsWorkbookOpen(ByVal wkbkname As String) As Boolean
Dim Wb As Workbook
'check if its already opened in the Windows collection
For Each Wb In Workbooks
If Wb.Name = wkbkname Then IsWorkbookOpen = True
GoTo exitsub
'check if its already opened in the Windows collection
For Each Wb In Workbooks
If Wb.Name = wkbkname Then IsWorkbookOpen = True
GoTo exitsub
End If
Next Wb
Next Wb
exitsub:
End Sub
End Sub
Here's a IsWorksheetOpen function to check if a worksheet is open or not. Returns TRUE or FALSE
Public Function IsWorksheetOpen(ByVal wsname As String) As Boolean
Dim wk As Worksheet
For Each wk In Worksheets
If wk.Name = wsname Then
IsWorksheetOpen = True
GoTo exitsub
End If
Next wk
exitsub:
End Function
Dim wk As Worksheet
For Each wk In Worksheets
If wk.Name = wsname Then
IsWorksheetOpen = True
GoTo exitsub
End If
Next wk
exitsub:
End Function
Wednesday, February 16, 2005
Change the creation date of a workbook
Two of my colleagues in the office wanted to know how can they change the creation date of a workbook, and I knew there would be some property of the Workbook, which can be accessed thru VBA. So here is the simple sub to get the new creation date and the message boxes will show you the old and the new date.
Public Sub prop()
MsgBox "Old creation date is " & ActiveWorkbook.BuiltinDocumentProperties(11)
'enter the date you want to change and this is the US date format
ActiveWorkbook.BuiltinDocumentProperties(11) = "2/14/2004"
MsgBox "New creation date is " & ActiveWorkbook.BuiltinDocumentProperties(11)
End Sub
- Open the workbook you want to change the creation date of.
- Press Alt + F11 to bring the VBE window.
- Paste this code.
- Then run the code by pressing F5.
- Delete the code after using it.
Public Sub prop()
MsgBox "Old creation date is " & ActiveWorkbook.BuiltinDocumentProperties(11)
'enter the date you want to change and this is the US date format
ActiveWorkbook.BuiltinDocumentProperties(11) = "2/14/2004"
MsgBox "New creation date is " & ActiveWorkbook.BuiltinDocumentProperties(11)
End Sub
Saturday, February 12, 2005
Importing IE bookmark
Steps to get your bookmarks in a database format
First way:
Open Excel>Data>Import External Data>New Web QueryPaste the address of your bookmark on your hardisk in the web query addressSelect the tableImport it to ExcelYou'll see first column has all the addresses of the links, and after few columns the text for that hyperlinkPress Ctrl + H (Find and replace) to remove all the unessesary characters in those two columnssuch as, < ,", etcAutoFilter on the first column for blank rows and delelte those blank rows.There you go; have two columns one with the address and one with the text.If you want to make that address as hyperlink you can use Excel funtion HYPERLINK, check help on that.
Second way;Copy all the text from the from the bookmark.htm file to cell A1Then paste this code after pressing Alt + F11
Sub giveaddress()
Application.ScreenUpdating = False
Dim k As Hyperlink
Dim i As Byte
i = 1
With ActiveSheet
For Each k In ActiveSheet.Hyperlinks
.Cells(i, "B") = k.Address
.Range("A" & i).Copy
.Range("C" & i).PasteSpecial xlPasteValues
i = i + 1
Next
.Columns("C:C").Cut
.Columns("A:A").Select
ActiveSheet.Paste
End With
Application.ScreenUpdating = True
End Sub
Run this macro pressing Alt + F8 in the worksheet you've pasted the links and select macro giveaddress and run it.
Column B will have the addresses for the links in column A you'll have the text.
Formula to get the site name, i.e. text of a URL excluding http
=IF(MID(C3,1,11)="http://www.",MID(C3,12,FIND("/",C3,10)-12),"")
Formula to get just the site name, i.e. including http and .com or .org
=MID(C2,1,FIND("/",C2,FIND("//",C2,1)+2))
Thursday, February 10, 2005
Excel Function to check if text is palindrome or not
Definition of palindrome as given in Answers.com
pal·in·drome (păl'ĭn-drōm')
n.
A word, phrase, verse, or sentence that reads the same backward or forward. For example: A man, a plan, a canal, Panama!
I've written this function in Excel 2003- VBA and it'll return the values True or False depending on the result. The function is not case sensitive, it can take numbers as input, the text can have spaces and characters like, [, ? . ] . You can add more if you want, it's just an or statement. There's minimal error handling though.
Function IsPalindrome(ByVal inputtext As String) As Boolean
Dim i As Long, length As Long
Dim tmp2 As String, tmp3 As String
On Error GoTo exitsub
length = Len(inputtext)
If length = 0 Then
MsgBox "Enter some value, exiting sub now", vbCritical, "Empty cell"
GoTo exitsub
End If
inputtext = LCase(inputtext)
tmp2 = ""
tmp3 = ""
'removing spaces and other chars
For i = 1 To length
tmp2 = Mid(inputtext, i, 1)
If tmp2 = " " Or tmp2 = "," Or tmp2 = "?" Or tmp2 = "." Then GoTo continue1
tmp3 = tmp3 & tmp2
continue1:
Next i
inputtext = tmp3
'reversing the word and checking
If inputtext = StrReverse(inputtext) Then
IsPalindrome = True
Else: IsPalindrome = False
End If
exitsub:
End Function
pal·in·drome (păl'ĭn-drōm')
n.
A word, phrase, verse, or sentence that reads the same backward or forward. For example: A man, a plan, a canal, Panama!
I've written this function in Excel 2003- VBA and it'll return the values True or False depending on the result. The function is not case sensitive, it can take numbers as input, the text can have spaces and characters like, [, ? . ] . You can add more if you want, it's just an or statement. There's minimal error handling though.
Function IsPalindrome(ByVal inputtext As String) As Boolean
Dim i As Long, length As Long
Dim tmp2 As String, tmp3 As String
On Error GoTo exitsub
length = Len(inputtext)
If length = 0 Then
MsgBox "Enter some value, exiting sub now", vbCritical, "Empty cell"
GoTo exitsub
End If
inputtext = LCase(inputtext)
tmp2 = ""
tmp3 = ""
'removing spaces and other chars
For i = 1 To length
tmp2 = Mid(inputtext, i, 1)
If tmp2 = " " Or tmp2 = "," Or tmp2 = "?" Or tmp2 = "." Then GoTo continue1
tmp3 = tmp3 & tmp2
continue1:
Next i
inputtext = tmp3
'reversing the word and checking
If inputtext = StrReverse(inputtext) Then
IsPalindrome = True
Else: IsPalindrome = False
End If
exitsub:
End Function
Labels: String Operations
Wednesday, February 09, 2005
Count number of asterix (*)
To count number of asterix (*) in a column use this formula:
=countif(A1:A3, "=~*")
=countif(A1:A3, "=~*")
Labels: String Operations
