Here we are coming with one more exciting post which can help you to solve very basic but very important problems while writing VBA codes. For example, what VBA code you write to add borders in a range?
You might be using a code like below:
Range("A1:D10").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("A1:D10").Borders.LineStyle = xlContinuous
In this post, we have shared similar codes that will makes your life easy.
The following code returns the extension from any file name:
Code:
Right(strFilePath,instr(1,StrReverse(strFilePath),"."))
Following code reads the text in the given cell or range. The code can be used where you want to alert the user through system audio device:
Codes:
Range("A1").Speak
Cells(1,1).Speak
This code protects the sheet for manual inputs; however if the changes are done through programming then it will allow the changes.
Code:
Sheet1.Protect Password:="123", UserInterfaceOnly:=True
In some situations when you want to accept other actions or interrupts performed by the user then you can make use of DoEvents. For example, you are running a long loop and you want to allow the user to stop the loop in-between.
Public bStop As Boolean
‘This function runs a loop on each row of the sheet. If the Boolean variable is True then it will exit from the loop
Sub StartLoop()
Dim lCounter As Long
For lCounter = 1 To Rows.Count
DoEvents
If bStop = True Then
Exit For
End If
'Code to be executed for each loop
UserForm1.Label1.Caption = lCounter
Next
End Sub
‘This button event will change the Boolean variable to True
Private Sub cmdStop_Click()
bStop = True
End Sub
When you want to perform certain tasks on each cell of a large Excel range then a normal loop may take lot of time to complete the task. It would be good idea if you convert the range to an array, perform the required changes and write it back to the Excel range. As the array variable is stored in RAM instead of Hard Disc, the loop will run faster.
Sub RangeToArray()
Dim varArray() As Variant
Dim lRow As Long
Dim lColumn As Long
'Convert the range to array
varArray = Range("A1:Z10000")
For lRow = 1 To 10000
For lColumn = 1 To 26
'Perform changes in the array
varArray(lRow, lColumn) = varArray(lRow, lColumn) + 1
Next
Next
'Write the modified array back to the Excel
Range("A1:Z10000") = varArray
End Sub
This function can be used to evaluate formulas or arithmetic calculations directly in the coding. This is helpful when you do not want to perform these calculations on a cell.
Code:
Evaluate(“=VLOOKUP(7,A:B,2,0)”)
When you are performing multiple changes or navigation in the Excel sheet, Excel application keeps on updating the screen to show the latest changes. This normally slowdowns performance of the code. To solve this problem, you can use ScreenUpdating property of Excel application. When you turn it False then screen will not be updated during code execution. If the property is True then screen will be updated as normal.
Code:
Application.ScreenUpdating=False
‘Perform changes in sheet
Application.ScreenUpdating=True
When you need to create a folder through programming, you can make use of MkDir VBA function for creating a folder.
Code:
MkDir (strFolderPath)
When you need to delete a folder through programming, you can use RmDir VBA function like below.
Code:
RmDir (strFolderPath)
Code:
Kill ("E:\Work\ExcelSirJi\Folder 1\*")
RmDir ("E:\Work\ExcelSirJi\Folder 1")
When you need to rename a file or files then you can make use of Name VBA function to easily rename the file(s). Note that you can also use Name VBA function to move the files from one folder to another. Have a look at the examples mentioned below.
Code:
Name strSourceFilePath As strDestinationFilePath
Thanks for reading the article, subscribe us to get more VBA tricks
In MS Access, the best way to create a multiuser tool is to divide your solution. One part acts as interface and other one acts as database. You can have multiple copies of the interface distributed to users which are connected to central MS Access database saved at common shared drive. To connect the interface to database, you can use link table feature (Access>External Data>Import & Link) available in MS Access. Below is a commonly required VBA code which helps the developers to re-link MS Access linked tables when the database is renamed or moved to other location
What is Pareto Chart? Pareto principle was introduced by Italian Economist Vilfredo Pareto. He stated that 80% of the effects are caused by 20% of the causes. So if we closely monitor and solve 20% of…
VBA Code to check if folder exist Validation is one of the important parts of any programming language. As per few studies, 60% of the code is focused on validating input or output. In this…
VBA CODE TO HIDE MENU RIBBON IN MS ACCESS In MS Access, there are multiple ways to protect your code or tool from un-authorized access and edits. Some developers prefers to hide MS Access ribbons…
This Excel VBA Code converts the excel range into HTML and also can convert Excel to HTML Table to paste data on Outlook Email Body
VBA Code To Add Items In Listbox Control Using ListBox in Userform is very common. You can use ListBox.AddItem function to add items in the listbox.; however, it is little difficult to add items in…