VBScript tips - excel macro to fill formula

Thanks for the post here, then I can now use the syntax highlight in google post. 
As a programmer, takes several hours to figure out some vb usage. Some performances/concepts are different as normal programming language. So I summary some tips as below:
The example snippet I implemented is as below, this macro will generate excel formula and fill them to the excel file.
Sub FillReportSummary()
'
' FillReportSummary Macro
'
'
    Dim description As Variant
    description = Array("Type A", "Type B", "Type C", "Type D")

    Dim headerNum As Integer
    headerNum = UBound(description) + 1

    'MsgBox ("headerNum: " & headerNum & description(0))

    Dim headerFormulas() As Variant
    ReDim headerFormulas(1 To headerNum)

    Dim strFormulas() As Variant
    ReDim strFormulas(1 To headerNum)

    Dim calSumFormula As String
    
    'With ThisWorkbook.Sheets("SheetName")
    With ActiveWorkbook.Sheets("SheetName")
        For i = 1 To headerNum
            headerFormulas(i) = "=IF($A5=""Type"", ""Total" & description(i - 1) & ""","""")"
            calSumFormula = "SUMIF(INDIRECT(CELL(""address"",INDEX($B$1:$B14,MATCH($B14,$B$1:$B14,0)))):$B14,""=" & description(i - 1) & """,INDIRECT(CELL(""address"",INDEX($B$1:C14,MATCH($B14,$B$1:$B14,0),2))):$C14)"
            strFormulas(i) = "=IF($A14=""Total Client"",IF(ISBLANK(" & calSumFormula & "),""0"", " & calSumFormula & "),"""")"
            'MsgBox (headerFormulas(i))
            'MsgBox (strFormulas(i))
        Next i
        .Range("G5:J5").Formula = headerFormulas
        .Range("G14:J14").Formula = strFormulas
        .Range("G14:J4000").FillDown
        .Range("G14:J4000").Font.Color = vbRed
        .Range("G14:J4000").Font.Bold = True
        .Range("G14:J4000").NumberFormat = "#,##0"
    End With

    MsgBox ("Macro run to get the client level summary is completed!")
End Sub

1.  Better to use "Variant" type for array type if the array size is unknown yet. If want to define an array of string, then need to give the size of the array. The array is start with index 0.

    Dim description(4) As String
    description = Array("Type A", "Type B", "Type C", "Type D")

2. Can not use variable in "Dim" definition, need use ReDim after a Dim.

3. Use MsgBox() to get the info to test value of variables.

4. Use "&"(without ") to concatenate or join strings and variables.

5. Can use "ThisWorkbook.Sheets", then the macro must be in the same excel file, or use "ActiveWorkbook.Sheets", then can use macro from other opened .xlsm in current/active .xlsm or .xlsx file.

6. When use excel formula with string in it, then need use a double-quote(") to escape the double-quote(").

7. An "INDIRECT()" function must be use when need to return the result of the CELL() function as part of the formula.

8. Display number 0 in excel or not(be empty when result is 0). Number format with thousand separator "#,##0" will let the excel display 0; "#,###" means not display 0.

9. Always add a MsgBox() after the script is done. This is a good habit to notify users the process is completed/finished. This is especially help for non-tech users.


Comments

Post a Comment

Popular posts from this blog

install ipython in Cloudera VM using pip

I am practicing pyspark in Cloudera VM, and pyspack need be launched by ipython. In all the old Cloudera VM guide, it use easy_install to install ipython, but it does not work in Virtualbox 5.2.0 r118431 and later. And will get the error : $sudo easy_install ipython==1.2.1 ... error: Could not find suitable distribution for Requirement.parse('ipython==1.2.1') The solution for this is to use pip to install ipython. But by default, pip is not installed in cloudera VM, and pip can not be installed by eacy_install as well. And after some search, I  noticed cloudera has yum installed, and yum can install pip: $sudo yum install python-pip ... $which pip /usr/bin/pip Now ipython can be installed b pip: $sudo pip install ipython=1.2.1 ... Succesfully installled ipython-1.2.1 After that, launch spack by ipython: $PYSPARK_DRIVER_PYTHON=ipython pyspark ... Welcome to       ____                      ...
Keep reading

install postgreSQL in ubuntu 16.04

When following the Install PostgreSQL in Ubuntu  in Ubuntu 16.04, got error as below: $ apt-get install postgresql-9.6 Reading package lists... Done Building dependency tree Reading state information... Done You might want to run 'apt-get -f install' to correct these: The following packages have unmet dependencies: adobereader-enu:i386 : Depends: libgtk2.0-0:i386 (>= 2.4) but it is not going to be installed postgresql-9.6 : Depends: postgresql-client-9.6 Depends: postgresql-common (>= 171~) but it is not going to be installed Recommends: postgresql-contrib-9.6 but it is not going to be installed E: Unmet dependencies. Try 'apt-get -f install' with no packages (or specify a solution). The suggested solution in the last line and a normal ubuntu upgrade do not solve the issue,  and the cmd below can fix it: $sudo apt full-upgrade After the fix, then can apt-get to install postgresql again. After install postgresql, ...
Keep reading

Install MYSQL 5.6 in ubuntu 12.04/14.04 and set password and remote access

Part 1 : The steps to install mysql 5.6 in ubuntu 12.04 Note: it is also possible that , when you install mysql, there is a pop-up window to ask you to enter the password for root, and you/'d better remember  that in case there will be some extra work to set password in the Part 2. 1. check the system OS version uname -i 2.If there is an old mysql version running on your system and you need to uninstall it, and if you also install a .deb version, you need to use the "dpkg" one to uninstall: sudo apt-get remove mysql-server mysql-server-5.5 mysql-server-core-5.5 sudo apt-get remove --purge mysql-server mysql-client mysql- common sudo dpkg --pure mysql-server mysql-client mysql-common sudo apt-get autoremove sudo apt-get autoclean deluser mysql sudo updatedb ##sometimes after above cmds,using "locate mysql" still lists many file related to mysql, that is because the system database is not updated## sudo find /var/lib/mysql/ -type f -delete ##delete ...
Keep reading