### 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!
```
Thursday, December 9th, 2010 Comments Off on VBA – Loop through arrays

### 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)))) )))
```
Wednesday, December 8th, 2010 Excel Comments Off on Excel formula – Miscellaneous

### 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!

Wednesday, December 8th, 2010 VBA Comments Off on VBA – Delete all files in a folder

### 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

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

```
Wednesday, December 8th, 2010 VBA Comments Off on VBA – Loop through sheets

### 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!

Friday, November 26th, 2010 UserForm Comments Off on VBA – Define PageSetup (PaperSize, Orientation, etc.) and Print Excel Sheet

### 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: Friday, November 26th, 2010 Comments Off on VBA UserForm – How to automatically switch to next Textbox

October 2020
M T W T F S S
« Feb
1234
567891011
12131415161718
19202122232425
262728293031