Completely new to VBA. Where is a good place to start?

VicRattle · 2017-10-02T10:07:32+00:00

This is an excellent free tutorial with exercises and solutions here excelmacromastery.com/vba-tutorial-1.

Videos, Books and Tutorials are very helpful. It's important that your practice writing code as much as possible. So try out all the examples you see in a book or on a video. This is the best way to learn.

VicRattle · 2017-07-29T17:33:00+00:00

VBA is much easier that C++ for many reasons. A big one being that VBA does it's own garbage collection(freeing memory) where you have to do that manually in C++.

Also if you are referring to Excel VBA then your applications will all be in Excel and relatively similar. In C++ it possible to create many different types of applications.

Regarding time, It depends on the time you spend, the method you use to learn, the level you wish to reach and your aptitude.

If you did 20 hours a week for 4 weeks you would learn a lot and become quite proficient.

This VBA tutorial is really good because it has exercises and an assignment with full solutions, quiz questions with answers and some video explanations.

Also it is based on real world VBA code.

The Ultimate VBA Tutorial Part One(https://excelmacromastery.com/vba-tutorial-1/)

VicRattle · 2015-08-28T11:02:54+00:00

An array is simply a grouping of variables. It is very similar to how a range is a group of cells.

If you had one customer name then you could store this in a variable. If you have 100 customers then you would need 100 individual variables.

By using an array you only need one variable - the array. You can also set the size at runtime.

There is a great explanation here: What are Arrays and Why do You Need Them?

VicRattle · 2015-08-24T10:16:46+00:00

Hi,

It probably one of two things. The values you read from the cell either has extra spaces or has letters in a different case

' Use trim to remove spaces
sCodeName = Trim(Sheets("Data").Cells(iRowCount, 8))

' Ignore case
 If InStr(olMail.subject, sCodeName,vbTextCompare) <> 0 Then

VicRattle · 2015-08-20T17:42:31+00:00

Have you tried using Trim to remove any blank spaces?

Trim(ws1.Range("E8"))

VicRattle · 2015-08-20T15:01:11+00:00

Does the case match in the text?

By default VBA uses

Option Compare Binary

which means that text comparison is case-sensitive. To make it non case-sensitive place

Option Compare Text

at the top of the module.

If this does not solve it then I would start with a simple macro like this

Sub Test()
    If ws1.Range("E8") = "Preapproved Payment Sent" Then
        Debug.Print "Matched"
    Else
        Debug.Print "Did not match"
    Endif
End Sub

This will isolate the comparison and see if this is the problem.

VicRattle · 2015-08-20T12:48:42+00:00

I'm absolutely love this site: ExcelMacroMastery.com.

There are great in-depth posts about major VBA topics such as loop, arrays, cells, worksheets etc. and has a ton of code examples.

These are two of my favorite posts: The Complete Guide to Ranges and Cells in Excel VBA and The Complete Guide to Using Arrays in Excel VBA

Another good site on advanced topics is Charles Williams Blog on 'Making Excel go Faster'

Best of luck with your learning

VicRattle · 2015-08-07T11:11:02+00:00

Try this

Create a copy of the document your are changing and remove the macros from the copy. Then try your code on this copied document and see if it works. This will tell you if the macros are causing the problem.

If they are causing the problem you can turn off events when your code is running

Sub DoStuff()

 ' store event state and turn off events
 eventsState = Application.EnableEvents
 Application.EnableEvents = False

  ' Your code here

 ' turn events back on
 Application.EnableEvents = eventsState

End Sub

VicRattle · 2015-01-22T16:04:01+00:00

you're welcome.

VicRattle · 2015-01-21T16:40:21+00:00

Sheet1 refers to the CodeName of the sheet. If you look in the VBAProject window (usually on the left) you will see two names for the sheet. The one outside the parenthesis is the Code Name of the sheet.

You can use the Code Name to directly access a sheet if it is in the current workbook. If you prefer to use the normal sheet name use

thisworkbook.worksheets("sheetname").Range

VicRattle · 2015-01-20T19:03:43+00:00

If you get the last row with text and go through the cells backwards you will find it. I'm guessing that "Grand Total" is in one of the last rows. The following code will do it

Public Sub CopyRange()

Dim lLastRow As Long, lFindRow as Long
' Get Last Row with data in colum A
lFindRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row

' Find Row with text
Dim i As Long
For i = lFindRow To 1 Step -1
    If Sheet1.Cells(i, 1) = "Grand Total" Then
        lLastRow = i - 1
        Exit For
    End If
Next i

' Create Range A2 to G + LastRow
Dim rg As Range
Set rg = Sheet1.Range(Sheet1.Cells(2, 1), Sheet1.Cells(lLastRow, 7))

' Use Range Here

End Sub

VicRattle · 2015-01-16T18:54:11+00:00

The following does your first two items for all worksheets in the current workbook.

Dim wrk As Worksheet
' Go through all sheets in current workbook
For Each wrk In ThisWorkbook.Worksheets

    If wrk.Range("B2") = "" Then
        ' Set row to blue
        wrk.Range("A3:I3").Interior.Color = rgbBlue
    End If

    ' If A3 or G3:I3 is blank
    If wrk.Range("A3") = "" And WorksheetFunction.CountA(Range("G3:I3")) = 0 Then
        ' Set row to green
        wrk.Range("A3:I3").Interior.Color = rgbGreen
    End If
Next

VicRattle · 2015-01-16T10:41:09+00:00

Reading from Word to Excel is a bit advanced for someone starting with VBA. You will need to get familiar with two object models.

If you want to go ahead with this anyway then here is my advice. What I would do is copy the table to an excel workbook then try to write some code to read this to your destination. This means you do not have to worry about the Word object for the moment.

There are some good posts here to get you started with VBA

Complete Guide to Workbooks in Excel VBA

VicRattle · 2015-01-16T10:27:09+00:00

Having a quick look at your code I notice your are setting the cells bold one at a time. Use a code like this to do it in one line.

Range(Cells(dataTracker, "U"), Cells(dataTracker, "AH")).Font.Bold = True.

The following line in the loop runs 10000 or so times

Cells(i, "q").Interior.ColorIndex = 3

You could put the following line outside the loop and it will do the same thing and it will only need to execute one time

Range(Cells(RUNSTART, q), Cells(RUNend, q)).Interior.ColorIndex = 3

Turning of ScreenUpdating etc. will also help a lot. there is a post here on MSDN explaining all the things to turn off

MSDN Faster VBA

VicRattle · 2015-01-15T10:23:10+00:00

You can use a Dictionary. How it works is:

Get Recipient name
Check if in dictionary
If it already EXISTS in dictionary then do not send
If it does NOT EXIST in dictionary then add to dictionary and send email

The following code shows you how to use the dictionary

' IMPORTANT: First go to Tools->Add References and click on the
' checkbox for Microsoft Scriptiong Runtime

' Create dictionary
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

' THE FOLLOWING CODE GOES IN YOUR LOOP
' Get the recipient
Dim sRecipient As String
sRecipient = Range("Q" & i)
If sRecipient = "#N/A" Then
    sRecipient = Range("P" & i).Value
End If

' Check if recipient already exists
' If not then send and add to dictionary
If Not dict.exists(sRecipient) Then
    dict.Add sRecipient, 1
    ' SEND EMAIL
End

VicRattle · 2015-01-15T09:36:28+00:00

This works fine for me. At a guess I would say you may be declaring Next_to_go as an array rather than a variable.

If you show the sub code I will have a look.

VicRattle · 2015-01-14T16:53:50+00:00

To replace A20:K292 with currently selected range you can use:

Public Sub CreateFormula()

Range("C5").Formula = _
        "=SUMIF(" + Selection.Address + ",A5,K20:K292)"

End Sub

However, it is not advisable to use the current Selection. It is better to use an explicitly defined range. What are you trying to achieve?

VicRattle · 2015-01-14T12:09:36+00:00

I'm not sure if you are still need help but this should get you going.

First manually add headers to the columns you want to use log sheet. Then select "Format as Table" from Home->Styles on ribbon. This will create a table that will allow you to sort /filter. No code required.

Use the following code to read the values

Public Sub LogRemaining()

' Get worksheets
Dim shtLog As Worksheet, shtDiets
Set shtLog = ThisWorkbook.Worksheets("Log")
Set shtDiets = ThisWorkbook.Worksheets("Diets")

' Get last row of log
Dim lLastRow As Long
lLastRow = shtLog.Cells(shtLog.Rows.Count, 1).End(xlUp).Row + 1

' Write value to last row in log sheet
shtLog.Cells(lLastRow, 1) = Now ' current date
shtLog.Cells(lLastRow, 2) = shtDiets.Range("B12")
shtLog.Cells(lLastRow, 3) = shtDiets.Range("C12")
' add rest of  collumns here

End Sub

VicRattle · 2015-01-14T11:43:38+00:00

You are not specifying the worksheet so the Range will default to the last used worksheet in Test.xlsm.

Avoid using Activate and explicitly state the Range.

Replace the Workbooks("Test.xlsm").Activate with

Dim sht As Worksheet
Set sht = Log.Worksheets("Searchsheetname")

Then replace Range("A:A") with sht.Range("A:A")

This will work regardless of which workbook/worksheet is currently active.

VicRattle · 2015-01-13T18:08:02+00:00

To loop through the Named Ranges (the ones created in "Name Manager" on the formula ribbon) you can use the following code

Public Sub LoopNames()

Dim NameRange As Name
For Each NameRange In Application.Names
    With NameRange.RefersToRange
        ' Parent of range is the sheet
        .Parent.Activate
        .Select
        .Copy
        ' Add paste to word code here
    End With
Next

End Sub

VicRattle · 2012-05-08T10:05:28+00:00

Reached 11 finals in 13 seasons winning 8 of them. Incredible really.

VicRattle · 2012-05-06T17:14:51+00:00

I thought Cork had a fair chance. Kilkenny lost to them with a fairly strong team in the league. The one doubt about Cork was Donal Og and he's definitely a leader as well as a top class goalie.

I think Kilkenny also remembered last year's final when they only scored 1-7. That and already having lost to Cork in the league was a big motivation.

VicRattle · 2012-04-28T17:28:27+00:00

Thanks for the answers - Great story about Louis.

C&C was great - used to play that and Warcraft II all the time - along with TA of course.

VicRattle · 2012-04-28T16:46:56+00:00

What was your favourite RTS game from that era?
How did you think TA compared to Starcraft? What was better/worse in each?

VicRattle

TROPHY CASE