VBA – Loop through arrays
This post shows how to define an array, and to loop through each of the items in it. So many more things can be done with arrays (resizing, adding, deleting items, etc.) but for now I’ll just show how to loop through them.. that’s always useful.
Dim myArray As Variant Dim x As Integer myArray = Array(34610, 92105, 92263, 94121) 'define array For x = LBound(myArray) To UBound(myArray) 'define start and end of array MsgBox (myArray(x)) Next x ' Loop!
Excel formula – Miscellaneous
I recently had to work with Excel formulas, and I encountered a small challenge. The problem was this: I had a table with variating height (the user could add and delete rows), that was likely to sometimes have 0 rows.Also, it wasn’t just the sum of the whole column I needed, as the SUM value should be at the bottom of the table, and a circular loop should be avoided. Therefore, the usual SUM() didn’t work.
I did not want to use VBA for this problem, so I turned to this solution:
Example: Column A: Names – Column B: Salary
1) Find the last cells that isn’t empty (in column A).
2) Make a SUM()-formula in column B that can take the row number from 1) as a parameter.
1) (Danish)
=(SUMPRODUKT(MAKS((A4:A65003<>"")*RÆKKE(A4:A65003))))
(English)
=(SUMPRODUCT(MAX((A4:A65003<>"")*ROW(A4:A65003))))
To make the solution understandable, I will enter the above formula in cell G1. Now, I have defined the last row(G1), and I just need a fixed first row, which I define as 2: I need to use the SUM() and the INDEKS() to sum the variable amount of rows:
(Danish)
=SUM(INDEKS(B:B;2):INDEKS(B:B;G1))
(English)
=SUM(INDEX(B:B;2):INDEX(B:B;G1))
Of course, put togther, it looks like this:
=SUM(INDEKS(B:B;2):INDEKS(B:B;(=(SUMPRODUKT(MAKS((A4:A65003<>"")*RÆKKE(A4:A65003)))) )))
VBA – Delete all files in a folder
This code snippet shows how you can delete all files in a given folder in a VBA application.
Sub deleteFiles() dim myPath myFolder = "C:\MyFolder1\Myfolder2" Set Fso = CreateObject("Scripting.FileSystemObject") ' Get a File object to query. Set Fldr = Fso.GetFolder(myFolder) For Each Filename In Fldr.Files Filename.Delete True ' delete all files Next End Sub
That’s it!
VBA – Loop through sheets
This code snippet can be used if you want to loop thorugh the sheets in your workbook, either because you want to add something to everysheet, or – as in the example – you want to delete sheets with a specific name.
Sub slet_Faner() Application.DisplayAlerts = False ' Makes it unnecessary for the user to approve the deletion Dim ws As Worksheet For Each ws In Worksheets If ws.Name = "Home" Then ws.Delete 'Delete if name of sheet is "Home" Next Application.DisplayAlerts = True End Sub
Of course, if you want all sheets BUT the one sheet with a specific name, you just use:
Dim ws As Worksheet For Each ws In Worksheets If ws.Name <> "Home" Then ws.Delete 'Delete if name of sheet ISN'T "Home" Next
VBA – Define PageSetup (PaperSize, Orientation, etc.) and Print Excel Sheet
This posts explains how to print an Excel page using VBA and PrintOut
. Normally, you will want to define the PageSetup
first, in order to make sure that the right printer, the right paper size (fx A4), orientation (landscape or portrait), etc. is selected.
Step 1: Define PageSetup
.
With Worksheets("MyPage").PageSetup .Orientation = xlPortrait .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PaperSize = xlPaperA4 End With
It’s important to add the Zoom
property in adition to the FitTo...
properties, as you can otherwise not be sure that the printing in fact is restricted to 1×1 page.
Step 2: Print the page.
Sheets("MyPage").PrintOut Copies:=1, Collate:=True
And that’s it!
VBA UserForm – How to automatically switch to next Textbox
The code-snippet presented in this very first post on my blog is helpful when the user has to enter data in TextBoxes in a UserForm, using VBA for Excel.
What we want to achieve is that whenever the user has entered 6 characters in TextBox1
, then TextBox2
is selected by default. That way, the user does not need to used either TAB or the mouse to select the next TextBox:
We will need to create a sub that is called whenever a Change
is made to TextBox1
. We need to use the TextLength
and the SetFocus
, like shown below:
Private Sub TextBox1_Change() 'whenever there are 6 characters registered in the TextBox If TextBox1.TextLength = 6 Then 'select (SetFocus) the next TextBox TextBox2.SetFocus End If
Of course, the code-snippet has to be added in the code-behind of the UserForm. Make sure that you have build your Sub correctly by verifying that you have chosen TextBox1
and Change
, and not just General
at the top of the page:
M | T | W | T | F | S | S |
---|---|---|---|---|---|---|
1 | 2 | 3 | ||||
4 | 5 | 6 | 7 | 8 | 9 | 10 |
11 | 12 | 13 | 14 | 15 | 16 | 17 |
18 | 19 | 20 | 21 | 22 | 23 | 24 |
25 | 26 | 27 | 28 | 29 | 30 | 31 |
Recent Posts
- VBA – Import CSV file
- VBA – Get name of file without extension
- UserForm Listbox – Populate list and extract selected items
- VBA – Retrieve Last Row From More Than One Column
- VBA – Check Extension of File
- VBA – Delete PivotTables
- VBA – Add New WorkSheet After The Last Worksheet
- VBA – Toggle Between Open Excel Files
- VBA – Looping through all files in a folder
- VBA – Create and add items to dynamic arrays
- VBA – Loop through arrays
- Excel formula – Miscellaneous
- VBA – Delete all files in a folder
- VBA – Loop through sheets
- VBA – Define PageSetup (PaperSize, Orientation, etc.) and Print Excel Sheet