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 Arrays, VBA 1 Comment

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 No Comments

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 No Comments

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

Wednesday, December 8th, 2010 VBA No Comments

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 No Comments

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:

VBA Userform

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 UserForm, VBA No Comments