Overview of VBA Modules, worksheet, userforms, UDFs Modules are essentially where the VBA code (or script) is stored and edited. The VB Editor...
This quick script will scan through the worksheet(“X”) and if there is a coloured cell on the current row, it will put a...|
These 2 UDF’s will extract the first and last words using a formula in your worksheet. It is worth noting that during a...|
FE_Name = “Example” Sheets(“Sheet2”).Select Range(“A1:I47”).Select Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ ThisWorkbook.Path & “\” & FE_Name & “.pdf”, Quality _ :=PDFQuality, IncludeDocProperties:=True, IgnorePrintAreas:=False _ ,...
This quick piece of code will run down the current sheet and if the value in the cell is NOT a number or...|
Identifying whether a cell is coloured by Conditional formatting. This code will only work with Excel 2010 onwards and by choosing a cell...|
Application.ScreenUpdating = False Worksheets("Data").Select Range("A2:Z25000").ClearContents With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & GetFile, Destination:=Range( _ "$A$1")) .Name = "lget_csv" .FieldNames = True .RowNumbers = False...|
This UDF opens up the dialog box to choose a file to import into an Excel woorkbook. Function GetFile() As String Dim filename__path...|
This is a newer and more efficient way of creating a PDF from a specific range on a worksheet, saving it and emailing...|
Removing duplicates on a Mac is slightly different to on a PC version of Excel. This is more brute force than elegant and...|
Easily identify the iso weeknumber with a formula. Paste this UDF into a module in the VB Editor (ALT+F11) then use the following...|
A formula to return the column letter(s) when placed in any cell. For instance if the formula was put in cell G56 the...|
Occasionally you may want to know what the date was last Friday, or any day last week for that matter. This example, used...
To generate a ‘Random’ set of numbers in Excel you just need to use the Randbetween formula as follows: =Randbetween(1,5000) This will put...|
Occasionally when you have a large number of cells in a row that you want to sum, ideally it would just be in...|
Creating a QR Code or a large (Millions possible) in Excel is straightforward using this VBA example: ActiveSheet.Range("E9:E11").Select For Each qr In Selection...|
Creating a PDF directly from an Excel worksheet is simple with the following VBA Example: FName = "Datanology Dashboard Example" Sheets("PDF").Select Range("A1:BP89").Select Selection.ExportAsFixedFormat...|
Once you’ve drawn your shape and added text if applicable, or added an image that you want a hyperlink assigned to you can...|
This code should be added within a module to assign the first day of the month to the variable: fst_crnt_mnth act_dte = Now()...|
Copy a text file into a backup folder, delete the original, and add todays date and time to the backup filename. Dim FileUser...|
This example sends a large amount of data from a row into a text file in the same path as the excel file...|
'Recreate each worksheet as values instead of formulas and scroll to top / A1 For Each ws In Worksheets ws.Activate ws.Cells.Select Selection.Copy Selection.PasteSpecial...|
=NOW()-DAY(NOW())+1 This simple formula when put in any cell will automatically create the value in that cell as the first day of the...
This quick script will go down column A of a tab named ‘Data’ and for each unique value it finds, it will create...|
'This code looks down Sheet1 column A and considers the number of csv strings in column B and Column D 'Then puts that...
With ActiveSheet lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row End With OTP = 2 For n = 1 To lastrow srce = ActiveSheet.Range("A" & n) For...|
LastColumn = activesheet.Cells(1, activesheet.Columns.Count).End(xlToLeft).Column This will find the last used column on row 1 of the current sheet. It will return the number...
=COUNTIF(A:A,"Jan")+COUNTIF(A:A,"Feb")+COUNTIF(A:A,"Mar")+COUNTIF(A:A,"Apr")+COUNTIF(A:A,"May")+COUNTIF(A:A,"Jun")+COUNTIF(A:A,"Jul")+COUNTIF(A:A,"Aug")+COUNTIF(A:A,"FSep")+COUNTIF(A:A,"Oct")+COUNTIF(A:A,"Nov")+COUNTIF(A:A,"Dec") This formula counts all the occurrences of a month in the list when using multiple years and you need to know how...|
'HTML STUFF*********************************************** a = " ~!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.01 Transitional//EN"" ""http://www.w3.org/TR/html4/loose.dtd"">" b = "~!-- Original design work, including graphics and all...|
The project brief was to create a macro on a button that would follow this process: Open dialog box so user can choose...|
Sub Add_matrix_to_Richards_Specification_sheets 'Adding the Specification Compliance scoring system for Richard 'Each sheet ending in Specification Compliance needs this applied 'but may be different...
Border all around a cell Range(“K5”).Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0...|
Zest Print Prices Sub Button1_Click() 'Get lastrow of the Template form With Worksheets("Template") lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row End With With Worksheets("££ Print Prices...
Application.ScreenUpdating = False ActiveSheet.Cells.Interior.Color = xlNone x = 4 Z = 3 For n = 1 To 2 Sheets("TPS_IMPORT").Select With ActiveSheet.QueryTables.Add(Connection:="TEXT;F:\" & n...
This code will disable the function of copy, cut and paste in an Excel workbook, rendering the file very difficult to remove data...|
Logic will be to make them all disappear so create a Call All_Gone sub routine. Sub All_Gone Label46.visible = false Label57.visible = false...
There is no excerpt because this is a protected post.
There is no excerpt because this is a protected post.|
There is no excerpt because this is a protected post.|
This series of blogs created by datanology are updated as and when new features, problems & solutions or general tips regarding a particular...|
Userforms in Excel can give a professional look and feel to any Excel project, database or data entry system. Several of our recent...|
http://dev.emcelettronica.com/serial-port-communication-in-excel-vba Option Explicit '------------------------------------------------------------------------------- ' ' This VB module is a collection of routines to perform serial port I/O without ' using the...
There is no excerpt because this is a protected post.
We have recently completed several projects where our client has a large customer database, names and email addresses in one list, and in...|
A recent project brief managed by datanology related to the agreed marketing data of a companies client list. In order to create a...|
Listindex does not work on Mac VBA in Excel. It’s a shame because it can be a very powerful function when dealing with...|
Sub loop_sheets_and_copy_used_range() Dim WS_Count As Integer Dim I As Integer With Worksheets("Main") lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row End With ' Set WS_Count equal to...|
One way of identifying the first and last names in a cell is to use text to columns function and delimit the cells...|
If you have a dataset in Excel or has perhaps been copied over from Word where multiple lines appear in a single Excel...|
There may be an instance where you have potentially hundreds or even thousands of worksheets in a workbook, or have collated a large...|
There are many different ways to populate the data in a combobox in a Userform – using lists on a lookup tab, defining...|
Datanology are providers of offline, credit based UK postcode lookups to enable users to validate customer address data and clean their datasets in...|
Having researched many of the Excel help sites, we have not been able to find a solution for splitting out text in the...|
Attempting to create part of a string in a Userform textbox proved not yet possible in Excel up to version 2016, so an...|
On the exported spreadsheets, the formula needs to be reinstated, and the rest copied and pasted as values. VAT is still feeding through...
This code was used on a project recently to allow data from a worksheet to be output to a csv file with the...|
In a recent project undertaken by Datanology, a workbook had become large enough to be cumbersome in opening, saving, closing and general navigation....|
If you have a large list or dataset and need to keep particular rows of data rather than select those you don’t want...|
Merged cells in Excel are known to cause inaccuracy issues with lookups, matching etc and can be a problem area for users analysing...|
Quite a straightforward requirement, but none-the-less if your dataset is quite large, repetitive pasting of values into multiple cells in a column can...|
When cleaning data or manipulating a dataset in order to upload into an e-commerce website for instance, the data must be absolutely spot...|
Application.ScreenUpdating = False Worksheets("Sheet1").Cells.ClearContents Dim qry As QueryTable Dim FilNams As Variant Dim FilNamCntr As Long Dim strQryName As String Dim LastRow As...|
This code example will present you with a dialog box where you can choose which Excel (.xls or .xlsx) workbook you want to...|
Connecting to a COM port gives your Excel project functionality for external devices such as Barcode readers, key button readers such as point...|
Datanology’s Data Mining projects, extracting the useful and valuable data from large sets of data uses this kind of process regularly, in conjunction...|
Camel Text (CamelText) is best described as text where words that should be separated normally by a space have been pushed together and...|
Userforms can be very useful and powerful when it comes to creating Data entry forms or Databases in Excel. One of the most...|
This function is possibly something that should be built into Excel as standard, but even as Excel 2016 has just been released it’s...|
Comments in Excel can be stored in a cell and their locations are identified by a small red triangle in the top right...|
Address and Location analysis often requires the identification of the value in a cell starting with a number (ie house number) or not...|
The previous blog entry relates to the same project, but this refers to the creation of the files as XLS files as opposed...|
Datanology recently completed a project where the clients Excel workbook was used to auto create and store invoices as a new tab in...|
As part of Datanology’s recent projects, our UK Postcode analysis can validate any given UK Postcode by it’s structure, split the UK Postcode...|
This database created by Datanology contains the reference data to lookup the town/city related to the OUTER Postcode in the UK. To use...|
Excel ‘Tables’ are a good example of clearly laid out and visible data from a database in Excel. However there may be situations...|
Occasions when a persons first name and surname appear in the same cell in Excel may call for a need to ‘split’ the...|
This recent project assigned to datanology’s VBA team required a regular import of a standard csv file that was created from a web...|
This may seem illogical, and we even asked ourselves why anybody would want to do this, when the whole point of a clean...|
Charts in Excel have become more and more functional and powerful since Excel began and now with Excel 2016 it seems incredible how...|
There are a number of methods aside from ‘Right clicking’ a tab and choosing move or copy in order to copy a worksheet...|
We will need to know where the data we want to search is held, in this example it’s in the tab named ‘OrderFormData’...|
We have been faced with projects / situations where multiple pages have become clogged up with numerous and usually superfluous or duplicated items...|
When you have designed a userform with multiple controls such as textboxes, the user will probably want to navigate through in a logical...|
There are many different ways of describing ‘Data Mining’ and probably lots of different understanding of what it means. At the time of...|
When it comes to compatibility between Mac and Windows Userforms in Excel (as long as your version of Mac Excel is 2011 or...|
In location analysis for Road Traffic Accidents for instance, or even just general mapping the Geocodes for less specific road areas, ie those...|
This collection of VBA scripts has been created throughout the projects created by Datanology. Whilst we re-use and find further uses for them,...|
Deleting files on your drive with VBA is relatively easy. The ‘kill’ command is used but be careful, as the file is not...|
Finding yourself in a position to clean up a worksheet is quite common, and Excel users will sometimes want to remove rows of...|
Splitting data can be a cumbersome and highly manual task, and could result in inaccurate data. Splitting data, moving it about and intelligently...|
When you are dealing with worksheets with a user-function – ie a user needs to complete a questionnaire or needs to add details...|
Power sellers on Amazon and ebay will especially find this VBA solution useful to help create the multiple descriptions required to mix and...|
There may be many situations in your VBA project when you might need to open up the dialog box and choose a file...|
Sending an email using VBA. It is imperative that you are using Excel 2010 or newer on a Windows PC, and also use...|
Datanology are delighted to support CHCC Squash teams for the 2015/16 season – Good luck ! “Data can help us make smarter decisions...|
Before deciding to create a PDF from your worksheet and emailing it, it should be noted that this code is specifically for Windows...|
Excel can be used for many tasks outside it’s mainstream use as a database, calculations, data manipulation etc. It can also be used...|