• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to populate Pivot tabel using VBA (improvement in code)

ThrottleWorks

Excel Ninja
Hi,

I am using below mentioned recorded (and edited) code to populate a Pivot table.

This is working for me now, however can anyone suggest improvements to this code if possible.

Code:
Dim PvtDest1 As Range
    Set PvtDes1 = Pvt.Range("b10")
   
    Dim PvtStrg1 As String
    PvtStrg1 = "Age Status"
   
    Dim PvtStrg2 As String
    PvtStrg2 = "Age Bucket"
   
   
    Pvt.Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Rng16, Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:=PvtDes1, TableName _
        :="PivotTable4", DefaultVersion:=xlPivotTableVersion14
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable4").PivotFields(PvtStrg1)
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
        "PivotTable4").PivotFields(PvtStrg2), "Count of Age Bucket", xlCount
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Trade Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
 
Here's the suggestions I'd make
Code:
Dim PvtDest1 As Range
Set PvtDes1 = Pvt.Range("b10")

'If these strings are always going to be the same thing,
'might consider making them constants rather than variables
'Saves a bit of memory
Const PvtStrg1 As String = "Age Status"
Const PvtStrg2 As String = "Age Bucket"

Application.ScreenUpdating = True

'Don't need to select things
'Pvt.Select

'Always the active workbook, or this workbook?
'Also, note that you've hardcoded a pivot table name. Is that the name
'you want to use? Should it be a user prompt? Depending on your scenario
'you may want to make that a variable somehow
Dim ptName As String
ptName = "pt_4"
ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    Rng16, Version:=xlPivotTableVersion14). _
    CreatePivotTable TableDestination:=PvtDes1, TableName _
    :=ptName, DefaultVersion:=xlPivotTableVersion14

'Don't need this
'ThisWorkbook.ShowPivotTableFieldList = True

'Since we're doing several things with this PivotTable, let's assign it to
'a variable so we don't have to process so much
Dim myPT As PivotTable
Set myPT = Pvt.PivotTables(ptName)

With myPT.PivotFields(PvtStrg1)
    .Orientation = xlColumnField
    .Position = 1
End With

'Removed the hardcoded Count of ___ so that it changes if we
'want to change our overall variable
myPT.AddDataField myPT.PivotFields(PvtStrg2), "Count of " & PvtStrg2, xlCount
With myPT.PivotFields("Trade Type")
    .Orientation = xlRowField
    .Position = 1
End With

'Or this
'ActiveWorkbook.ShowPivotTableFieldList = False

Application.ScreenUpdating = True
 
Hi Luke Sir, I have a doubt.

Why should we use string as a variable, is it not correct to use strings as constant only.

I just read why and how constant are defined in JW's book but my guess is, it is feasible to use string as constant only.

Kindly guide if you get time.
 
Hi,

My doubt is, when I can define string as Const why should I define it in another way.

If it is more beneficial to use Const cause it saves memory what would be the purpose of defining as "Dim S2 As String S2 = 'Honda' ".

If my strings are going to be the same all over code, if I am not wrong I should be using Const only.

Code:
Sub text()
    Const S1 As String = "Yamaha"
   
    Dim S2 As String
    S2 = "Honda"
   
    MsgBox S1
    MsgBox S2
End Sub
 
AFAIK Object is almost always a variable, and Pivot table can't be constant.

Text, Numbers, etc can be constant, and as Luke stated saves bit of memory. Especially when you start dealing with large array calc etc.
 
Note that VB reads code left to right, top to bottom. Now, let's step through different layers of defining something

Code:
'A
Dim strA
strA = "Bob"

'B
Dim strA as String
strA = "Bob"

'C
Const strA as String = "Bob"

In example A, the code first sees that we want some type of variable. This is the loosest type of variable definition you can do in VB, and preferably not be used. At the definition line, VBE has no idea what type of variable we will use, so it has to allocate the max possible amount of memory in case you're storing a large object or something. Then later, when it gets assigned a string, it has to first determine what you are setting the value to (a string), and then stores that information. Note that the overall memory allocated to this variable is still very large, since it could change at any point.

In example B, the VBE knows now that the strA is going to be a string. This requires less memory that an object variable would, so we don't have to allocate quite so much. However, it doesn't know what size it will be. This is a variable length string then. Looking at the VBE help:
upload_2015-8-11_8-17-53.png
We can see that VBE at this point has to allocate enough memory for 2 billion characters. Even after we assign a value, it's still a variable length variable, so the memory allocation remains.

Finally, in example C, the VBE first learns that we are going to be creating a constant string. This means we need far less space, only up to 64k characters possibly. Finally, we also gave it the value, so it only has to allocate enough memory for 3 characters. VERY TINY!

So, why choose one over the other? If we know at the beginning of the code what the value of our variable is going to be, and it's not changing, use a constant. If it's something the code will have to learn, such as by reading the XL sheet, asking user for input, etc., then use a variable. The most common example I see for using a constant is when building a password check, and you want to test user input against some value.
Code:
Sub ExamplePassword()
Const myPass As String = "Pass1234"

If myPass = InputBox("What is the password?") Then
    MsgBox "Success"
Else
    MsgBox "Fail"
End If
End Sub

Does that help?
 
Hi !

Exception for Const statement : if no data type in codeline,
VBA compiler will force to an appropriate one !

For example Const S = "abcd" is internally declared as String
Easy to check in debug mode via Local variables window.

Const N = 1 is declared as Integer.
To save memory, you can force it like Const N as Byte = 1

Some variable type can be forced at first appearance in code
via a character, for example S$ = "qsdf"
See VBA help of each variable type.

And using DefStr T in top of module force any variable
name and any undefined Function starting by T to String …​
 
Last edited:
Hi @Chihiro , @Luke M Sir, and @Marc L thanks a lot for very detailed answer.

Good night. :)

Also, just read following information in JW's book regarding limiting length.

Moderator, please remove below reference if it is not legal to share information copied from ebook.

When declaring a string variable with a Dim statement, you can specify the
maximum length if you know it (it’s a fixed-length string) or let VBA handle it
dynamically (it’s a variable-length string).
The following example declares the MyString variable as a string with a maximum length of 50 characters. (Use
an asterisk to specify the number of characters, up to the 65,526 character
limit.)
YourString is also declared as a string but its length is unspecified:
Dim MyString As String * 50
Dim YourString As String
 
Last edited:
Back
Top