VBA Tips and Tricks

RSS Feed Homepage

Articles: 1, 2, 3

Disable Cut & Copy from Popup menu (Excel VBA/Word VBA)
2007-08-13 04:22:00
Here is a simple way to disable the Cut & Copy in the Popup menuSub Disable_Buttons()Dim oC1 As CommandBarSet oC1 = Application.CommandBars("CELL")oC1.Contro ls("Cu&t").Enabled = FalseoC1.Controls("&Copy").Enabled = FalseEnd SubOther menu items can also be handled similarlyTo disable the Insert & Delete in the Popup menuoC1.Controls("&Insert...").Enable d = FalseoC1.Controls("&Delete...").Enabl ed = False
More About: Word , Excel , Menu

Excel Height of Row - RowHeight using Excel VBA
2007-07-26 04:35:00
RowHeight property is used to get the HeightReturns the height of all the rows in the range specified, measured in points (point: Unit of measure referring to the height of a printed character. A point equals 1/72 of an inch, or approximately 1/28 of a centimeter.). Returns null if the rows in the specified range aren’t all the same height. Read/write Variant.You can use the Height property to return the total height of a range of cells.Sub Change_Header_Row_Height()MsgBox "RowHeight = " & Range("A1").RowHeight _& vbCrLf & "Height = " & Range("A1").HeightRange("A1").RowHeight = 90End SubDifferences between RowHeight and Height include the following:Height is read-only.If you return the RowHeight property of several rows, you will either get the row height of each of the rows (if all the rows are the same height) or null (if they’re different heights). If you return the Height property of several rows, you will get the total height of all the rows.Range("A1").Height = 90 w...
More About: Excel

Get the Height & Width of Shapes / Figures in Word Document (Word VBA)
2007-07-08 14:57:00
Height & Width of Shapes / InlineShapes in Word Document (Word VBA)Figures are embedded in the word document and when they move they are a nemesis. Programmers are often given the job of aligning, resizing etc. Here is a simple example to get the height of the Shapes. Here you will notice that a particular inline shape has been singled out. You will get to know this if you run this in the document:)Sub Figure_Attributes() Dim sRep sRep = "" For I = 1 To ActiveDocument.InlineShapes.Count If ((ActiveDocument.InlineShapes(I).Type > 0 And ActiveDocument.InlineShapes(I).Type 7 And ActiveDocument.InlineShapes(I).Type Height = ActiveDocument.InlineShapes(I).Height Width1 = ActiveDocument.InlineShapes(I).Width ActiveDocument.InlineShapes(I).Select If Selection.Fields.Count = 0 Then sRep = sRep & fname & vbTab & Height & vbTab & Width1 & vbCr End If End If Next I For I = 1 To Acti...

Paragraph & Character Styles in Word Document (Word VBA)
2007-07-08 14:46:00
Export Char acter Styles from Word Document / Export Paragraph Styles from Word DocumentHere is the simple macro for extracting character & paragraph styles from a Word document. This exports the styles to a new word documentSub Export_Styles_In_Document() Dim oSource As Document Dim oRep As Document Dim oPara As Paragraph Set oRep = Documents.Add Set oSource = ActiveDocument For I = 1 To oSource.Styles.Count If oSource.Styles(I).Type = wdStyleTypeParagraph Then SType = "Para" oRep.Bookmarks("EndOfDoc").Select Set oPara = oRep.Paragraphs.Add Set oPara = oRep.Paragraphs.Add oPara.Range.Text = SType & ": " & oSource.Styles(I).NameLocal Else SType = "Char" End If Next I For I = 1 To oSource.Styles.Count If oSource.Styles(I).Type = wdStyleTypeParagraph Then SType = "Para" Else SType = "Char" oRep.Bookmarks("EndOfDoc").Select Set oPara = oRep.Paragraphs.A...

Visual Basic Common Dialog
2007-06-19 10:02:00
Opening Files with Common Dialog Common Dialog not only replaces three controls (Drive, Directory and FileList), but also is easier to program. It is supported in Visual Basic and VBA as well. The new VB.NET has the same functionality in the OpenFileDialog classLet us have a small form created for explaining CommonDialog. Let us have a small form with a Text Box and a Command Button. On Clicking the Command Button, the selected file should be displayed in the Text BoxSample Form:To use the CommonDialog you need to include the component to your project. You can do so as follows:Once The component is included, the CommonDialog will be displayed in the ToolBoxDrag the CommonDialog to the form. You will see a small rectangle there. CommonDialog is visible in the Design time only (it is not visible during runtime)Add the following code to show the CommonDialog box and show the selected file in the text boxPrivate Sub Command1_Click()CommonDialog1.DialogTitle = "Select the File..."CommonDi...

Excel VBA - install an Excel Add-in (XLA or XLL)
2007-06-19 03:54:00
Adding Addins Automatically using VBA Most of today's Excel VBA code are as Addins (XLA or XLL). As an organization progresses there comes many revisions for the Addin - hence the need to update the program.Here is a simple way to add a new addin:Sub Add_an_Addin()Dim oAddin As AddInDim oTempBk As WorkbookSet oTempBk = Workbooks.AddSet oAddin = oXL.AddIns.Add("E:CostBenefit1.0.xla", True)oAddin.Installed = TrueoTempBk.CloseEnd SubIf you wonder why a temporary workbooks is added - it is because to avoid the Run-time error '1004': Unable to get the Add property of the AddIns class or Run-time error '1004': Add method of addins class failed exceptions that are raised when there are no workbooks. Just be safe!!

Show All Processes using VBA
2007-06-19 03:49:00
Get All Processes using Win API Functions'DeclarationsConst TH32CS_SNAPHEAPLIST = &H1Const TH32CS_SNAPPROCESS = &H2Const TH32CS_SNAPTHREAD = &H4Const TH32CS_SNAPMODULE = &H8Const TH32CS_SNAPALL = (TH32CS_SNAPHEAPLIST Or TH32CS_SNAPPROCESS Or TH32CS_SNAPTHREAD Or TH32CS_SNAPMODULE)Const TH32CS_INHERIT = &H80000000Const MAX_PATH As Integer = 260Private Type PROCESSENTRY32 dwSize As Long cntUsage As Long th32ProcessID As Long th32DefaultHeapID As Long th32ModuleID As Long cntThreads As Long th32ParentProcessID As Long pcPriClassBase As Long dwFlags As Long szExeFile As String * MAX_PATHEnd TypePrivate Declare Function CreateToolhelp32Snapshot Lib "kernel32" (ByVal lFlags As Long, ByVal lProcessID As Long) As LongPrivate Declare Sub CloseHandle Lib "kernel32" (ByVal hPass As Long)' API Functions to get the processesPrivate Declare Function Process32First Lib "kernel32" (ByVal hSnapShot As Long, uProcess As PROCESSENTRY32) As LongPrivate Declare Function Pr...
More About: Show

Run a VB6.0 Executable from Excel/Word
2007-06-14 04:40:00
Run an Executable from Excel VBA / Word VBAIf you need to use some grid for showing data / use the feautres in Visual Basic 6.0 that arenot available in VBA, you can create the application in VB6.0 or anyother program and show the User Interface in VBA codeSub Run_VB6App_FromWord()--- Some VBA Code heresCmd = "C:Program FilesMyFile.exe"vntResult = OpenProcess(PROCESS_QUERY_INFORMATION, False, Shell(sCmd, 1))GetExitCodeProcess vntResult, lngExitCode' ----------------------------------------- ------------------' Coded by Shasur for http://vbadud.blogspot.com' ----------------------------------------- ------------------Do GetExitCodeProcess vntResult, lngExitCode DoEventsLoop While lngExitCode = STILL_ACTIVE--- some more VBA CodeEnd SubThe above program will show the MyFile executable till the user clicks OK/Cancel. Once the application is closed the control will return to the calling VBA programThis used WinAPI FunctionsPublic Declare Function GetExitCodeProcess Lib "kernel32" ...

VBA Read Text Files (With Leading & Trailing Spaces)
2007-06-14 04:29:00
Read Data frm Text Files (VBA)Reading Text Files using VBA is one of the major development activity of programmers. There are multiple ways to read a file1. Input # Statement2. Input Function3. Get Function4. File System Object FunctionsInput # StatementDim MyString, MyNumberOpen "c: est.txt" For Input As #1 ' Open file for input.Do While Not EOF(1) ' Loop until end of file. Input #1, MyString, MyNumber ' Read data into two variables. Debug.Print MyString, MyNumber ' Print data to the Immediate window.LoopClose #1 ' Close file.However, the bug here is Input # does not take the leading or trailing spaces with it. That is, ' My Name is ' becomes 'My Name is'. This will not be the correct one as we need to get the spaces alsoThen Input function comes handyDim MyCharOpen "c: est.txt" For Input As #1 ' Open file.Do While Not EOF(1) ' Loop until end of file. MyChar = Input(1, #1) ' Get one character. Debug.Print MyChar ' Print to the Immediate w...
More About: Read , Leading , Spaces

Extract Procedure Names from all Modules - VBA
2007-06-14 04:18:00
Count No of Programs in a WorkbookWorkbooks have Macros, If you need to know if the Workbook contains macros and then the following proc will get you the macros in the workbookSub Extract _Program()Dim VBP As VBProjectDim VBModule As CodeModuleDim VBProc As VBComponentDim sLastProcName As StringDim arProcName() As StringDim iProcCount As IntegerSet VBP = ThisWorkbook.VBProjectFor Each VBM In VBP.VBComponents Set VBModule = VBM.CodeModule i = 1 Do Until i >= VBModule.CountOfLines procname = VBModule.ProcOfLine(i, vbext_pk_Proc) i = i + 1 If LenB(procname) 0 Then If procname sLastProcName Then iProcCount = iProcCount + 1 ReDim Preserve arProcName(iProcCount) arProcName(iProcCount) = procname sLastProcName = procname End If End If LoopNext' List all proceduresFor i = 1 To UBound(arProcName) MsgBox arProcName(i)Next iEnd Sub
More About: Names

ColorIndex - Coloring Excel Sheet Cells
2007-06-14 04:08:00
Highlight Color in Excel CellsColorindex is used to color the background of Excel Cells Cells(1, 2).Interior.ColorIndex = 30etcHere is the entire list of colors you can use: RSS Feeds Submission Directory
More About: Sheet , Shee

Excel VBA - Delete Empty Rows
2007-06-14 04:04:00
Delete Rows without ValuesHere is a primitive simple function to delete rows that does not contain any value (I have taken Cols 1 to 10) for consideration.Sub Delete_UnWanted_Rows()For Each SHT In Sheets SHT.Activate iMax = SHT.Cells.SpecialCells(xlCellTypeLastCell ).Row For i2 = 2 To iMax For i1 = 1 To 10 If LenB(SHT.Cells(i2, i1)) 0 Then GoTo TakeNextRow End If Next i1 SHT.Rows(i2).EntireRow.Delete TakeNextRow: Application.StatusBar = SHT.Name & " " & i2 Next i2TakeNextSht: Next SHTApplication.StatusBar = FalseEnd SubYou can do the same with Special Cells - LastCell also
More About: Excel , Delete , Empty

Setting Default & Cancel Buttons in VBA/Visual Basic
2007-06-09 04:58:00
VB/ VBA Setting Default & Cancel Buttons through codePrivate Sub Form_Load()'Sets cmdOK as the button control that is clicked when the user presses the Enter key.cmdOK.Default = True'Sets cmdCancel as the button control that is clicked when the user presses the ESC key.cmdCancel.Cancel = TrueEnd SubFor doing the same in VB.Net Refer : http://dotnetdud.blogspot.com/2007/06/vbn et-setting-default-cancel-buttons.html
More About: Visual Basic , Visual , Basic

Assigning Shortcut Keys - Excel Macros
2007-06-03 05:03:00
Shortcut Key Assignment for SubroutinesIt is always nice to have keyboard shortcuts for executing functions/subroutines rather than having to click the menu and its commandOne way will to link the macro to a command button and assign the shortcut for the button. The other one is to assign the shortcut to the function using Application.OnKeyOnKey method executes a specified procedure when a particular key or key combination is pressedApplication.OnKey "%b", "ToWord"is used to trigger the "ToWord" subroutine whenever Alt+b is pressed . Percentage symbol is used to substitute Alt keyUse the Caret (^) to symbol for Ctrl key and plus (+) for ShiftkeyApplication.OnKey "^b", "ToWord"Is for Ctrl + bOther keys are : Key Code BACKSPACE {BACKSPACE} or {BS} BREAK {BREAK} CAPS LOCK {CAPSLOCK} CLEAR {CLEAR} DELETE or DEL {DELETE} or {DEL} DOWN ARROW {DOWN} END {END} ENTER (numeric keypad) {ENTER} ENTER ~ (tilde) ESC {ESCAPE} or {ESC} HELP {HELP} HOME {HOME} INS {INSERT} LEFT ARROW {...
More About: Excel , Macros , Keys , Acro , Shortcut