Wise Owl Training - Writing VBA blogs (page 2 of 4)

Showing blogs 21-40 (out of 71)

Flappy Bird in Excel VBA - Tutorial Index

Posted by Andrew Gould on 11 April 2014

This is the main index page for the Flappy Bird in Excel VBA tutorial. Here you can find links to all of the articles which comprise the tutorial.

Tags:   Writing VBA | Flappy Owl    |    VBA macros | Flappy Owl

How to create autoshapes, lines and connectors in VBA macros

Posted by Andy Brown on 25 January 2014

You can use Visual Basic within Excel, PowerPoint or Word to draw shapes, format them and even assign macros to run - this long blog gives lots of ideas of how to proceed!

Tags:   VBA macros | General    |    Writing VBA | VBA tutorial

Advice on how to scrape tables of data or HTML from webpages using VBA

Posted by Andy Brown on 13 January 2014

You can use VBA to extract data from web pages, either as whole tables or by parsing the underlying HTML elements. This blog shows you how to code both methods (the technique is often called "web-scraping").

Tags:   VBA macros | References    |    Writing VBA | Other

Getting the Calendar control to work in Excel 2010 onwards

Posted by Andy Brown on 13 January 2014

The calendar control for user forms is built into versions of Excel up to 2007, but has to be imported for later versions - this blog shows how to do this!

Tags:   VBA macros | User forms    |    Writing VBA | Other

How to use environment variables like USERNAME in VBA

Posted by Andy Brown on 25 November 2013

You can get at all sorts of system information within Visual Basic for Applications by using environment variables - this blog shows you how to get at your user's name, computer name and much more besides.

Tags:   VBA macros | Variables, arrays    |    Writing VBA | Other

How to loop over enumerations in VBA

Posted by Andy Brown on 07 November 2013

A short blog explaining how to use Visual Basic for Applications to loop over enumerations.

Tags:   Writing VBA | Other    |    VBA macros | Enumerations

Want to wind up your neighbour? Try this annoying Excel VBA code!

Posted by Andy Brown on 14 May 2013

If you have a colleague who trusts you, you could always betray this trust by sending them a workbook which misbehaves: it won't close and you can't leave it!

Tags:   Writing VBA | Other    |    VBA macros | Events

Problem with using VBA to create chart labels

Posted by Andy Brown on 04 April 2013

If your chart has more than a thousand data labels, this blog explains why you may experience problems.

Tags:   Excel | Charts    |    Writing VBA | Other    |    VBA macros | Working with charts

Write a macro in VBA to fill in an IE form (Internet Explorer)

Posted by Andy Brown on 02 November 2012

You can use VBA to talk to Internet Explorer, providing that you understand the structure of the web page you're talking to. This blog shows how to do this!

Tags:   VBA macros | References    |    Writing VBA | VBA tutorial

Using Excel VBA to Label Multiple Series in a Chart

Posted by Andrew Gould on 30 May 2012

This follow-up article to a previous blog describes how to label the data points in multiple series in Excel using VBA.

Tags:   Writing VBA | Other    |    VBA macros | Working with charts

Capture Excel application events - event sinks

Posted by Andy Brown on 06 March 2012

It's not for the faint-hearted, but there is a way to capture application events (such as someone trying to create a new workbook) in Excel, using something called an event sink.  This blog explains how you might go about creating an event sink.

Tags:   VBA macros | Classes    |    Writing VBA | VBA tutorial

How to Delete Digital Certificates from your Computer

Posted by Andrew Gould on 06 March 2012

Creating a digital certificate for your VBA projects is easy, but deleting them is somewhat less obvious. This blog shows you how to use the certificate manager to remove certificates that you no longer need.

Tags:   VBA macros | General    |    Writing VBA | Other

Collections of class object using Excel VBA

Posted by Andy Brown on 05 March 2012

If you've learnt how to create your own objects using classes in Excel VBA, the next step is to learn how to group them together into collections. This on-line tutorial will show you how.

Tags:   VBA macros | Classes    |    Writing VBA | VBA tutorial

Creating classes in VBA - class modules

Posted by Andy Brown on 05 March 2012

Those who want to add spice to their VBA programming can learn how to create classes, or class modules, and become object-orientated programmers.  It's difficult, but fun - and this multi-part blog will guide you along the way.

Tags:   VBA macros | Classes    |    Writing VBA | VBA tutorial

Using Excel Add-Ins to Share VBA Code

Posted by Andy Brown on 05 March 2012

If you've written a killer function, you'll want to be able to share it between workbooks. The best way to do this is using an add-in.

Tags:   VBA macros | References    |    Writing VBA | VBA tutorial

Advanced controls in userforms with VBA macros

Posted by Andy Brown on 28 February 2012

The two previous parts of this mini-blog have shown how to draw forms and how to write code to handle form events. This final part shows how to add some of the more exotic controls to user forms, like combo boxes, list boxes, multipage controls, spinners and option buttons.

Tags:   VBA macros | User forms    |    Writing VBA | VBA tutorial

Writing VBA macros to work with user forms

Posted by Andy Brown on 28 February 2012

The previous part of this three-part series showed how to draw user forms; this part shows how to write macros to get them to work (the final part shows how to use some of the more advanced controls, such as combo boxes and MultiPages).

Tags:   VBA macros | User forms    |    Writing VBA | VBA tutorial

Drawing UserForms in the VBA code editor

Posted by Andy Brown on 28 February 2012

Learn how to create your own custom dialog boxes in VBA, using UserForms. This is the first part of a three-part series (the other parts are on Writing Code for UserForms and Creating Advanced Controls).

Tags:   VBA macros | User forms    |    Writing VBA | VBA tutorial

Writing functions for Excel using VBA

Posted by Andy Brown on 24 February 2012

On-line training in writing functions using VBA, for use either within other VBA programs or within Excel itself.

Arrays in Visual Basic for Applications macros

Posted by Andy Brown on 24 February 2012

You can use arrays (multiple variables) in VBA to speed up code - and they are also invaluable for processing CSV files and the like.

This page has 1 thread Add post
09 Feb 17 at 15:24

Firstly please let me introduce myself, I'm Charlie, I'm new to coding and I have been given a task. The task is to create code to generate discrete part descriptions for every part number our system can generate. I have attached the code that was written to generate the part numbers, I have also attached the code that I am trying to use to generate the descriptions which has compile error type mismatch occurring.

I would very much like to understand the original code and then create new rather than someone writing the code and just accepting that it works, as has happened before, hence the current situation! My first major hurdle is understanding the "i" in for example:

Dim i1 As Long

If Cells(i2, 2) Mod 2 <> 0 And Cells(i4, 4)

and

Next i5

Amongst other areas of the code.

Sub series325()
Dim a As Long
    Dim b As Long
    Dim c As Long
    Dim d As Long
    Dim e As Long
    Dim i1 As Long
    Dim i2 As Long
    Dim i3 As Long
    Dim i4 As Long
    Dim i5 As Long
    Dim counter As Long
     
    counter = 1
     
    a = Range("A1").End(xlDown).Row
    b = Range("B1").End(xlDown).Row
    c = Range("C1").End(xlDown).Row
    d = Range("D1").End(xlDown).Row
    e = Range("E1").End(xlDown).Row
     
    For i1 = 2 To a
        For i2 = 2 To b
            For i3 = 2 To c
                For i4 = 2 To d
                    For i5 = 2 To e
                        If Cells(i2, 2) Mod 2 <> 0 And Cells(i4, 4) Mod 2 = 0 Then GoTo Nexti
                        If Cells(i2, 2) Mod 2 <> 0 And Cells(i4, 4) = "-2" Then GoTo Nexti
                        If Cells(i2, 2) > 61 And Cells(i4, 4) = "-1" Then GoTo Nexti
                        Cells(counter, 10) = Cells(i1, 1) & Cells(i2, 2) & Cells(i3, 3) & Cells(i4, 4) & Cells(i5, 5)
                        counter = counter + 1
Nexti:
                    Next i5
                Next i4
            Next i3
        Next i2
    Next i1
End Sub
 

I would like to apply the same method to the descriptions. I was hoping the code below might work, sadly it didn't.

Sub series325PartDescriptionTest()
    Dim a As String
    Dim b As Long
    Dim c As String
    Dim d As String
    Dim e As String
    Dim f As String
    Dim g As String
    Dim i1 As String
    Dim i2 As Long
    Dim i3 As String
    Dim i4 As String
    Dim i5 As String
    Dim i6 As String
    Dim i7 As String
    Dim counter As String
     
    counter = 1
     
    a = Range("A1").End(xlDown).Row
    b = Range("B1").End(xlDown).Row
    c = Range("C1").End(xlDown).Row
    d = Range("D1").End(xlDown).Row
    e = Range("E1").End(xlDown).Row
    f = Range("F1").End(xlDown).Row
    g = Range("G1").End(xlDown).Row
     
    For i1 = 2 To a
        For i2 = 2 To b
            For i3 = 2 To c
                For i4 = 2 To d
                    For i5 = 2 To e
                        For i6 = 2 To f
                            For i7 = 2 To g
                                If Cells(i2, 2) Mod 2 <> 0 And Cells(i5, 5) Mod 2 = 0 Then GoTo Nexti
                                If Cells(i2, 2) Mod 2 <> 0 And Cells(i5, 5) = "Double Row formation. " Then GoTo Nexti
                                Cells(counter, 10) = Cells(i1, 1) & Cells(i2, 2) & Cells(i3, 3) & Cells(i4, 4) & Cells(i5, 5) & Cells(i6, 6) & Cells(i7, 7)
                                counter = counter + 1
Nexti:
                            Next i7
                        Next i6
                    Next i5
                Next i4
            Next i3
        Next i2
    Next i1
End Sub
 

I am struggling to add the spreadsheets for reference, the formatting falls apart when I use copy & paste.

 

10 Feb 17 at 13:00

Hi Charlie, thanks for your question! It's a tricky one to answer in a simple comment like this one because, as you say, you're new to programming. Fortunately, we've created both written and video tutorials on VBA that will help you to understand all of the elements that go into making up the procedure you're trying to create.

This page lists the written articles that are part of our main Excel VBA tutorial. We've written plenty of other articles on more esoteric aspects of the language but the articles in that tutorial will give the core skills you need.

If you'd prefer to watch rather than read, you can see a list of our Excel VBA videos here. The main ones you'll need in order to understand the code you've posted are the videos on Selecting Cells, Variables in VBA, If Statements in VBA and For Next Loops.

I hope that helps!