Quick Bytes IT Stuff

Excel provides a function to check blank cells and also provide functions to check cells that are not empty. CountA function will check cells that are not empty. Countblank function as its name literally implies will check or count for blank or empty cells. Example for Countblank function: =COUNTBLANK(A1:B11) --- Function will check for empty spaces from A1 to B11 (2 columns) =COUNTBLANK((A1:A11)) --- Function will check for empty spaces in a single column. Note the double parentheses Example for CountA function: =COUNTA(A1:A11) --- Function to check cells with values in a single column (Column A only) =COUNTA(A1:B11)   --- Functio to check cells with values in two columns from A1 to B11 (Column A and B) To enter formula above using VBA: ============================== Sub VBA_Formula() Dim Cell_Formula As String Dim xvalue As String Cell_Formula = "=COUNTA(A1:A11)" 'Cell_Formula = "=COUNTBLANK((A1:A...

How to use datedif function in Excel? How to find date difference between two dates in Excel? How to find date difference by month, year or days in Excel? Excel has a built-in function and does the heavy lifting to date difference between two dates. Excel uses the “datedif” function to easily compute the date difference  by days, months or years. Notice the Datedif function has a single “f” at the end it’s not DateDiff. If you accidentally type double “f” at the end of the function excel will return an error and will just display “#NAME?” which basically means that the function is not recognized by Excel. Here’s the function: =DATEDIF(A1,C1,"d")  ==== > this function find the date difference by days for the values of A1 and C1 =DATEDIF(A3,C3,"m")  ==== > this function find the date difference by months for the values of A3 and C3 =DATEDIF(A5,C5,"y")  ==== > this function find the date difference by yea...

To check excel workbook path or location via vba, the code below will get the path or location of the workbook. Application.ActiveWorkbook.Path     ===  Path only without the workbook name Application.ActiveWorkbook.FullName === Display path and the workbook name Sample code: Sub Display_WB_Location() Dim xPath_Workbook xPath_Workbook = Application.ActiveWorkbook.FullName 'xPath_Workbook = Application.ActiveWorkbook.Path Msgbox xPath_Workbook End Sub If the file is open, just click on "File" then click "Info" to show workbook location. See image below: Sometimes it's useful to know the location, especially if you're working on a lot of folders and files. And forget which folder or the file location. Cheers!!! Hope it helps!!!

Printing an Excel worksheet, can be adjusted manually. In Excel 2010 and other versions of excel, printing can be adjusted by zoom in and zoom out. In Excel 2010, to adjust page setup: Click on "File". Click on "Print". Then the print preview is shown on the screen. Under the "Custom Scaling" option, click on "Page Setup". Click on "Adjust to" option and set the desired percentage. See image below:   To do this via VBA or macro, it's quite straight forward. See code below, just specify the "zoom" keyword and the value. ========================= Sub VBA_PageSetup_Option() With Worksheets("Sheet1")  .PageSetup.Orientation = xlLandscape  .PageSetup.Zoom = 280 '< - - - set the desired percentage  '.PrintPreview   .PrintOut End With End Sub  ========================= Code VBA above will zoom or adjust the whole worksheet. If need to choose only a specific print area, then u...

Disable auto insert of decimal point in excel 2010. When typing in excel, the number typed on the worksheet automatically changed to decimal point. This could happen, if the auto insert of decimal point is enabled. To disable follow settings below: Go to the ribbon, click on File. Select "options". On the excel options window, click on 'Advance'. Uncheck "Automatically insert a decimal point". See image below: Cheers!! Hope it helps...

If you are an SQL junkie then using SQL in Excel VBA would be a good choice. If just starting to embark on the SQL world, then it would be a fun also to use SQL statement in Excel VBA. To start SQL queries in Excel VBA, it will need to reference to "Microsoft Activex Data Object 6.1 Library". If Microsoft Activex Data Object 6.1 Library, is not found on the system. Please refer to this Microsoft link: https://support2.microsoft.com/kb/2640696?wa=wsignin1.0 To add reference using Excel VBA, press "Alt + F11" to open Excel VBA Project Window. Click on "Tools" and click "References", then browse for Microsoft Activex Data Object 6.1 Library and click ok. Or check out link below for the screen shot on how to add References in Excel VBA. This link has screen shots on how to add references. http://quickbytesstuff.blogspot.sg/2014/09/excel-vba-speak-method.html Once the Activex Library is added, then it's time to code and have ...

How to get Word table value or data from Excel using VBA? Sharing or getting data from word table to Excel via VBA. Below is a piece of VBA code that will do the task to get a value from Word using Excel VBA. This example assumes that there is a single table in Microsoft Word. And the table is 3x8, 3 columns and 8 rows per column. So just for reference, for those who are trying to wrap up on what is row and column. Please check the image below, Value1, Value2 and Value3 represent columns. (3 columns on this example) And each Value column has 8 lines, which is basically called rows. (8 rows on this example) VBA code below will get the data from a word file and display the value using an msgbox command in Excel VBA. It will also place the value to the Excel Worksheet where the VBA is being executed. Code below assumes that there is a worksheet called "Sheet1". If a worksheet named "Sheet1" is not found on the wo...

Excel 2010 does not provide a native function to insert watermark unlike in Word. Word by default has a function on the Ribbon tab to insert a watermark. In Excel watermark can be done also using this method below. On the Ribbon tab click on “Insert”, then click on “Header & Footer”. On the small rectangle that will pop up after clicking “Header & Footer” type:     &[Picture] Type exactly as it shown “&[Picture]”,  when done typing click outside the small rectangle. Then an insert dialog box will appear choose from your PC any pictures you want as a watermark background. Please images below on how to insert a watermark background in “Excel 2010”. After selecting a picture to insert then the final result would be something like this: Image inserted as watermark: Screen shot of excel with the watermark background: Cheers.. hope it helps!!

Microsoft has provided an Speech Object Library that can easily be referenced using   Excel VBA. To add a reference to Microsoft Speech Object Library on Excel. Open the Developer tab or press "ALT + F11". Or check out this link to open the developer tab: http://quickbytesstuff.blogspot.sg/2014/03/how-to-view-excel-macros.html On the VBA window, click on " Tools " and click on " References ". And add the "Microsoft Office Speech Object Library". See image below on how to add references in excel. If Microsoft Office Speech Object Library is not added, VBA will throw a Compile error, "User-defined type not defined". Once the reference has been added, create a new macro. Click on the modules folder, on the VBA project window and add the code below: Uncomment the line to test different voices. ==================================== Option Explicit Private V As SpeechLib.SpVoice ...