## New idea for software ecosystem research

Analyse Python ecosystem according to following DOI: 10.1016/j.jss.2017.06.095

## Displaying HTML in Excel cell

Recently I was tasked with coming up with an idea of how to display HTML tags inside an Excel sheet which were in the rows of 1 column. It had to be done automatically with a click of a button (i.e. conversion of each HTML cell into properly formatted cell that displays HTML content with applied styling) and of course VBA had to play a role in it. (Full disclosure: Idea borrowed from here)

Well, after a lot of trials & errors, here is the general idea & code: For each cell in the column we first open Internet Explorer and navigate to “about:blank” (blank page). Then, we ‘copy’ the cell’s content to such blank page and ‘select all & copy’ the content into the clipboard. From there, we read it again and paste it into the same cell from which we have first copied it.

It might sound fairly complicated because……it is. Plus, one clear disadvantage is that if, during time the macro is executed, anything is copied to clipboard by the user, it will 1000% lead to inconsistent results. And this is not even to talk about speed.

Warning: The code below can take up to 4 minutes with 250 rows to execute. This is intentional because if there is no “Wait()” then for some reason it again leads to inconsistent results (e.g. where the same row will be copied up to 4 times -> 4 rows with same HTML content)

Before you will be able to execute this VBA macro, read this website on how to load “Microsoft Forms 2.0 Object Library”.

 Sub DisplayHTMLContentProperly() Dim rng As Range Dim row As Range Dim cell As Range Dim Ie As Object Set Ie = CreateObject("InternetExplorer.Application") Ie.Visible = True 'to be tested Dim DataObj As MSForms.DataObject ' for clipboard Set DataObj = New MSForms.DataObject

 Dim sht As Worksheet Dim LastRow As Long Set sht = ThisWorkbook.Worksheets("SheetName") LastRow = sht.ListObjects("DataName").Range.Rows.Count Set rng = Range("L2:L" & LastRow) For Each row In rng.Rows For Each cell In row.Cells Application.CutCopyMode = False DataObj.GetFromClipboard myString = " " DataObj.SetText " " If Not IsEmpty(cell) Then With Ie .Visible = True .Navigate "about:blank" While .Busy Or .ReadyState 4: DoEvents: Wend .Document.body.InnerHTML = cell 'update to the cell that contains HTML you want converted .ExecWB 17, 0 'Select all contents in browser .ExecWB 12, 2 'Copy them 'get data from clipboard (due to copy method above) and paste it into the cell DataObj.GetFromClipboard myString = DataObj.GetText(1) 'MsgBox myString - to debug cell = myString 'delete anything from the clipboard DataObj.Clear Application.CutCopyMode = False DataObj.SetText " " End With End If 'Do Something Set HTML = Nothing Application.Wait (Now + 0.000000011574 * 1200) Next cell Next row Ie.Quit Set Ie = Nothing Application.CutCopyMode = False MsgBox "I am now done with proper formatting of HTML column." 'move to see just summary page Sheets("Summary").Activate 

End Sub 

## SAS code for replacing missing values with 0 in a folder full of datasets

%macro loopOverDatasets();
/*imho good practice to declare macro variables of a macro locally*/
%local datasetCount datasetName iter inMember;

/*get number of datasets + name of datasets*/
proc sql noprint ;
select count(*)
into: datasetCount
from dictionary.tables
where libname = “EXAM”;
quit;

/*initiate loop*/
%let iter=1;
%do %while (&iter.<= &datasetCount.);

proc sql noprint;
select memname
into: datasetName
from dictionary.tables
where libname = “EXAM” and monotonic() eq &iter.;
quit;

%put &iter &datasetCount &datasetName &inMember;
/*now you can apply your logic to the dataset*/
data &datasetName.;
set exam.&datasetName.;
array change _numeric_;
do over change;
if change=. then change=0;
end;
run;

%let iter=%eval(&iter.+1);
%end;
%mend;

%loopOverDatasets;