Things I wished I know when I Started programming VBA

-
1.       Use auto complete word: Control + Space
This invokes intellisense
A further press of the Tab key will select the highlighted word
If the Spacebar key instead of the Tab key is pressed it will add a space after selecting the highlighted word.
2.       View the definition: Shift + F2  and back to View the last position: Control + Shift + F2
2.1          If we have the following code in our module:
Sub Calc()  
                Dim result as Long
                Result = PerformCalc(5, 10)
                MsgBox “Result is “ & result
                End Sub
If you right click and select Definition or press Shift + F2 on PerformCalc it takes as to the definition which is
                Function PerformCalc(x as Long,  y as Long) as Long
                PerformCalc = (x + y) * 2
                End Function
If you right click and select Last Position or press Control + Shift + F2 it takes you back to the original code.
2.2          Another way to use the definition is if you look at constants in VBA
                If we have the following code :
                If VarType(arr)  = vbDate Then
                We want to check to see if vbDate is a date. You can see that vbDate is a constant.
We can put VarType(arr) in our watch window. Say for instance the watch window give a value of 8 and vbDate have a constant value of 7. Now we want to find out what is the constant of the other value 8.
What you can do is to click on vbDate and press Shift + F2 and this will bring up all the members or siblings  of vbVarType.
So then you can click on any one in the list and look at the bottom of the screen and it will give you the value of the vbVarType that you have selected.
To close this list you can click the close button on the top right of press Control + F4
3.       Using the Range Address
The following respective code in the watch window will give the range that is applicable:
                                                               i.      shOrders.Cells(i,4).Offset(2,-3).Address ß will give the cell address e.g. “$A$6”
                                                             ii.      shOrders.Cells(i,4).Offset(2,-3).Parent.Name ß will give the sheet name e.g. “Orders”
                                                            iii.      shOrders.Cells(i,4).Offset(2,-3).Parent.Parent.Name  ß will give the workbook name e.g. “Things I wished I know when I started VBA.xlsm”

4.       Getting the range from the user

Initially I thought you have to ask the user via a userform with text box and ask them to put in the range.
There is however a much easier and powerful way to get the range.
Simply use the inputbox and set the type to 8.
e.g. Set rg = Application.InputBox(“Please enter the range ”, Type:= 8)

5.       Reading from closed workbooks
It is possible using ADO
The following code can be used and the only change will be to insert the closed file name that contains the data.
‘To add ADO reference select Tools->Reference and
‘check “Microsoft ActiveX Data Objects 6.1 Library”
Sub UseADO
‘Get the file name
Dim filename as String
filename = ThisWorkbook.Path & Application.PathSeperator & “Things I wished I know when I started VBA Data.xlsx”
‘Get the connection
Dim conn  as new ADODB.Connection
conn.Open “Provider=Microsoft.ACE.OLEDB.12.0;” & “Data Source=” & filename & “;” & _
“Extended Properties=””Excel 12.0; HDR=Yes;””;”
‘Create the SQL Query
Dim query as String
query = “Select * from [Sales$] where [First name] = ‘Alan’ “   
query = “Select [First Name], sum[Amount] from [Sales$] Group by [First Name]”
‘Get the data from the workbook
Dim rs As New Recordset
rs.Open query, conn
‘Write the data
shResult.Cells.Clearcontents
shResult.Range(“A1”).CopyFromRecordset rs
‘Close the connection
conn.Close
End Sub

Comments

Post a Comment

Popular posts from this blog

VBA Developer - Digitally sign your macro project

-
Image
Digitally sign your macro project This article describes how you can digitally sign a macro project by using a certificate. If you don't already have a digital certificate, you must obtain one. To test macro projects on your own computer, you can create your own self-signing certificate by using the Selfcert.exe tool. Obtain a digital certificate for signing You can obtain a digital certificate from a commercial certificate authority (CA) or from your internal security administrator or information technology (IT) professional. To learn more about certificate authorities that offer services for Microsoft products, see the list of  Microsoft root certificate program members . Your macro projects and Microsoft Office Because a digital certificate that you create isn't issued by a formal trusted certificate authority, macro projects that are signed by using such a certificate are known as  self-signed projects . Microsoft Office trusts a self-s...
Read more

Distribute your VBA project to other Microsoft Users

-
We often spend a lot of time to write VBA code to assist us in completing tasks in Microsoft Applications whether an Excel spreadsheet, Access database, Outlook Message, Word Document. This code can be used by other users on the same scenario. We can make our code available to other users. This code can be protected to ensure integrity. The procedure below describe how to create distributed software for an Excel application. The process for other Microsoft applications will be similar. 1. Once you have completed, debugged and tested your program and are happy that it works, protect      your code as follows: Right click on your module or project name and select VBAProject Properties. Assume your sub name is "ProtectedCode" In the General Tab type in the name of your project e.g. "CodeForDistribution". In the Protection Tab select Lock project for viewing  and enter a password for your project. Click on OK. 2. Save your project but select the file type...
Read more

How far can you stretch the Excel LET function?

-
Image
 Excel LET Function Definition and background and Syntax The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax. To use the LET function in Excel, you define pairs of names and associated values, and a calculation that uses them all. You must define at least one name/value pair (a variable), and LET supports up to 126. =LET(name1_of_variable1, name_value1, calculation_or_name2_of_variable2,[name_value2_of_variable2, calculation_or_name3_of_variable3  . . .  ]) The LET function can take up to 132 variables. The LET function with more than one variable can look like this: =LET(name of variable1, name_value1, name of variable2, name_value2, calculation) Argument Description name1 Required The first name to assign. Must start wi...
Read more