Our training courses

Other training resources

Our training venues

Why we are different

Details for duggie

duggie has participated in the following threads:

Added by duggie on 21 Feb 2024 at 23:23

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?

Added by duggie on 13 Feb 2024 at 10:35

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?

Added by duggie on 15 Dec 2023 at 00:00

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.

Added by duggie on 01 Oct 2023 at 19:06

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

 

Added by duggie on 24 Aug 2023 at 16:25

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

 

 

 

 

 

Added by duggie on 21 Jun 2023 at 09:30

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

Added by duggie on 02 Nov 2022 at 12:43

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

 

 

Added by duggie on 22 Mar 2022 at 14:07

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?

 

 

 

Added by duggie on 29 Jan 2022 at 14:14

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?

 

Added by duggie on 08 Sep 2021 at 14:52

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)?

 

Added by duggie on 14 Mar 2021 at 12:44

Many thanks (sorry only about 2 years late)!

 

 

Added by duggie on 29 Oct 2020 at 00:34

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?

Added by duggie on 07 Oct 2020 at 13:26

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

Added by duggie on 25 Aug 2020 at 20:46

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?

Added by duggie on 24 Aug 2020 at 14:12

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.

 

 

Added by duggie on 22 Jun 2020 at 22:55

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.

Added by duggie on 22 Apr 2020 at 11:05

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?

 

Added by duggie on 18 Mar 2020 at 08:01

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

 

Added by duggie on 09 Mar 2020 at 12:26

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?

 

Added by duggie on 31 Jan 2020 at 13:25

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

 

Added by duggie on 03 Jan 2020 at 16:31

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.

Added by duggie on 27 Nov 2019 at 11:20

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

Added by duggie on 22 Nov 2019 at 14:14

Hi,

Can you tell me what is PowerApps?  Is it another application such as Powerpivot, Power View, Power Map or something totally different?

Added by duggie on 20 Aug 2019 at 09:34

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!

 

 

Added by duggie on 30 Jul 2019 at 12:47

 

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

 

 

Added by duggie on 20 May 2019 at 16:51

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

 

Added by duggie on 08 May 2019 at 20:58

Andrew,

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

 

Added by duggie on 04 May 2019 at 19:31

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

 

 

Added by duggie on 02 May 2019 at 14:12

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

 

Added by duggie on 04 Apr 2019 at 16:46

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

Added by duggie on 21 Mar 2019 at 15:56

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

 

Added by duggie on 28 Dec 2018 at 16:02

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

 

 

Added by duggie on 14 Nov 2018 at 13:15

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

Added by duggie on 15 Oct 2018 at 10:23

Brilliant!

Thanks for your detailed explanation, finally got it.

 

 

Added by duggie on 13 Oct 2018 at 08:51

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

Added by duggie on 22 Sep 2018 at 07:19

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 ()?

Added by duggie on 20 Sep 2018 at 21:44

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?

 

 

Added by duggie on 01 Aug 2018 at 12:17

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

 

Added by duggie on 06 Jul 2018 at 00:42

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

Added by duggie on 05 Jul 2018 at 22:46

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.

 

Added by duggie on 23 Jun 2018 at 07:59

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.

Added by duggie on 16 Jun 2018 at 18:53

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!

Added by duggie on 11 Jun 2018 at 17:37

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

Added by duggie on 04 Nov 2017 at 11:43

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?

Added by duggie on 27 Oct 2017 at 16:01

Yes, thanks.

 

 

Added by duggie on 27 Oct 2017 at 10:34

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?

Added by duggie on 21 Oct 2017 at 12:23

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?

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.

End of small page here
Please be aware that our website uses cookies!
I'm OK with this Tell me more ...