VBA - Fit to Screen

Have you ever tried to see a worksheet on your 22" screen and then look to the same report on your laptop, as realize that the worksheet do not fit in the screen?

Well hopefully this will be the answer, for when to build the Dashboard/Report:

Click Alt + F11 to open the VBA Editor

Select sheet that you want to open and fit to screen, and select Worksheet (top left drop down) and, Activate (top left drop down), you should see something like...

Private Sub Worksheet_Activate()

Dim ws As Worksheet

Set ws = Worksheets("Zoom2") ' ~~~ change sheet name as required
ws.Range("a1:Aa1").Select ' ~~~ change range as required
ActiveWindow.Zoom = True
ws.Range("f2").Activate

End Sub

Your worksheet should now adjust itself to the screen where you open it.

List "Defined Names"

List Defined Names

If you are like me, when you finish the project you will be lost with all the components of your dashboard, more importantly, when a change needs to be perform you need time to study the dashboard again, or, your colleagues will be lost, in particular when you build the dashboard using “defined names”.

Now, how do you keep track of all these names?

Thankfully, there is just the feature for this, called as “paste names“.

This is how it works.

·         Insert a new worksheet

·         Press F3 (Paste Name dialogue will be displayed)

 

·         Click Paste List

Eureka!!

VBA saves as filename plus add date to filename

Process to save a file with a specific name and add the date to the filename


Dim strDataPath As String

strDataPath = "C:\YOURLOCATION"

ActiveWorkbook.SaveAs FileName:=strDataPath & "CI Services Workstack Automation Data" & "-" & Format$(Date, "DD-MM-YY") & ".xls"

' ~~~ strDataPath is your location where the file is to be saved

Process to save the current filename, and, add the date to filename

Sub MyFileSaveAs()

' File needs to be saved as a xlsm

            Dim last_dot As Long

           last_dot = InStrRev(ActiveWorkbook.FullName, ".")

           Dim Name As String

Name = Left$(ActiveWorkbook.FullName, last_dot - 1) & "-" & Format$(Date, "DD-MM-YY") & Mid$(ActiveWorkbook.FullName, last_dot)

        ActiveWorkbook.SaveAs Filename:=Name, FileFormat _

    :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub

Number Format (Simple)

I am sure that you have come across many times to one of the most common issues in Excel, numbers which are formatted as TEXT, therefore when you are trying to use them as a reference it won't work, so Microsoft created a simple solution using the formula VALUE.

This is a very simple and straightforward way to convert the TEXT to a Number, however and as you know Microsoft also offer the solution using the drop down when you "Hoover" on the small triangle located on the top right hand side of the cell:

But then again you need to select the full range and depending of the number of items selected it may take a longer time to convert, so and with the "VALUE" formula you can speed up the process.

To use the "VALUE" formula you just need to follow some simple steps:

1. Let's imagine that the range of numbers formatted as Text are in column C row 6.
2. Go to an empty column or Insert column next to the column where the range is.
3. Now in the column and row 6, enter =VALUE(C6)
4. Copy the formula to cover the required range.
5. Simple, your new range can now be used as a reference for any formula that you require.

 

Use INDEX and MATCH functions for table lookup

The table lists some products and from where they are originally from, and the quantity that my establishment received.

How can we look up the location from where the Product originated from? 

Normally we just create the VLOOKUP function as

“=VLOOKUP("Oranges",$A$2:$C$9,2,0)”

The result would be “Portugal” as we ask to look up “Oranges” within the table and to give the result in column 2.

Nothing wrong with this, BUT, what about if you want to know the reverse, like, from which Location is the product “Oranges”, well here is a bit more tricky as the function VLOOKUP do not look to the left, so using the INDEX AND MATCH function is better and more powerful.

So how will we do it?

For this example we use the syntax:

The MATCH function, syntax:  MATCH(lookup_value;array;match_type)

Lookup_value: is the value you want to match in lookup_array. It can be a value like =MATCH(10,A1:A15,0)or a cell reference like =MATCH(B2,A1:A5,0).
Lookup_array: is a contiguous range of cells containing possible lookup values. Lookup_array must be an array like this: =MATCH(“b”,{“a”,”b”,”c”},0) or an array reference like MATCH(7,A1:A15,0).
Match_type: is the number -1, 0, or 1 as seen here: =MATCH(B2,A1:A5,0). Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

=INDEX($A$2:$A$9,MATCH("Italy",$B$2:$B$9,0)) = “Tomatoes”, meaning:

• *        INDEX will the range of the result column in the example I want to get the “Product”
• *        MATCH is what you want to look up, in the example is the “Location”, you also need to define the range of the MATCH, the “0” represents EXACT MATCH, so it gives you exactly what you want.

There are loads more of INDEX and MATCH possibilities, I will come back to this subject in the near future. 

Convert to Number

Many times you are faced with numeric values but the format is "Text or General", although you change the format with the usual "Format Cells" sometimes the format is not changing so the VBA below will help you.

If you are extracting the integer with a formula i.e. in column A you have 
vdf01 and you enter formula - RIGHT(vdf01,2) the result will be '01' and will be formatted as "Text or General" you won't be able to get it as a Number unless you delete the "0", but if you write the formula as VALUE(RIGHT(vdf01,2)) the result will be '1' formatted as "General" but now you can change it to "Number" :-) 


Copy the code to the VBA application within excel, return to your Excel book and highlight the column (range) that you want to convert and run the macro.

Sub ConvertNumber()
'We turn off calculation and screenupdating to speed up the macro.
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    For Each xCell In Selection
        xCell.Value = xCell.Value
    Next xCell
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

Bounce back with any questions,

Document Properties

Sometimes there is a need/requirement to get the Document properties in particular the "Last Saved Date", "Last Saved Time", "Last Saved By", "Author", etc...

There are various codes that can be used, however I feel that using the code below is a lot easier as only needs to be written once:

Function DocProps(prop As String)
     '-----------------------------------------------------------------
    Application.Volatile
    On Error GoTo err_value
    DocProps = ActiveWorkbook.BuiltinDocumentProperties _
    (prop)
    Exit Function
err_value:
    DocProps = CVErr(xlErrValue)
End Function

Off course that we do need now to indentify the cells with what is required from the code so in your book and in the desired cell just enter the following:

• Last Saved Date =DocProps("Last Save Time") format as dd/mm/yy
• Last Saved Time =DocProps("Last Save Time") format as hh:mm:ss
• Last Saved By =DocProps("Last Author") format as General
• Author =DocProps("Author") format as General