Categories
Excel

Launch of BSuites.ca, MS Office & G Suite Services

Congrats to BSUPERIOR SYSTEM team who are continuously working on our new website, BSuites.ca, Custom Microsoft Office & G Suite Solutions. We already launched this website and continue our Excel and other services there. This website is a division of our BSUPERIOR family and proudly working with other BSUPERIOR divisions to serve the businesses in our community.

Why Separate Website?

These are the reasons that we made a decision to develop BSuites.ca.

Canadian Domain

Our current website, MSExcelVancouver.com, is a dot com domain. Domains of dot com is commonly used throughout the blue planet and not necessarily specific to a location such as Canada. We decided to make our website more Canadian and emphasize on our presence in Canada. Therefore we chose a dot CA domain to represent that we are Canadian.

Not Just Vancouver

MSExcelVancouver.com includes “Vancouver” in its domain name. We started from Vancouver and originally provided services to businesses only in Vancouver. We grown up and cover other cities of Canada and already doing business in Calgary, Edmonton and Toronto. “Vancouver” might be misleading that we are targeting only Vancouver. We decided a new domain name which is not only limited to Vancouver.

Expansion of Services

MSExcelVancouver.com includes “Excel” in its domain name. The first service of BSUPERIOR SYSTEM was Excel customization and automation, and that’s why we decided to emphasize on our only service in our domain name. Since then, we expanded our services which is not limited to Excel, such as the automation of other MS Office products and G Suite. 

It’s the time to choose a domain name which represents all of our services. “Suites” in BSuite.ca domain is plural of “Suite” which can be interpreted as a software suite or an application suite. Both Microsoft Office and G Suite are a collection of software or application suites. Therefore our new name is relevant to our services. 

“B”

Additionally, “B” in “BSuites” symbolize the name pattern used in the domain names of BSUPERIOR SYSTEM family, such as BSUPERIORSYSTEM.com. “B” in BSUPERIOR is the abbreviation of two words, “be” and “business”. Therefore, BSUPERIOR name represents both being superiority and business superior solutions.

Shorter Domain Name

A common advice from our user experience team is to be short, meaningful and simple. Our current domain name, “MSExcelVancouver.com” is a little too long even though it’s meaningful about our main service, Excel. We came up with a shorter and simpler domain name. 

What is BSuites.ca?

BSuites.ca is a collection of services regarding the products of Microsoft Office and G Suite apps. A list of services is,

  • Excel Consultation and Automation:  We already offering Excel services such as consultation, automation and customization in our current website and will be continuing them in the new website.
  • Other services to be added soon.
Categories
Excel

Maximizer CRM & Database Structure – Mapping Fields to Database Columns

In this blog, we explain how to map fields in a Maximizer CRM page to SQL database. The structure of Maximizer software on your computer might be different and we use our Maximimer structure as a reference.

If you’re looking for help for Maximizer or Excel, please feel free to contact our Maximizer and Excel experts.

Direct Mapping

Our Maximizer includes a page called “Opportunities”. The table on this page is mapped to a SQL table called, “AMGR_Opportunity_Tbl”. Many of the fields on Opportunities page are directly mapped to the columns of the SQL table; however, the naming might be different. For example, Opportunities page contains a field called, “Job Description” and it’s mapped to “Comments” column on the SQL database table. How did we find the mapping between these two? We picked a value in Job Description field and searched the entire database for this value. The search results referred to Comments column of AMGR_Opportunity_Tbl table.

ٔNon-Direct Mapping

However, not all fields are directly mapped to a column. Some fields on Opportunities page are defined by user. These fields are managed by “AMGR_User_Field_Defs” and “AMGR_User_Fields” SQL tables. We explain two examples of user-defined fields.

user fields

Opportunities page includes another field called, “Estimate #”. This field is not directly mapped to AMGR_Opportunity_Tbl database table. The value of this field is stored in “AMGR_User_Fields” database table because it’s a custom field defined by user. Let’s find the Estimate # of a row in Opportunities page. In the first step, find the ID of that row. In our Maximizer SQL database, the ID is stored in “Opp_Id” column of AMGR_Opportunity_Tbl.

Open AMGR_User_Fields SQL table and look for the ID (found in the first step) in Client_Id column. You probably find many rows. Each row in this table is storing some user defined fields and each user defined field has a unique Type_Id. In our Maximizer table, the Type_Id of Estimate # is 44.  Therefore, look for a row in AMGR_User_Fields table that has the specific ID and Type_Id that you are looking for. When you find the row, look into “AlphaNumericCol” column which stores the value of the user defined field. Image below shows the process visually.

user field definistions

In addition of the fields discussed, there is another special field in Opportunities page called, “Type”. The value of this field is stored in AMGR_User_Field_Defs SQL database table. As explained in USER FIELDS section, look for a row in AMGR_User_Fields that has the ID and Type_Id that you’re looking for. When you find that row, grab the value in “Code_Id” column.

Then open AMGR_User_Field_Defs table and look for the row which has the specified Type_Id and Code_Id. The value of the field that we were looking for is stored in “Description’ column of that row.

Maximizer & Excel Help

If you need help with your Maximizer and Excel files, please feel free to contact us and we provide you a free quote.

Categories
Excel

Best Practices in Writing VBA Code for Developer Teams

Are you part of a development team? Are you writing VBA code for your team project? Are other team members working on the same project? What are the best practices in writing VBA codes so other team members can understand your code efficiently and continue the job smoothly?

As Excel Consultants in Vancouver, we share our experiences within our own group.

Define Constants

A sheet name might be used all over the code. Similarly, a range name is most often used. What if a constant is defined for these names? A team member might have to change a sheet or a range name. With defining constants, they don’t have to look for it all over the code just because they want to change a sheet name. They can simply, change the value of the constant.

A Module for Constants

You can create a module called “Constants” and place all your constants in that module. Make sure you categorize your constants, for example, place constants for each sheet names below each another. Create another section for range names. Make sure you write a comment for each section, and explain what this section is about. Reading comments is easier for your team member than reading codes. You can see a screenshot of a module that I created for constants below.

VBA Module for Constants - Excel

Declare Variables

In VBA you don’t get an error if you don’t declare variables. However, this is a must, especially, if you’re programming in a team based environment. Use “Option Explicit” on top of your code. Using Option Explicit forces you to declare all your variables. Define your variables at the beginning of a function because team members who are reading the code are probably looking for the list of variables at the beginning of a function.

Declare Variables with Specific Data Type

Make sure to declare the variable with a specific data type to give others a clue of what the purpose is. For example, “Dim c” might have multiple meanings. Some might interpret variable “c” as a counter (integer type) but others as a column name (string type).

Comments and Proper Naming for Variables

In addition, include comments for variable and write the purpose, if the variable is for specific use. Try proper naming. Which variable is more meaningful for declaring a report workbook, the one called “rep”or “reportwb”?

This is a sample of how we declared a variable:

' Master workbook
Dim masterwb as Workbook

Function Names & Commenting

Descriptive Function Names & Commenting in Calling Functions

Functions are for specific use. Why not use a specific descriptive name for them? When you find a function called in the middle of another function and you want to know what it is for, what would be the easiest way? I would say the answer is, proper function naming and commenting. If the function name is descriptive, you wouldn’t need to scroll all the way down to find the function and study its structure. A descriptive function name saves a lot of time in scrolling. However, often a descriptive function name is not enough. Make sure to add comments when calling a function, explain what the function does and possibly what it returns.

Descriptive Commenting in Defining Functions

So, another programmer is reading your code and is wondering why you created a function called “adjust_columns”. They get a clue from the function name that it has something to do with fixing rows, but what exactly does it do? They could study the function code or you could save their time by writing some comments about what the function does. For instance, you can explain that this function adjusts the width of all filled columns in a given sheet to make sure the values in the columns are visible.

Other than the purpose of the function in the comment area, it’s a good idea to explain:

  • the inputs,
  • the outputs, if any,
  • and requirements, i.e. a requirement of a function before it’s called that a specific sheet should be active.

Practices for Functions

Short Functions

It’s a good practice to make functions short. Going through a long function might be frustrating for others. Break your long functions into smaller ones. It is easier to understand three small functions rather than a long one, right? I, at least, prefer reading smaller functions.

Repetitive Code in a Function

Avoid repetitive code. If your team member wants to change something in a repetitive code, they must change all of the repetitions. Define a function and add the repetitive code in that function.

Main Function

In some projects, there is a main function which is called first and calls other functions. In C or C++, this function is called main(). In VBA, we could follow the same practice and call the main function as main() and place it above all other functions. Consequently, other programmers can easily identify it.

Indent Style & Next Line

Indent your code specially inside loops, “if” conditions, and other blocks. Additionally, if a line gets too long, use “_” to break it into multiple lines. This helps programmers visually picture the code block in their mind.

Commenting

Commenting a source code could lift pressure! Reading comments instead of a code reduces frustrations and helps other team members understand what your code does. Place comments for,

  • Functions: explain what a function does, the inputs, outputs and requirements.
  • Specific variables: what a variable is specifically used for.
  • At the top of a module: write the purpose of the module.
  • Unified block of code: for example, include comments for a loop, or a block of a code used for opening a workbook or a specific goal.

Finally, it’s a good practice to time stamp each comment so other programmers know when the related code is written or updated.

If you’re looking for a team of Excel VBA Developers, please feel free to contact us for a free quote.

Categories
Excel

How To Parse an Address in Excel by Macro/VBA

Do you have an address and are looking for extracting the address line, city, state (province), country and zip code (postal code)? In this blog, we will explain how to make an Excel file to parse an address. If you’re interested in a custom solution for your Excel file, please contact our Excel expert consultants for a free quote.

Google API Key

We used Google Geocoding API to extract the information from an address. To use Google API, you’re required to have a Google API key.

Demo

Feel free to download a demo of the Excel XLSM file.

How the Excel file works?

  1. In range C1, type the address that you want to parse, for example “1455 Crown St Ste A, North Vancouver, BC”.
  2. In range C2, enter your Google API key.
  3. Click “Parse Address” button. In the yellow area marked by “Google Map Address Structure”, the processed address by Google will be displayed.

Development

If you are familiar with VBA Editor and creating buttons, you can copy/paste the following VBA code into your VBA editor. And create a button to execute this function.

Then, install Json Converter developed by https://github.com/VBA-tools/VBA-JSON. i.e. create a module called “JsonConverter” and copy/paste the code written in the Github link in the module.

Once you copy/paste the code and created a button, type an address in range C1, and your Google API key in range C2. Then click the button. The address is parsed!

If you’re looking for more help regarding your Excel files, please contact our Excel consultants.

VBA Code

' Developed by msexcelvancouver.com

' Extract Full Address to
' Street Number, Route, Neighborhood, Locality, Administrative Area, Country and Postal Code
Sub ExtractAddress()
    Dim sURL As String, sResult As String
    Dim oResult As Variant, oData As Variant, R As Long, C As Long
    
    ' Clear the sheet
    ActiveSheet.Range("B3:C10").ClearContents
    
    sURL = "https://maps.googleapis.com/maps/api/geocode/json?address=" + _
        WorksheetFunction.EncodeURL(Range("C1").Value) + "&sensor=false" + "&key=" + Range("C2").Value
    
    Debug.Print "URL: " & sURL
    sResult = GetHTTPResult(sURL)
    
    Dim Json As Object
    Set Json = JsonConverter.ParseJson(sResult)
    
    Dim element As Variant
    
    R = 2
    
    On Error GoTo Google_Err
    For Each element In Json("results")(1)("address_components")
        ActiveSheet.Cells(R + 1, 2) = element("types")(1)
        ActiveSheet.Cells(R + 1, 3) = element("long_name")
        R = R + 1
    Next element
    
    Set oResult = Nothing
    Exit Sub
    
Google_Err:
    MsgBox "Macro got a problem in connecting to Google API. " & _
        "Please make sure yor Google API key is valid and working."
    Set oResult = Nothing
End Sub

' Get the json result
Function GetHTTPResult(sURL As String) As String
    Dim XMLHTTP As Variant, sResult As String

    Set XMLHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    XMLHTTP.Open "GET", sURL, False
    XMLHTTP.Send
    Debug.Print "Status: " & XMLHTTP.Status & " - " & XMLHTTP.StatusText
    sResult = XMLHTTP.ResponseText
    Debug.Print "Length of response: " & Len(sResult)
    Set XMLHTTP = Nothing
    GetHTTPResult = sResult
End Function

Categories
Excel

The difference between XLSM and XLSB extensions in Excel

In this part I will explain about XLSB extension which is useful but many users are not aware of its benefits. As you know the default format for data which are stored in Excel is XLSL extension, and if your file includes VBA code or a macro it will be stored in XLSM format. There is another format in Excel for the files which include macro, named as XLSB, but is used rarely. In the following we will learn more about XLSB format and understand when it ought to be used.

Both XLSM and XLSB are in fact compressed files which you can open them in software such as WinRAR. Typically, XLSM format uses XLM to store the data, while XLSB includes a binary workbook which helps significantly to reduce the file size. Here we have discussed the main advantages and disadvantages of using XLSB compared to XLSM format. All in all, if you have a large and complex Excel file, you’d better to store it in XLSB format.

The advantages of XLSB over XLSM:

  • Excel will run the file faster
  • Much Smaller size in comparison with XLSM format

Disadvantage of XLSB format:

  • Not compatible with other Microsoft software such as Openoffice, etc.

 

Categories
Excel

Combination of INDEX and MATCH functions in Excel

INDEX and MATCH functions can be used together to create flexible and powerful formula in Excel. As said before, the INDEX function can return the value at the juncture of a given row and column. Now instead of manually setting value for the column and row, we can get help from the result of MATCH function to solve the problems with providing formula. By combining INDEX and MATCH functions in Excel, we may search for the lookup value in a column or a row and return a value in the corresponding cell. In the following we will learn more about the applications of these two functions in Excel. Consider the table below to understand easier:

1

If we want to find the name of the product according to its number 1398, for instance, we may use a VLOOKUP function formula like VLOOKUP(1398,A:B,2,FALSE).

But now consider the table below:

2

Here we do not use VLOOKUP function, but rather a combination of INDEX and MATCH functions. Because we cannot set the third parameter as -1 in VLOOKUP function. Therefore, we use INDEX MATCH function as follows:

  • First we write an INDEX function and determine its first parameter as A: A, since we are going to find the name of the product which is located in Column A.
  • we could give the row value to INDEX function before. The row value for the number 1398 is equal to 4. But now we want to find the row value automatically by the Excel rather than manually. So we do this using a MATCH function. Function MATCH(1398,B:B,0) will find the number 1398 in Column B and will return the corresponding row value which is equal to 4.
  • Then we assemble the two functions and get this formula:

INDEX(A:A,MATCH(1398,B:B,0))

Now we can change the number 1398 with another cell or number to find the corresponding product name.

 

Categories
Excel

How to create a Macro in Microsoft Office and use it?

You might have experienced being required to do repeatedly some tasks in Excel. Examples being tasks such as copying a certain part of a software’s output in another sheet, etc. Doing something repeatedly usually takes too much time and is also boring. You may get rid of this work using macro tool in Excel. A macro is a piece of computer code or a set of actions that is written for Excel using the Visual Basic for Applications (VBA) programming language. By macro you can automate your tasks so that you may not need to repeat them manually. In the following you will learn how to create a macro in Excel.

Here I will show you how to create a simple macro using Excel’s Macro recording functionality. Once you have recorded the macro, you can repeat the set of actions as many times as you like, by simply running the recorded macro in Excel. We can also create macros using VBA which assists us in writing more complex and advanced macros. Creating macro by VBA requires more knowledge, so we will discuss it separately in future. Now let’s create a simple macro by Excel. Suppose that the output given below pertains to our software:

1

As you see the output does not follow an appropriate format. According to the output, if we want to provide a daily report about the product “mouse”, for instance, in a format like below:

2

then once we get our output every day, we have to categorize our data in accordance with the types of “mouse” product, choose appropriate font for the texts of the output, and highlight the first line in Bold type, etc. which takes a long time and is too boring. Now we want to automate this task by creating a macro in Excel. In order to create a macro, you need to start the recording process. To do this, click on Macros menu which is located in the View Tab, and then select the option “record macro”.

3

Then you will be presented with an option box by which you can modify settings related to the macro which you are going to create. The setting options include:

4

Macro name

you can choose a name for your macro.

Shortcut key

you can assign a keyboard shortcut to easily run the macro. For example, you can write the letter U, so that you will run the macro by Ctrl+U.

Store macro in

By this option you can determine where to store the macro. If you select this workbook, the macro will be stored in current workbook. Remember that you should store the macro in xlsm extension. By Choosing new workbook option, a new workbook will be created and the macro will be stored in it.

If you choose personal macro workbook, the macro will be stored in a personal file named as personal.xlsb. whenever you open Excel, the file will be opened as a hidden file. Therefore, if you want to have your macro available in all workbooks, you should store it in personal macro workbook.

Description

Here you can enter a description for your macro

Once you have modified all the settings, click Ok to start recording. As soon as your macro starts to record, every action that you perform will be captured. Then you will perform the actions that you want to record in your macro. For example, here we change the font of the text, highlight the first line in Bold type, and categorize the types of “mouse” products.

When you have completed the actions, you can stop the macro recording by clicking on the stop button which is located in macros menu.

5

Now your macro will be stored in the location where you had assigned before. From now on, you can click on View macros to run these actions automatically. having clicked on View macros option, a list of created macros will be shown, and you should choose your macro and select run.

6

7

This way you can repeat the set of actions as many times as you like, by simply running the recorded macro. This is much more efficient than repeating the same set of actions manually each time. However, macro has more applications in Excel which I will explain them separately in future.

 

Categories
Excel

What’s New in Microsoft Office Excel 2016?

Hi. As you probably know, the newest version of Microsoft Office, Microsoft Office 2016 was officially released a few days ago. Similar to the previous versions, this version includes many new features and enhancements. Excel, as a product of Office package, has always included a number of new capabilities and features in the latest version. We will get to know some of the most important new features of Excel 2016 in the following:

  • New Charts

Six new charts have been added to Excel 2016:

Waterfall chart:

waterfall

Histogram:

Histogram

Pareto chart:

Pareto

Categories
Excel

How to STDEV on different ranges using Excel formulas?

Consider an Excel file in which we have different group of numbers and we want to calculate STDEV for each rang using a formula. The process is as follow,

Group of numbers in an Excel sheet

 

=IF(A1="",STDEVA(INDIRECT(CELL("address",A2)&":A"&SMALL(IF(A2:A200="",ROW(A2:A200),""),1)-1)),"")

Note: This is an array formula and should be inserted with Ctrl+Shift+Enter
STDEV (Sample Excel File)

Categories
Excel

Inserting blank rows between the rows of a database

This blog post is about inserting rows between the rows of a database. Selecting rows one by one and insert row is very exhausting especially when there are so many rows to do so. A better solution is as follow,

Consider the database shown in the image below.

A sample Database in an Excel Spreadsheet

Step 1:

Insert a column to the left of the database and type numbers from 1 to the last row number of database-1. (here we have data up to row 16, so I will type number from 1 to 15)

Inserting a column to the database

 

Step 2:

Now copy the group of numbers under each other, how many times? Depends on the number of blank row you want. For example, I want to insert 2 blank rows between each row. So I copy and paste these numbers twice under each other:

Inserting group of numbers

Step 3:

Now select whole database and go to Data>sort and do as shown in below figure:

Data & Sort Database

After clicking OK you will have 2 blank rows between your database rows. Then you can delete the helper column (column A)

Database Blank Rows

And after deleting the helper column (column A) we will have this:

After deleting the database helper column

Additionally, for inserting a column between columns in a database, do similar steps above and set the option in sort to left to right, then do as shown above.

Inserting column between columns in a database

\\