Details for duggie

This is my (yours actually!) code:

Dim objOutlook As Object

Dim objMail As Object

Call ModPause.Pause(Delay:=wksCustomising.Range("Pause").Value2)

Set objOutlook = CreateObject("Outlook.Application")

Call ModPause.Pause(Delay:=wksCustomising.Range("Pause").Value2)

Set objMail = objOutlook.CreateItem(0)

I have two questions:

1. Sometimes the code crashes on the first Set line, sometimes on the second. The error message is:

   run time error 462 the remote server machine does not exist or is unreachable

   It seems adding a pause helps. Is that normal?

2. Can you tell me should the code work with the latest version of Outlook, ie Outlook 365?

I tried it but it hangs on the first Set line.

A couple of points:

1. The suggestion you provided for setting the source of a map works but only if the map was embedded on a worksheet. If the map was "on its own" ie on a chartsheet, it fails.

   Is there a workaround for this?

2. I use the following code to change the size of the legend and it works for a column / bar, etc chart:

   ActiveSheet.ChartObjects("Chart 1").chart.legend.width=10

   but if the chart is a map, I get an error:

   Object doesn't support this action.

   Is there a fix for maps to alter the size of the legend?

   or have i identifed two further bugs with Excel?

I have two columns of data, country and metric and I created a column chart from it manually.

This code sets the source data of a chart:

ActiveSheet.ChartObjects("Chart 1").Activate

ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$3")

However, when I changed the chart type manually to a map, then ran the code, I got an error:

run-time error 445 Object doesn't support this action

Are maps different to "normal" charts? How can I set its source data?

I tried using your code to copy a row of data from Excel into Powerpoint but I got some expected results.

My row of data in Excel consisted of 26 columns (A to Z).

When I ran the code, the output in Powerpoint "was wrapped" a bit like the Wrapped Text function in Excel.

Is this because my row was too wide to fit on a single row in Powerpoint?

The chess program is impressive but unfortunately it's buggy. It doesn't observe the 3 fold repetition, perpetual check and it allows castling, even when squares between the King and Rook are attacked.

I am trying to add 3D effect to my pie chart.

I manually created a pie chart, named it Chart 1, then applied the following 3D formatting:

Right-clicked on the chart to bring up the Format Chart Area on the right.

Under Chart Options, I selected the middle icon (Effects) then expanded 3D Format.

Under Top bevel, I chose Round (the first one). Similarly for Bottom bevel.

Recording the above actions genenerated this code:

 With ActiveSheet.Shapes("Chart 1").ThreeD
        .BevelTopType = msoBevelRelaxedInset
        .BevelTopInset = 6
        .BevelTopDepth = 6
    End With
    With ActiveSheet.Shapes("Chart 1").ThreeD
        .BevelBottomType = msoBevelRelaxedInset
        .BevelBottomInset = 6
        .BevelBottomDepth = 6
    End With

If I reversed my formatting actions, then run the code, nothing happens to the pie chart.

Furthermore, if I execute this in the Immediate Window:

ActiveSheet.Shapes("Chart 1").ThreeD.BevelTopInset = 6
 

followed by:

?ActiveSheet.Shapes("Chart 1").ThreeD.BevelTopInset = 6

it returns False!

Two questions spring to mind:

1. Is this yet another VBA quirk?

2. How can I use cod to set the bevel?

Thanks

 

I have File Dialog box, where the user can choose where to save their file.

Option Explicit

Sub ChooseFileLocationToSave()

    Dim FileSelector As FileDialog
           
    Set FileSelector = Application.FileDialog(fileDialogType:=msoFileDialogSaveAs)
   
    With FileSelector
       
        .FilterIndex = 1
       
        .InitialFileName = ThisWorkbook.Path & "\"
        
        .Title = "Please Type A Filename"
       
        Dim FileSelected As Boolean
   
        FileSelected = .Show

    End With
   
    If FileSelected <> False Then
   
        Application.DisplayAlerts = False
       
        ThisWorkbook.SaveAs Filename:=FileSelector.SelectedItems(1), _
                            FileFormat:=51

        Application.DisplayAlerts = True
       
    End If
   
    Set FileSelector = Nothing
           
End Sub
 

I also have a Workbook_BeforeSave Event:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim MyArray() As Variant
   
    MyArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
End Sub

For some reason, when I try to save the workbook, the contents of the array is not read, which causes the program to crash, with a Run Time error 13. Type Mismatch message.

Is this a bug or have I missed something?

Thanks

 

 

 

 

 

Hi,

This is my first post on Power BI, so please excuse my naivety!

I have a workbook containing many formulae on a worksheet relating to other worksheets within the same workbook, eg SUMIFS, VLOOKUPS, etc.

Updating rows causes the workbook to be slow (Excel shows Calculating 1%, 2%, etc.)

My limited understanding of Power BI is that it's made for creating dashboards by clicking an dragging icons.

Would Power BI be able to solve this updating of fomulae problem?

Thanks

In this video, you stated it's better to write:

Title = "Not Set"

rather than:

pTitle = "Not Set"

because the former will access the Let Property.

 

Can you tell me if the following will give the same result (actually I've checked it and it does):

Me.Title = "Not Set"

 

But I'm not entirely clear on the keyword "Me" in the contect of a Class Module.

Thanks

 

 

I have reached this page of the code and started the game.

What I can't understand is how does the blue pixel drop down, then rises and repeats itself? I can't see any loop in the code that makes it do that.

I added a breakpoint on this line:

Public Sub InitialiseGame()

    'Called once when game first starts

    'Used to set starting parameters
    
    'Begins the game timer
    
    ShTest.Select
    
    Range("A1").Select
    
    Cells.Clear
    
    InitialiseBird
    
    InitialiseTimer 'ADDED A BREAK POINT

End Sub

Then when I ran that line of code, the blue pixel started dropping down, then rising and dropping. It only stopped when I clicked on the Stop Game button.

Can you please explain which line of code is causing this?

 

 

 

I came across this code from another website:

These lines are in the class collectionExt:

 

Public Function Clone() As collectionExt

    Dim newColl As New collectionExt

    ' some more code

End Function

 

It seems inside the function, it is decalring an instance of itself. How come it's not recursive?

 

I noticed a slight problem.

If the folder "My Files" contains a file with the same name as the workbook running the code, it fails on this line:

For Each ws In MovieFile.Worksheets

How can this be amended (other than NOT renaming a file with the same name)?

 

Many thanks (sorry only about 2 years late)!

 

 

Instead of using the Initialise event, ie

Option Explicit

Private HangmanBook As Workbook

Private Sub Class_Initialize()

'on starting a game, create new workbook
Set HangmanBook = Workbooks.Add

'display welcoming message
MsgBox "Welcome to Hangman!"

End Sub

Private Sub Class_Terminate()

'on ending the game, we'll close down the workbook
HangmanBook.Close savechanges:=False

End Sub

Sub Play()

'something should happen here!
MsgBox "Should be able to play game here"

End Sub

Could you have written this way?

Sub Play()

Private HangmanBook As Workbook

Set HangmanBook = Workbooks.Add

'display welcoming message
MsgBox "Welcome to Hangman!"

something should happen here!
MsgBox "Should be able to play game here"

End Sub

Ie do away with the Initialise event and add whatever was in there into the Sub instead?

If so, under what circumstances would you use the Initialise event?

In the video, around the 9min 48 sec mark, you state you wanted to see everything in the director's table and to do that, you use a LEFT OUTER join.

Imagine if the actor's table didn't exist. If you wanted to see everything in the director's table, would you simply write:

SELECT * FROM tbldirector

If so, even if the actor's table is "brought into the equation", to see everything in the director's table, wouldn't my query work?

On a separate note, if you do have both tables, which part of the venn diagram would be highlighted for this query:

SELECT * FROM tbldirector

Andrew,

Here is part of the code in this video:

 while (!clientsFile.EndOfStream)
                {
                    string eachClient = clientsFile.ReadLine();
                    string[] clientDetails = eachClient.Split('\t');

Am I correct in saying that immediately when the While loop starts, you defined several variables, ie in these two lines:

 while (!clientsFile.EndOfStream)
                {
                    string eachClient = clientsFile.ReadLine();

   you declared a variable called eachClient of type String?

The reason for my confusion is in VBA, I make a point never to declare variables within loops, be it a Do While or For Next loop, such as:

For i = 1 to 10
    Dim s As String
    ' rest of the code
Next i

the reason being for each iteration, the variable s is "reset".

Is there another way to declare the variables in C# or is it perfectly acceptable?

I noticed if the user enters a max age that is smaller than the min age, the message box returns a meaningless answer.

 

A) How can the code be amended to correct this?

B) Probably better if there's some data validation to prevent users entering a max age < min age.

 

 

I have noticed something strange with the Hangman game. Can someone please check if they experience the same problem.

1. Open the Hangman game and enable macros.

2. Go to the VB editor, look for the class module, clsGame and put break point on this line:

StopGameStatus = UserKeptGuessingInvalidLetters

3. Press the Play button.

4. When the Debugging messagebox pops up, click No.

5. When the Guess message box pops up, click Cancel.

6. When the "You must type in one (and only one) letter" message box pops up, click OK

7. Click Cancel

8. Click OK

9. Click Cancel

10. Click OK

Now the program should hit the break point.

Look for this line (found in the class module, clsGame and Sub PlayRound):

If Not Letter.IfTooManyGoes Then

What I found was if I hovered my mouse over the word Not, then the program continues to run and the Guess message box pops up!

Can someone please explain why the program continues to run, despite I have NOT stepped onto the next line of code after the break point.

Thanks.

Can you tell me what is the syntax in a For Each Loop?

My understanding is For Each x In y, x has to be either: 1. Variant, 2. Generic Object or 3. Specific Object

Does x also have to be of the same type as y?

 

Andrew,

I have followed your video and everything works fine.

However, I would like to find out the number of records in the recordset because if there are more than 1m records, they can't be returned to the worksheet and so I would like to do some filtering to the results.

I added these few lines to your existing code:

        Dim j
        
        j = rs.GetRows(rs.RecordCount)
        
        Dim myarray() As Variant
        
        myarray = j

So altogether, the entire code is as follows:

Sub GetDataFromStoredProcedure()

    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset   
    
    Set cn = New ADODB.Connection
    
    cn.ConnectionString = _
    "Provider=SQLNCLI11;Server=MYLAPTOPNAME\SQLEXPRESS;Database=Movies;Trusted_Connection=yes"
    
    cn.Open
    
    Set cmd = New ADODB.Command
    
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "spFilmList"
    
    Set rs = cmd.Execute   
        
        Dim j
        
        j = rs.GetRows(rs.RecordCount)
        
        Dim myarray() As Variant
        
        myarray = j
        
    WriteToSheets rs
    
    rs.Close
    cn.Close
    
End Sub

Private Sub WriteToSheets(ResultSet As ADODB.Recordset)

    Dim ws As Worksheet
    Dim i As Integer
        
    Set ws = ThisWorkbook.Worksheets.Add
    
    ws.Select
    
    For i = 0 To ResultSet.Fields.Count - 1
        ws.Cells(1, i + 1).Value = ResultSet.Fields(i).Name
    Next i     
    
    ws.Range("A2").CopyFromRecordset ResultSet
    
End Sub

Why is it that adding those few lines makes the code return no results onto the worksheet (though the field names are retirned)?

Thanks

 

This video shows how to use commandtext, whereas the previous video concentrates on recordsets.

Can you please explain the merits of using commandtexts over recordsets or is it just an alternative?

 

Hi,

Can you please explain why Method 1 fails but Method 2 works?

Method 1:

'This is Class1

Option Explicit

Public MyArr As Variant

Private Sub Class_Initialize()

    ReDim MyArr(1 To 5) As Variant
    Dim i As Integer

    For i = 1 To 5
        MyArr(i) = 0
    Next i

End Sub

'This is in a standard module

Option Explicit

Sub Start()

    Dim abc As Class1
    Set abc = New Class1

    abc.MyArr(1) = 10
    Debug.Print abc.MyArr(1)

End Sub

After this line has been run:

abc.MyArr(1) = 10

in the Immediate Window, I see:

abc.MyArr(1) = 0

I expect it to be 10.

 

However using tmp variable seems to do the trick.

Method 2:

Sub Start()

   Dim tmp As Variant
   Dim abc As Class1
   Set abc = New Class1
  
   tmp = abc.MyArr
   tmp(1) = 10
   abc.MyArr = tmp
   Debug.Print abc.MyArr(1)

End Sub

Thanks

 

I came across this by accident and wondered if other readers have experienced it as well.  I thought hovering the mouse over a variable only shows the value of the variable but not change it.

This is in a standard module:

Option Explicit

Sub Start()

    Dim abc As Class1

    Set abc = New Class1

    Dim a As Long

    a = 1

    Debug.Print abc.MyProperty(a)

End Sub

This is Class1:

Option Explicit

Public Property Get MyProperty(ByRef b As Long) As Long

    b = b + 1

    MyProperty = b

End Property

If I run the code but put a break point here:

Debug.Print abc.MyProperty(a)

and hover the mouse over abc.MyProperty(a), I see:

abc.MyProperty(a) = 2

If I move my mouse away then back over abc.MyProperty(a), I see the value increment by 1 each time.  Why is this?  II thought hovering doesn't change anything.

Thanks.

Hi

Having watched this video, in particular the part about merging tables, it leads me to think that it is actually doing a vlookup.  Many people have expressed their desire to use Power Query to do vlookups because of its speed.  My question is: how do you incorporate the vlookup performed using Power Query into VBA?

Suppose in Excel on Sheet1 column 1, I have the values 1,2,3,4,5 and in column 2 the values a,b,c,d,e.

Also on Sheet2 I have the value 1 in cell A1. Running the following simple code would return the value of "a" into cell B1 on Sheet2, as expected:

Option Explicit

Dim SourceArray() As Variant

Sub Start()
    SourceArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Call Module1.Lookup
   
    Erase SourceArray()
   
End Sub

Sub Lookup()

    Sheet2.Cells(1, 2).Value = Application.WorksheetFunction.VLookup(Sheet2.Cells(1, 1).Value, SourceArray(), 2, False)
   
End Sub

My question is: if the Lookup subroutine was done manually using Power Query, how can that code be generated? As far as I'm aware, (and do correct me if I'm mistaken), clicking the macro recorder button then performing Power Query steps manually DOES NOT generate any VBA.

Thanks

Thanks for the suggestion.

It's taken me some time but I think I've managed to convince my customer to provide me with the data in a csv format instead!

 

 

 

The video shows how one can extract data from a database, be it Access or SQL Server MS and paste the results back into Excel using ADO.

Can you tell me if there is a similar technique to extracting data that is stored within SS Reporting Services?

Currently my users are essentially doing a "lookup" in SSRS. For example they manually copy the Customer Name field from Excel, then pastes it into a pre-created report in SSRS, clicks the refresh button in the report, which in turn refreshes its page to reveal customer's Nationality. This Nationality info is then copied manually and pasted back into Excel.

Can all the above steps be automated using VBA and if so, how might it be done?

Thanks

 

 

Andrew,

Quick question here:

Assuming I have some data (10 rows) on Sheet1 via a query, if I execute this:

Sheet1.Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False

will it clear the the entire contents of Sheet1 first before populating it?

My concern is if the webpage which the query is fetching from has been updated / changed and now only contains fewer than 10 rows (say only 3 rows), I don't want the query to simply grab those 3 rows and paste onto Range("A1") (and leaving the old rows 4 to 10 data still on the sheet).

Thanks

 

Andrew,

Do you know if it's possible to delete date from a worksheet but still preserving the query behind it?

 

Andrew,

Is something perculiare with the following? I tested it on Excel 2010.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    With Application
   
        .EnableEvents = False
       
        Select Case Target.Column
       
            Case 1
           
                Me.Cells(Target.Row, 2).Select
       
            Case 2
           
                Me.Cells(Target.Row, 3).Select
       
            Case 3
           
                Me.Cells(Target.Row, 4).Select
       
            Case 4
           
                Me.Cells(Target.Row, 1).Select
       
        End Select
       
        .EnableEvents = True
       
    End With
   
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    MsgBox "SelectionChange!"
   
End Sub

The first time you select a cell on the worksheet, it triggers the Worksheet_SelectionChange event and the message box appears. I expect that.

After clicking OK, if I change a value on the worksheet, the Worksheets_Change event kicks off (as expected) and runs to the End Sub.

However, what I did not expect is after the End Sub in Worksheet_Change has been run, it immediately jumps to Sub Worksheet_SelctionChange.

I thought that by adding Applkication.EnableEvents = False, it suppresses the event.

Do you see a similar result (preferably testing in Excel 2010)?

Thanks

 

 

Hi,

If the table on a webpage contains 100 records, yet only the first 50 is shown on the first webpage and the remaining on the second webpage, how can I adapt the code so it brings back the full 100 records onto my worksheet?

Thanks

 

I have followed your code and produced this, which works perfectly:

 

Sub ImportWebPage()

    Dim qt As QueryTable
    Dim URL As String
   
    URL = "SomeWebPage"
   
    Set qt = Sheet1.QueryTables.Add(Connection:="URL;" & URL, Destination:=Sheet1.Range("A1"))
   
    With qt
   
        .RefreshOnFileOpen = True
   
        .Name = "SomeName"
       
        .WebFormatting = xlWebFormattingRTF
       
        .WebSelectionType = xlSpecifiedTables
       
        .WebTables = "1"
       
        .Refresh
   
    End With
   
End Sub

 

The problem is that I have additional code running afterwards but the code needs to run AFTER the code above has refreshed.

 

Adding Wait or DoEvents hasn't helped.

How can I ensure the web page has completely refreshed before I continue with the next part of the code?

Thanks

Hi,

I followed your video but encountered an error on this line:

PPSlide.Shapes.Paste

I think the reason is I am using Excel 2010, whereas you are using Excel 2013.

Can you tell me how can I adapt it to work for Excel 2010?

Thanks

 

Hi,

I am following your videos on using VBA to apply filters to Pivot Tables and would like a copy of the movies database, as an Excel spreadsheet.

Can you please direct me to the appropriate location to download this.

Thanks

 

 

Something rather puzzling has occured to my worksheet and I'm not to sure why that is the case.

If I set a range to a worksheet, I expected that refering to a cell that is NOT in the range would result in an error but it hasn't.

For example, if I had the values 10 through to 80 in the cells A1 through to D2 and I write:

Dim rng As Range

Set rng = Range("A1:D1")

Debug.Print rng(3)

this returns the value 30, as expected.

However, if I typed:

Debug.Print rng (5)

I get the value of 50!

But I though rng(5) was OUTSIDE of my range, so surely it should return an error?

Thanks

Brilliant!

Thanks for your detailed explanation, finally got it.

 

 

Hi, I have a query with my code and hope you can help.  I use this function to find the last column on a particular worksheet that contains data:

Public Function LCol(ByRef wks As Worksheet) As Long

    On Error GoTo Correction

        With wks
       
            LCol = .Cells.Find(What:="*", _
                               After:=.Cells(Rows.Count, Columns.Count), _
                               SearchDirection:=xlPrevious, _
                               SearchOrder:=xlByColumns).Column
           
        End With
   
        GoTo Exitpoint
   
Correction:
        LCol = 1

Exitpoint:

    On Error GoTo 0

End Function

My workbook has two worksheets: Sheet1 and Sheet2.  Sheet1 contains a value in cell J10.

I have a Worksheet_Deactivate() event on Sheet1:

Option Explicit

Private Sub Worksheet_Deactivate()
   
    Dim a As Long
   
    a = FnLastRow.LCol(wks:=Me)
   
End Sub

So when I move off Sheet1 to go to Sheet2, the code above assigns the value of 10 to the variable a because the last column of data on Sheet1 is in column J.

All is good.

Now if my workbook also contains a chart sheet (that contains a chart only) and I move from Sheet1 to the chart sheet, the code goes to the function BUT it seems to assign a value of LCol of 1, ie it errors.

What is wrong?

Thanks

This code is early bound:

    Dim DIC As Scripting.Dictionary
    'Dim DIC As Object
   
    Set DIC = New Scripting.Dictionary
    'Set DIC = CreateObject("Scripting.Dictionary")
   
    Dim MyArray() As Variant
    
    MyArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Dim n As Long
   
    For n = 1 To UBound(MyArray(), 1)
       
        DIC.Item(MyArray(n, 1)) = 0
       
    Next n
   
    Dim KeysArray() As Variant
   
    KeysArray() = DIC.Keys
   
    Dim NumKeys As Long
   
    NumKeys = DIC.Count
   
    Dim ElementsArray() As Variant
    ReDim ElementsArray(1 To DIC.Count, 1 To 1) As Variant
   
    Dim DataRng As Range
    Set DataRng = Sheet1.Range(Sheet1.Cells(2, 1), Sheet1.Cells(Module1.LRow(wks:=Sheet1), 1))
   
    Dim Counter As Long
   
    For Counter = 1 To DIC.Count - 1
   
        ElementsArray(Counter + 1, 1) = Application.WorksheetFunction.CountIf(DataRng, DIC.Keys(Counter))
      
    Next Counter

To convert it to late bound, I have to chnage this line:

ElementsArray(Counter + 1, 1) = Application.WorksheetFunction.CountIf(DataRng, DIC.Keys(Counter))

to this:

ElementsArray(Counter + 1, 1) = Application.WorksheetFunction.CountIf(DataRng, DIC.Keys()(Counter))

What's the significance of the extra ()?

This code removes all rows if column E contains Oranges  

    Dim DataArray() As Variant
   
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Dim DataArrayRows As Long
   
    DataArrayRows = UBound(DataArray(), 1)
   
    Dim DataArrayCols As Long
   
    DataArrayCols = UBound(DataArray(), 2)
   
    Dim SubArray() As Variant
   
    ReDim SubArray(1 To DataArrayRows, 1 To DataArrayCols) As Variant
   
    Dim I As Long, j As Long, k As Long
   
    j = 1
   
    k = 1
   
    For i = 2 To DataArrayRows
   
        If DataArray(i, 5) <> "Oranges" Then
       
            For j = 1 To DataArrayCols
       
                SubArray(k, j) = DataArray(i, j)
           
            Next j
           
            k = k + 1
           
        End If
   
    Next I
   
    Sheet3.Cells(2, 1).Resize(k - 1, DataArrayCols).Value = SubArray()

 

but I've had to declare a large SubArray, as big as the original array.

 

ReDim SubArray(1 To DataArrayRows, 1 To DataArrayCols) As Variant

 

Is it possible to use collections to "add-on-the-fly"?

 

Dim DataArray() As Variant
   
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Dim DataArrayRows As Long
   
    DataArrayRows = UBound(DataArray(), 1)
   
    Dim DataArrayCols As Long
   
    DataArrayCols = UBound(DataArray(), 2)
   
    Dim SubArray() As Variant
   
    ReDim SubArray(1 To DataArrayRows, 1 To DataArrayCols) As Variant
   
    Dim I As Long, j As Long, k As Long
   
    j = 1
   
    k = 1
   
    Dim Coll1 As Collection
    Dim Coll2 As Collection
    Dim Coll3 As Collection
    Dim Coll4 As Collection
    Dim Coll5 As Collection
    Dim Coll6 As Collection
    Dim Coll7 As Collection
    Dim Coll8 As Collection
    Dim Coll9 As Collection
    Dim Coll10 As Collection
    Set Coll1 = New Collection
    Set Coll2 = New Collection
    Set Coll3 = New Collection
    Set Coll4 = New Collection
    Set Coll5 = New Collection
    Set Coll6 = New Collection
    Set Coll7 = New Collection
    Set Coll8 = New Collection
    Set Coll9 = New Collection
   
    Set Coll10 = New Collection
    For i = 2 To DataArrayCols
   
        If DataArray(i, 12) <> "RD" Then
       
            Coll1.Add DataArray(i, 1)
            Coll2.Add DataArray(i, 2)
            Coll3.Add DataArray(i, 3)
            Coll4.Add DataArray(i, 4)
            Coll5.Add DataArray(i, 5)
            Coll6.Add DataArray(i, 6)
            Coll7.Add DataArray(i, 7)
            Coll8.Add DataArray(i, 8)
            Coll9.Add DataArray(i, 9)
            Coll10.Add DataArray(i, 10)
       
            k = k + 1
           
        End If
   
    Next i
   
    Sheet3.Cells(2, 1).Resize(k - 1, DataArrayCols).Value = SubArray()

 

but it seems cumbersome.

 

How can I use a collection without having to declare it many times?

 

 

Hi,

In this video, you mentioned the singular is usually the object in the collection, eg worksheet is an object of the collection of worksheets (plural).

You mentioned usually.

What are the exceptions?

Thanks

 

Could this be a solution?

 

Sub WithClassDo()

    Dim DataArray() As Variant
   
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Dim DataArrayRows As Integer
   
    DataArrayRows = UBound(DataArray(), 1)
   
    Dim Counter As Integer
   
    Counter = 2
   
    Dim MyName() As Class1
    Dim MyAge() As Class1

    ReDim MyName(1 To DataArrayRows, 1 To 2) As Class1
    ReDim MyAge(1 To DataArrayRows, 1 To 2) As Class1
   
    Set MyName(Counter, 1) = New Class1
    Set MyAge(Counter, 2) = New Class1
   
    Set MyName(Counter + 1, 1) = New Class1
    Set MyAge(Counter + 1, 2) = New Class1
   
    MyName(Counter, 1).Name = DataArray(Counter, 1)
    MyAge(Counter, 2).Age = DataArray(Counter, 2)
   
    MyName(Counter + 1, 1).Name = DataArray(Counter + 1, 1)
    MyAge(Counter + 1, 2).Age = DataArray(Counter + 1, 2)
     
    Do Until MyName(Counter, 1).Name <> MyName(Counter + 1, 1).Name
   
        Counter = Counter + 1
       

'***** NEW CODE

        Set MyName(Counter + 1, 1) = New Class1
        MyName(Counter + 1, 1).Name = DataArray(Counter + 1, 1)

'*****

      
    Loop
   
End Sub

We can use class modules to bring headings into our code to make things easier to read.

Assume there are two columns of data named Name and Age.

WITHOUT using class modules, we might write this:

Sub WithoutClassForNext()

    Dim DataArray() As Variant
   
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Dim DataArrayRows As Integer
   
    DataArrayRows = UBound(DataArray(), 1)
   
    Dim Counter As Integer
   
    For Counter = 2 To DataArrayRows
       
        MsgBox DataArray(Counter, 1)
       
        MsgBox DataArray(Counter, 2)
       
    Next Counter
   
End Sub

 

However, we can use class modules as follows:

 

'Class1

Option Explicit

    Private pName As String
    Private pAge As Integer

Public Property Get Name() As Variant

    Name = pName
   
End Property

Public Property Let Name(ByVal vNewValue As Variant)

    pName = vNewValue
   
End Property

Public Property Get Age() As Variant

    Age = pAge
   
End Property

Public Property Let Age(ByVal vNewValue As Variant)

    pAge = vNewValue
   
End Property

 

'Normal module:

Sub WithClassForNext()

    Dim DataArray() As Variant
   
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Dim DataArrayRows As Integer
   
    DataArrayRows = UBound(DataArray(), 1)
   
    Dim Counter As Integer
   
    Dim MyName As Class1
    Dim MyAge As Class1

    Set MyName = New Class1
    Set MyAge = New Class1
   
    For Counter = 2 To DataArrayRows
   
        MyName.Name = DataArray(Counter, 1)
        MyAge.Age = DataArray(Counter, 2)
   
        MsgBox MyName.Name
        MsgBox MyAge.Age
   
    Next Counter
   
End Sub

 

Both methods work.

The problem I have lies with a Do Loop.

WITHOUT classes, it might look like:

 

Sub WithoutClassDo()

    Dim DataArray() As Variant
   
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Dim DataArrayRows As Integer
   
    DataArrayRows = UBound(DataArray(), 1)
   
    Dim Counter As Integer
   
    Counter = 2
   
    Do Until DataArray(Counter, 1) <> DataArray(Counter + 1, 1)

        Counter = Counter + 1

    Loop
   
End Sub

 

But how might I do it using classes? My attempt as follows fails:

 

Sub WithClassDo()

    Dim DataArray() As Variant
   
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Dim DataArrayRows As Integer
   
    DataArrayRows = UBound(DataArray(), 1)
   
    Dim Counter As Integer
   
    Counter = 2
   
    Dim MyName() As Class1
    Dim MyAge() As Class1

    ReDim MyName(1 To DataArrayRows, 1 To 2) As Class1
    ReDim MyAge(1 To DataArrayRows, 1 To 2) As Class1
   
    Set MyName(Counter, 1) = New Class1
    Set MyAge(Counter, 2) = New Class1
   
    Set MyName(Counter + 1, 1) = New Class1
    Set MyAge(Counter + 1, 2) = New Class1
   
    MyName(Counter, 1).Name = DataArray(Counter, 1)
    MyAge(Counter, 2).Age = DataArray(Counter, 2)
   
    MyName(Counter + 1, 1).Name = DataArray(Counter + 1, 1)
    MyAge(Counter + 1, 2).Age = DataArray(Counter + 1, 2)
     
    Do Until MyName(Counter, 1).Name <> MyName(Counter + 1, 1).Name
   
        Counter = Counter + 1
   
    Loop
   
End Sub

 

Any help greatly appreciated.

 

Hi Andy,

I'm a little confused about Power BI.  Looking at your videos, it seems Power BI is a collective term for PowerPivot, Power Query, Power Map, etc.

However, googling Power BI, I see this, where the video shows a completely independent tool aimed at creating dashboards and reports, so can you please clarify what is Power BI?  Thanks.

Andrew,

Thought you might be interested to hear about the following named range bug in Excel!

We know each worksheet name has to be unique. Any attempt to name a worksheet that already has that name wil be met with an error prompt.  The same applies to named ranges - sort of!

Named ranges have two scopes, worksheet and workbook.

Suppose I created a named range, say myname, at the Sheet1 level, referring to cell A1.  Obviously if I try to create another named range called myname also at the Sheet1 level, the error prompt appears.

However, if you hide all named ranges in VBA, like this:

Dim nm As Name
For Each nm In Thisworkbook.Names
    nm.Visible = False
Next nm

Then if you attempt to create a named range called myname (and make it point to cell A2 of Sheet1) it will work! In doing so, it removes the original myname!

Andrew,

We know "usually" the default for passing arguments is by reference, so if the keyword ByRef / ByVal is omitted, the argument will be passed by reference.

In a standard module, this can (and has been) easily proved.

However, I have yet to see an example to prove the case fro a Get / Let / Set Property.

Do these properties also behave in the same way, ie the default is by reference? The reason for my query is if you create a Let Property using Insert -> Procedure -> Property, the keyworb, ByVal, is automatically added.

Does that mean for a Let Property (and I assume also for a Set Property), the default, perhaps the ONLY way to pass arguments is by value?

Is it possible to prove this by way of an example?

Also what about Get Properties? Do they pass by reference by default or by value?

Thanks

Usually you create a function of a type, such as:

Public Function MyFunc (arg As String) As String

End Function
 

However, I have come across functions such as:

Public Function MyFunc (arg As Someclass) As Dictionary

End Function

How does one go about learning this?

Yes, thanks.

 

 

I have got a copy of the database the accompanies this SQL video.  When I expand Database Diagrams, I don't have anything.  Why is that?

I find using joins difficult to understand.

In the video, you have:

select
 d.directorid,
 d.directorname,
 f.filmname,
 f.filmdirectorid
from
 tbldirector as d
 inner join tblFilm as f
  on d.directorid=f.FilmDirectorID

I prefer the following:

select
d.directorid,
d.directorname,
f.filmname,
f.filmdirectorid
from tbldirector as d
, tblFilm as f
WHERE d.directorid=f.FilmDirectorID

However, I can't seem to do the same for a LEFT JOIN.

I tried to replicate this query

select
 d.directorid,
 d.directorname,
 f.filmname,
 f.filmdirectorid
from
 tbldirector as d
 left join tblFilm as f
  on d.directorid=f.FilmDirectorID

by writing this but it returned more results (though I can't be sure if all the records from the query using LEFT JOIN is contained in this query):

select
d.directorid,
d.directorname,
f.filmname,
f.filmdirectorid
from tbldirector as d
, tblFilm as f
WHERE d.directorid=f.FilmDirectorID OR f.FilmDirectorID IS NULL

What is wrong?