Actuarial Outpost

Actuarial Outpost (http://www.actuarialoutpost.com/actuarial_discussion_forum/index.php)
-   Software & Technology (http://www.actuarialoutpost.com/actuarial_discussion_forum/forumdisplay.php?f=17)
-   -   Best way to learn VBA. (http://www.actuarialoutpost.com/actuarial_discussion_forum/showthread.php?t=88423)

actuary21c 11-14-2014 06:16 AM

Some free VBA info I've just put up on the web
 
This page on my company's website has a VBA course which I wrote 11 years ago (but is still valid, because Microsoft have hardly changed VBA since then!), and also has some good principles for separating the key steps (read data, process it, output the results) which can be applied to many (but of course not all) coding situations:

http://www.inqa.com/Home/ThoughtPieces
AOers are welcome to use this on an AS IS basis. If you have any questions, post them here (or contact me directly at my company), and if I have time, I'll try and answer them :) Similarly, comments/feedback are welcome.

I've also posted a free version of something missing within VBA, a Dictionary class, see http://www.actuary21c.com/?p=3425 for why I wrote this, and https://gist.github.com/actuary21c/9529767 for the code, together with (in the comments) an example of a simple routine testing/using the VBADictionary.

Arthur Kade 11-14-2014 07:13 AM

Quote:

Originally Posted by AvidLearner (Post 7769893)
Programming was always quite intuitive to me, and to most actuaries I know.

Most actuaries I work with who claim that coding is intuitive to them write the most God-awful code I've ever seen.

Something about 90% of people rating themselves a 9 or a 10.

Or perhaps it's about somebody who teaches themselves having a fool for a student.

Abelian Grape 11-14-2014 07:23 AM

Quote:

Originally Posted by Arthur Kade (Post 7779211)
Most actuaries I work with who claim that coding is intuitive to them write the most God-awful code I've ever seen.

Something about 90% of people rating themselves a 9 or a 10.

Or perhaps it's about somebody who teaches themselves having a fool for a student.

More actuarial students should be taking computer science. Taking courses in data structures and algorithmic efficiency was invaluable.

actuary21c 11-14-2014 08:43 AM

Quote:

Originally Posted by Abelian Grape (Post 7779214)
More actuarial students should be taking computer science. Taking courses in data structures and algorithmic efficiency was invaluable.

Or at least reading books about it/taking online (or other courses).
I agree that many aspects of good programming are not very intuitive, eg:

-object oriented programming
-(as you say above) data structures: database design is very mixed in these days with the design of the objects, particularly with Code First (where you don't create the backend database tables directly, instead you write classes and the IDE [Interactive Development Environment, eg Microsoft Visual Studio] creates the database tables for you).
-website programming
-progress reporting during long running operations
-design patterns

dumples 11-14-2014 09:31 AM

Quote:

Originally Posted by actuary21c (Post 7779198)
This page on my company's website has a VBA course which I wrote 11 years ago (but is still valid, because Microsoft have hardly changed VBA since then!), and also has some good principles for separating the key steps (read data, process it, output the results) which can be applied to many (but of course not all) coding situations:

http://www.inqa.com/Home/ThoughtPieces
AOers are welcome to use this on an AS IS basis. If you have any questions, post them here (or contacy me directly at my company), and if I have time, I'll try and answer them :)

I've also posted a free version of something missing within VBA, a Dictionary class, see http://www.actuary21c.com/?p=3425 for why I wrote this, and https://gist.github.com/actuary21c/9529767 for the code, together with (in the comments) an example of a simple routing testing/using the VBADictionary.

How is your dictionary different from the dictionary in the scripting reference library?

Oh, and thanks for sharing.

campbell 11-14-2014 09:38 AM

Quote:

Originally Posted by actuary21c (Post 7779198)
This page on my company's website has a VBA course which I wrote 11 years ago (but is still valid, because Microsoft have hardly changed VBA since then!), and also has some good principles for separating the key steps (read data, process it, output the results) which can be applied to many (but of course not all) coding situations:

http://www.inqa.com/Home/ThoughtPieces
AOers are welcome to use this on an AS IS basis. If you have any questions, post them here (or contacy me directly at my company), and if I have time, I'll try and answer them :)

I've also posted a free version of something missing within VBA, a Dictionary class, see http://www.actuary21c.com/?p=3425 for why I wrote this, and https://gist.github.com/actuary21c/9529767 for the code, together with (in the comments) an example of a simple routing testing/using the VBADictionary.

Oooh, thanks for that code.

actuary21c 11-14-2014 09:58 AM

Quote:

Originally Posted by dumples (Post 7779451)
How is your dictionary different from the dictionary in the scripting reference library?

Oh, and thanks for sharing.

You're welcome.
It probably isn't very different from the scripting reference library dictionary (which I used to use, until I wrote a .net c# version which I preferred - but which required messy COM Interop registry settings to call from VBA).
The interfaces are pretty similar, ie you can probably do whatever you want with either, but I thought I'd write my own earlier this year for 2 reasons:

1. I was fed up of having to set a reference (and tell others to do the same) to the Microsoft Scripting Runtime dll, and wanted people to have a self contained project.

2. I thought it would be a useful, relatively small, example of an object oriented class that performed useful work, that people might like to look at :)

actuary21c 11-14-2014 10:43 AM

There are subtleties to be aware of when retrieving values or keys which are Objects
 
I've added a few comments on the above to my blog post that people may not be aware of, but which are standard issues that arise fairly frequently when manipulating collections of custom objects (classes, as opposed to simpler items called value types [in .net terminology, whereas objects are reference types]), such as integers, doubles, dates).
(Some of these issues arise just as much in VBA when the keys or values for a dictionary or collections are objects).

Re:
Quote:

Originally Posted by campbell (Post 7779475)
Oooh, thanks for that code.

You're v welcome :) but of course it's a relatively simple example of creating a useful class in VBA with some encapsulation (the two underlying collections are private, and kept in sync), abstraction (it hides the details of exactly how it is implemented from the user, who only needs to interact with the interface, which are the class's public methods) etc.

dumples 11-14-2014 01:12 PM

So I'm considering using your code, but I've made some revisions.

Spoiler:
Code:

Option Explicit

' License: distributed under Creative Commons license
' (Basically: use as you wish providing you attribute/give appropriate credit, see https://creativecommons.org/licenses/by/4.0/)
' Use at your own risk!
' Author: Patrick Lee (PJL) of InQA Limited, patrick . lee at inqa dot com
' File name: VBADictionary.
' Purpose: implements a basic Dictionary (also sometimes called a hash array) for use within VBA only
' Date        Who  Comments
' 13 Mar 2014 PJL  Creation
' Revised by Rob Baron 11 Nov 2014

Private m_colKeys As Collection
Private m_colValues As Collection
Private m_StringCompare As Long

Private Sub Class_Initialize()
    Call Me.Clear
    m_StringCompare = vbBinaryCompare 'This is the default value that I've chosen. Rob
End Sub
Public Property Get StringComparisonMethod() As Long
    StringComparisonMethod = m_StringCompare
End Property
Public Property Let StringComparisonMethod(vLong As Long)
Select Case vLong
Case vbBinaryCompare, vbTextCompare, vbDatabaseCompare
    m_StringCompare = vLong
Case Else
    Err.Raise vbObjectError + 1000, "Invalid comparison type " & vLong
End Select
End Property

Public Sub Clear()
    Set m_colKeys = New Collection
    Set m_colValues = New Collection
End Sub
Public Sub Add(ByVal vKey As String, vValue As Variant)
    If ContainsKey(vKey) Then
        Err.Raise vbObjectError + 1000, "Can't add item, the dictionary already contains a key with value " & vKey
    End If
    Call m_colKeys.Add(vKey)
    Call m_colValues.Add(vValue)
End Sub
Public Sub AddFromRanges(vKeys As Range, vValues As Range)
    Dim i As Long
    Dim lKeys As Variant
    Dim lValues As Variant
    If Not (vKeys Is Nothing Or vValues Is Nothing) Then
        If vKeys.Columns.Count = 1 And vValues.Columns.Count = 1 And vKeys.Rows.Count = vValues.Rows.Count Then
            lKeys = vKeys.value
            lValues = vValues.value  'Reading these all at once is faster than referring to them individually.
            For i = 1 To vKeys.Rows.Count
                Call Me.Add(CStr(lKeys(i, 1)), lValues(i, 1))
            Next i
        Else
            Call Err.Raise(vbObjectError + 1000, , "The sizes are not as expected." & vbCrLf & vbCrLf & "Keys :[" & vKeys.Rows.Count & "x" & vKeys.Columns.Count & "]" & vbCrLf & "Values :[" & vValues.Rows.Count & "x" & vValues.Columns.Count & "]")
        End If
    End If
End Sub
Public Function GetKeys() As Collection
    ' we must return a copy of the collection, to avoid the user potentially changing it!
    ' NB the user should NOT normally change individual key items unless they are not objects
    Dim newcol As Collection
    Set newcol = New Collection
    Dim llIndex As Long
    For llIndex = 1 To m_colKeys.Count
        Call newcol.Add(m_colKeys(llIndex))
    Next llIndex
    Set GetKeys = newcol
End Function
Public Function GetValues() As Collection
    ' we must return a copy of the collection, to avoid the user potentially changing it!
    ' NB the user is still free to modify individual values, but that facility is probably intended/needed
    ' e.g. the value may itself be a dictionary, which the user wants to add items to
    Dim lNewCol As Collection
    Set lNewCol = New Collection
    Dim llIndex As Long
    For llIndex = 1 To m_colValues.Count
        Call lNewCol.Add(m_colValues(llIndex))
    Next llIndex
    Set GetValues = lNewCol
End Function
Public Function GetValueFromKey(ByVal vKey As String) As Variant
    Dim llIndex As Long
    If Not ContainsKey(vKey, llIndex) Then
        Err.Raise vbObjectError + 1000, "Can't retrieve item, the dictionary does not contain a key with value " & vKey
    End If
    If IsObject(m_colValues(llIndex)) Then
        Set GetValueFromKey = m_colValues(llIndex)
    Else
        GetValueFromKey = m_colValues(llIndex)
    End If
End Function
Public Sub RemoveAnyValueForGivenKey(ByVal vKey As String)
    Dim llIndex As Long
    If ContainsKey(vKey, llIndex) Then '(we don't need to do anything if the dictionary doesn't contain the key)
        Call m_colKeys.Remove(llIndex)
        Call m_colValues.Remove(llIndex)
    End If
End Sub
Public Sub SetValueForGivenKey(ByVal vKey As String, vValue As Variant)
    Call Me.RemoveAnyValueForGivenKey(vKey)
    Call Me.Add(vKey, vValue)
End Sub
Public Function ContainsItemForKey(vKey As String) As Boolean
    Dim llIndex As Long
    ContainsItemForKey = ContainsKey(vKey, llIndex)
End Function
Public Function Count() As Long
    Count = m_colKeys.Count
End Function
' private routines
Private Function ContainsKey(vKey As String, Optional ByRef vIndexWhereFound As Long = 0) As Boolean
    Dim lsThisKey As String
    Dim llIndex As Long
    For llIndex = 1 To m_colKeys.Count
        lsThisKey = m_colKeys(llIndex)
        If StrComp(lsThisKey, vKey, m_StringCompare) = 0 Then
            vIndexWhereFound = llIndex
            ContainsKey = True
            Exit Function
        End If
    Next llIndex
    ContainsKey = False
End Function
'''These should be obsolete now that GetValueFromKey exists.  Left for backwards compatability.
Public Function GetNonObjectValueFromKey(ByVal vKey As String) As Variant
    Dim llIndex As Long
    If Not ContainsKey(vKey, llIndex) Then
        Err.Raise vbObjectError + 1000, "Can't retrieve item, the dictionary does not contain a key with value " & vKey
    End If
    GetNonObjectValueFromKey = m_colValues(llIndex)
End Function
 Public Function GetObjectValueFromKey(ByVal vKey As String) As Variant
    Dim llIndex As Long
    If Not ContainsKey(vKey, llIndex) Then
        Err.Raise vbObjectError + 1000, "Can't retrieve item, the dictionary does not contain a key with value " & vKey
    End If
    Set GetObjectValueFromKey = m_colValues(llIndex)
End Function
'''



Summary of changes
Spoiler:
Some of it is just naming conventions that I use, I don't mean to be heavy handed, but if I'm going to use it that's how I'm going to do it.

The big change is that I've made use of the Class_Initialize sub routine, so now when you create a vbaDictionary it will automatically initialize. That means that you don't need to say

dim dict as vbaDictionary
set dict = new vbaDictionary
dict.initialize

it also means that you don't need to check to see if the dictionary is initialized and so you can get rid of your boolean that tracks whether things have been initialized.

On top of that, now you can say GetValueFromKey and you don't need to specify whether you expect an object back or not.

You can also load in a table of values from excel ranges.

And you have the option of changing the comparison type so that "ABC" = "abc" if you want it to be that way. By default it does a binary comparison, like you were doing.

I also made the variable that you were using to return the index optional. Now if you don't want to actually know the index, you don't need a dummy variable.

Feedback is welcome.

actuary21c 11-14-2014 01:38 PM

Quote:

Originally Posted by dumples (Post 7780201)
So I'm considering using your code, but I've made some revisions.

Spoiler:
Code:

Option Explicit

' License: distributed under Creative Commons license
' (Basically: use as you wish providing you attribute/give appropriate credit, see https://creativecommons.org/licenses/by/4.0/)
' Use at your own risk!
' Author: Patrick Lee (PJL) of InQA Limited, patrick . lee at inqa dot com
' File name: VBADictionary.
' Purpose: implements a basic Dictionary (also sometimes called a hash array) for use within VBA only
' Date        Who  Comments
' 13 Mar 2014 PJL  Creation
' Revised by Rob Baron 11 Nov 2014

Private m_colKeys As Collection
Private m_colValues As Collection
Private m_StringCompare As Long

Private Sub Class_Initialize()
    Call Me.Clear
    m_StringCompare = vbBinaryCompare 'This is the default value that I've chosen. Rob
End Sub
Public Property Get StringComparisonMethod() As Long
    StringComparisonMethod = m_StringCompare
End Property
Public Property Let StringComparisonMethod(vLong As Long)
Select Case vLong
Case vbBinaryCompare, vbTextCompare, vbDatabaseCompare
    m_StringCompare = vLong
Case Else
    Err.Raise vbObjectError + 1000, "Invalid comparison type " & vLong
End Select
End Property

Public Sub Clear()
    Set m_colKeys = New Collection
    Set m_colValues = New Collection
End Sub
Public Sub Add(ByVal vKey As String, vValue As Variant)
    If ContainsKey(vKey) Then
        Err.Raise vbObjectError + 1000, "Can't add item, the dictionary already contains a key with value " & vKey
    End If
    Call m_colKeys.Add(vKey)
    Call m_colValues.Add(vValue)
End Sub
Public Sub AddFromRanges(vKeys As Range, vValues As Range)
    Dim i As Long
    Dim lKeys As Variant
    Dim lValues As Variant
    If Not (vKeys Is Nothing Or vValues Is Nothing) Then
        If vKeys.Columns.Count = 1 And vValues.Columns.Count = 1 And vKeys.Rows.Count = vValues.Rows.Count Then
            lKeys = vKeys.value
            lValues = vValues.value  'Reading these all at once is faster than referring to them individually.
            For i = 1 To vKeys.Rows.Count
                Call Me.Add(CStr(lKeys(i, 1)), lValues(i, 1))
            Next i
        Else
            Call Err.Raise(vbObjectError + 1000, , "The sizes are not as expected." & vbCrLf & vbCrLf & "Keys :[" & vKeys.Rows.Count & "x" & vKeys.Columns.Count & "]" & vbCrLf & "Values :[" & vValues.Rows.Count & "x" & vValues.Columns.Count & "]")
        End If
    End If
End Sub
Public Function GetKeys() As Collection
    ' we must return a copy of the collection, to avoid the user potentially changing it!
    ' NB the user should NOT normally change individual key items unless they are not objects
    Dim newcol As Collection
    Set newcol = New Collection
    Dim llIndex As Long
    For llIndex = 1 To m_colKeys.Count
        Call newcol.Add(m_colKeys(llIndex))
    Next llIndex
    Set GetKeys = newcol
End Function
Public Function GetValues() As Collection
    ' we must return a copy of the collection, to avoid the user potentially changing it!
    ' NB the user is still free to modify individual values, but that facility is probably intended/needed
    ' e.g. the value may itself be a dictionary, which the user wants to add items to
    Dim lNewCol As Collection
    Set lNewCol = New Collection
    Dim llIndex As Long
    For llIndex = 1 To m_colValues.Count
        Call lNewCol.Add(m_colValues(llIndex))
    Next llIndex
    Set GetValues = lNewCol
End Function
Public Function GetValueFromKey(ByVal vKey As String) As Variant
    Dim llIndex As Long
    If Not ContainsKey(vKey, llIndex) Then
        Err.Raise vbObjectError + 1000, "Can't retrieve item, the dictionary does not contain a key with value " & vKey
    End If
    If IsObject(m_colValues(llIndex)) Then
        Set GetValueFromKey = m_colValues(llIndex)
    Else
        GetValueFromKey = m_colValues(llIndex)
    End If
End Function
Public Sub RemoveAnyValueForGivenKey(ByVal vKey As String)
    Dim llIndex As Long
    If ContainsKey(vKey, llIndex) Then '(we don't need to do anything if the dictionary doesn't contain the key)
        Call m_colKeys.Remove(llIndex)
        Call m_colValues.Remove(llIndex)
    End If
End Sub
Public Sub SetValueForGivenKey(ByVal vKey As String, vValue As Variant)
    Call Me.RemoveAnyValueForGivenKey(vKey)
    Call Me.Add(vKey, vValue)
End Sub
Public Function ContainsItemForKey(vKey As String) As Boolean
    Dim llIndex As Long
    ContainsItemForKey = ContainsKey(vKey, llIndex)
End Function
Public Function Count() As Long
    Count = m_colKeys.Count
End Function
' private routines
Private Function ContainsKey(vKey As String, Optional ByRef vIndexWhereFound As Long = 0) As Boolean
    Dim lsThisKey As String
    Dim llIndex As Long
    For llIndex = 1 To m_colKeys.Count
        lsThisKey = m_colKeys(llIndex)
        If StrComp(lsThisKey, vKey, m_StringCompare) = 0 Then
            vIndexWhereFound = llIndex
            ContainsKey = True
            Exit Function
        End If
    Next llIndex
    ContainsKey = False
End Function
'''These should be obsolete now that GetValueFromKey exists.  Left for backwards compatability.
Public Function GetNonObjectValueFromKey(ByVal vKey As String) As Variant
    Dim llIndex As Long
    If Not ContainsKey(vKey, llIndex) Then
        Err.Raise vbObjectError + 1000, "Can't retrieve item, the dictionary does not contain a key with value " & vKey
    End If
    GetNonObjectValueFromKey = m_colValues(llIndex)
End Function
 Public Function GetObjectValueFromKey(ByVal vKey As String) As Variant
    Dim llIndex As Long
    If Not ContainsKey(vKey, llIndex) Then
        Err.Raise vbObjectError + 1000, "Can't retrieve item, the dictionary does not contain a key with value " & vKey
    End If
    Set GetObjectValueFromKey = m_colValues(llIndex)
End Function
'''



Summary of changes
Spoiler:
Some of it is just naming conventions that I use, I don't mean to be heavy handed, but if I'm going to use it that's how I'm going to do it.

The big change is that I've made use of the Class_Initialize sub routine, so now when you create a vbaDictionary it will automatically initialize. That means that you don't need to say

dim dict as vbaDictionary
set dict = new vbaDictionary
dict.initialize

it also means that you don't need to check to see if the dictionary is initialized and so you can get rid of your boolean that tracks whether things have been initialized.

On top of that, now you can say GetValueFromKey and you don't need to specify whether you expect an object back or not.

You can also load in a table of values from excel ranges.

And you have the option of changing the comparison type so that "ABC" = "abc" if you want it to be that way. By default it does a binary comparison, like you were doing.

I also made the variable that you were using to return the index optional. Now if you don't want to actually know the index, you don't need a dummy variable.

Feedback is welcome.

Great - I like how putting it out there is leading to changes/improvements. (I haven't had time to do other than a quick glance at your code, but I like the ideas. I haven't needed anything more than the basic dictionary, and so that's all I wrote. NB I have used Class_Initialize for simple classes like this which don't need parameters to be validly constructed, but I left the bInitialisedOK flag in as an example of a technique to use when a class really does need parameters to be passed as part of an Initialise method. For private members of a class, I also use convenstions like use m_ or _ as a prefix to the name so that they can be clearly distinguished from local variables.)


All times are GMT -4. The time now is 11:24 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.

Page generated in 0.29891 seconds with 9 queries