General question about VBA in Excel

[VBA2010]

Hi,

I've recently started programming in VBA and I find it very rewarding actually. I feel that i've come a long way in a short amount of time and thats great! But...
I've come to the conclusion that its very hard to remember all the commands and codes that the VBA language contains by heart. So my question is:
What do you guys recommend that I should do when I want to program something and there is some command or code that I want to use but cant remember? I mean whats the most efficient way to handle such a situation so that I can find a qualified solution to the problem without necessarily memorizing all commands and codes!

How do/did you guys do it?

Thx,

 

[RoryA]

In order to process every second row in a range, you will need a loop (effectively: process one row, move down two rows, repeat). It sounds to me like you need a book on the fundamantal structures of VBA (conditions, loops etc). The macro recorder won't really help with that. You could also search for 'For statement' in the VBA help files.

 

[Andrew Poulsom]

In VBA Help expand 'Microsoft Visual Basic Documentation' then 'Visual Basic Conceptual Topics'. Look at these topics:

Using Do...Loop Statements
Using For Each...Next Statements
Using For...Next Statements

 

[VBA2010]

In VBA Help expand 'Microsoft Visual Basic Documentation' then 'Visual Basic Conceptual Topics'. Look at these topics:

Using Do...Loop Statements
Using For Each...Next Statements
Using For...Next Statements

Thanks, that helped

 

[VBA2010]

In order to process every second row in a range, you will need a loop (effectively: process one row, move down two rows, repeat). It sounds to me like you need a book on the fundamantal structures of VBA (conditions, loops etc). The macro recorder won't really help with that. You could also search for 'For statement' in the VBA help files.

Cheers

 

[Darren Bartrup]

Where do I find this library? And what are .bas files?

.bas files are Basic Files (that's what the save dialog says anyway).
Basically they're the Modules that you put your code into - if you export a module it's saved as a .bas file.

You can't find the library - you have to build it yourself. When you find a bit of code that you know you'll use multiple times and is self contained then save it in a folder as a .bas file.

e.g.

Attribute VB_Name = "mdl_MondayDate"
Option Explicit

'---------------------------------------------------------------------------------------
' Procedure : MondayDate
' Purpose   : Returns the Monday date of the current week if no parameter passed
'             or the Monday of the week relative to a specific date if passed.
'---------------------------------------------------------------------------------------
Public Function MondayDate(Optional RelativeTo As Date) As Date

    Dim lDayNumber As Long

    On Error GoTo ERR_HANDLE

    If RelativeTo = #12:00:00 AM# Then RelativeTo = Date
    Select Case Weekday(RelativeTo)
        Case 1
            MondayDate = RelativeTo - 6
        Case 2
            MondayDate = RelativeTo
        Case 3 To 7
            MondayDate = RelativeTo - Weekday(RelativeTo) + 2
    End Select
          
EXIT_PROC:
    On Error GoTo 0
    Exit Function

ERR_HANDLE:
    Select Case Err.Number
        Case Else
            DisplayError Err.Description, "MondayDate()"
            Resume EXIT_PROC
    End Select
End Function

If I need to pull back Mondays date I just drag in the file containing the above code and I know it works. Don't need to remember how it works now.

 

[VBA2010]

.bas files are Basic Files (that's what the save dialog says anyway).
Basically they're the Modules that you put your code into - if you export a module it's saved as a .bas file.

You can't find the library - you have to build it yourself. When you find a bit of code that you know you'll use multiple times and is self contained then save it in a folder as a .bas file.

e.g.

Attribute VB_Name = "mdl_MondayDate"
Option Explicit
 
'---------------------------------------------------------------------------------------
' Procedure : MondayDate
' Purpose   : Returns the Monday date of the current week if no parameter passed
'             or the Monday of the week relative to a specific date if passed.
'---------------------------------------------------------------------------------------
Public Function MondayDate(Optional RelativeTo As Date) As Date
 
    Dim lDayNumber As Long
 
    On Error GoTo ERR_HANDLE
 
    If RelativeTo = #12:00:00 AM# Then RelativeTo = Date
    Select Case Weekday(RelativeTo)
        Case 1
            MondayDate = RelativeTo - 6
        Case 2
            MondayDate = RelativeTo
        Case 3 To 7
            MondayDate = RelativeTo - Weekday(RelativeTo) + 2
    End Select
 
EXIT_PROC:
    On Error GoTo 0
    Exit Function
 
ERR_HANDLE:
    Select Case Err.Number
        Case Else
            DisplayError Err.Description, "MondayDate()"
            Resume EXIT_PROC
    End Select
End Function

If I need to pull back Mondays date I just drag in the file containing the above code and I know it works. Don't need to remember how it works now.

Thanks Very helpful