{ From user Dave, Model COM_with_Excel_workb at 7-Apr-2015 6:46:30 PM, encoding="ascii" }
SoftwareVersion 4.6.0


{ System Variables with non-default values: }
TypeChecking := 1
Checking := 1
SaveOptions := 2
SaveValues := 0




Model COM_with_Excel_workb
Title: COM with Excel workbooks
Description: An example model demonstrating some ways to use  Analytica's COM Integration functions with Excel workbooks.~
~
~
<a href="http://wiki.lumina.com/index.php/COM_Integration">Wiki help on Com Integration</a>~
~
~
<a href="https://msdn.microsoft.com/en-us/library/office/ff194565.aspx">Microsoft Development center reference for the Excel Application Object</a>
Author: Dave
Date: Wed, Mar 11, 2015 3:22 PM
SaveAuthor: Dave
SaveDate: Tue, Apr 7, 2015 6:46 PM
DiagState: 2,10,11,756,328,17
FontStyle: Arial,15
FileInfo: 0,Model COM_with_Excel_workb,2,2,0,0,C:\Users\Dave\Desktop\rep6\Work new repo\comexc\COM integration with Excel.ana

Module Add_a_worksheet_to_a
Title: Add a worksheet to an Excel workbook
Description: This example illustrates using Analytica's COM functions to add a worksheet to an existing Excel workbook. In this example we  use a COM object called by Analytica's built in SpreadsheetOpen(...) function.
Author: Dave
Date: Wed, Mar 11, 2015 7:39 PM
NodeLocation: 648,72,1
NodeSize: 64,40
DiagState: 2,100,100,590,263,17
WindState: 2,267,228,552,375

Variable Workbook
Title: Workbook
Description: When this variable is evaluated you will be prompted to select a spreadsheet file, so just choose any Excel workbook you have laying around.~
~
This variable calls Analytica's  SpreadsheetOpen(...) function which  returns a Workbook COM object.  This object can be used with COM functions as well as other built-in spreadsheet functions.~
~
<a href="https://msdn.microsoft.com/en-us/library/office/ff835568.aspx">MSDN reference for the Excel Workbook Object</a>
Definition: spreadsheetopen("", showDialog: true)
NodeLocation: 68,112,1
NodeSize: 56,24
WindState: 2,112,58,518,422

Variable worksheets
Title: Worksheets
Description: In order to add a new worksheet, first we need to get the 'Worksheets' COM object, which is a property of the Excel 'Workbook' COM object, and returns an collection of 'Worksheet' COM objects.~
~
The definition of this variable is:~
Workbook->Worksheets ~
~
which is the same as:~
ComGetProperty(Workbook,"Worksheets")~
~
but using the more concise  ' -> ' invoke method operator~
~
<a href="https://msdn.microsoft.com/EN-US/library/office/ff835542.aspx">MSDN reference for the Excel Worksheets object</a>~
~

Definition: Workbook->Worksheets
NodeLocation: 280,112,1
NodeSize: 56,24
NodeInfo: 1,1,1,1,1,1,0,0,0,0,
WindState: 2,706,31,533,365

Variable Add_a_worksheet
Title: Add a worksheet
Description: The Worksheets COM object represents a collection of 'Worksheet' COM objects, it has a method 'Add' which, as expected, adds a new worksheet to the workbook.~
~
The Add method returns the Worksheet com object which you can you use later.  For example, you might want to rename the worksheet (I'm foreshadowing the next example.)~
~
The definition of this function is Worksheets->Add() ~
~
which is syntatic sugar for~
ComCallMethod(Worksheets, "Add")~
~
<a href="https://msdn.microsoft.com/EN-US/library/office/ff835542.aspx">MSDN reference for the Excel Worksheets object</a>
Definition: Worksheets->Add()
NodeLocation: 496,112,1
NodeSize: 56,24
WindState: 2,77,23,489,454

Text Te1426146266
Description: Example 1:  Adding a worksheet to a workbook
NodeLocation: 282,46,-1
NodeSize: 270,18
NodeInfo: 1,0,0,1,0,1,1,,0,,0
WindState: 2,102,83,476,224
NodeFont: Arial,24

Close Add_a_worksheet_to_a

Text Te1426146016
Description: This example illustrates using Analytica's COM functions with Excel workbooks. ~
~
You generally want to use Analytica's built-in functions for working with spreadsheets e.g. SpreadSheetOpen(...), SpreadSheetCell(...), SpreadSheetSetCell(...), SpreadSheetSave(...) etc.~
~
But what if you want to do something that is currently not possible via these built-in spreadsheet functions, like adding a new worksheet to a workbook.  This example will show you how to do this.~
~
• The individual nodes' description attribute explain the examples. ~
• You can find documentation for the Excel Application COM object here:~
~
<a href="https://msdn.microsoft.com/en-us/library/office/ff194565.aspx">MSDN reference for the Excel Application Object</a>~

NodeLocation: 288,152,0
NodeSize: 264,136
NodeInfo: 1,0,0,1,0,1,0,0,0,,0
WindState: 2,0,0,1455,406

Module Renaming_a_worksheet
Title: Renaming a worksheet
Description: The example in this module demonstrates renaming a worksheet within an existing Excel Workbook.using Analyticas's COM functions.~
~
<a href="https://msdn.microsoft.com/en-us/library/office/ff835568.aspx">MSDN reference for the Excel Workbook Object</a>
Author: Dave
Date: Thu, Mar 12, 2015 4:20 PM
NodeLocation: 648,160,1
NodeSize: 64,32
DiagState: 2,135,146,602,292,17
WindState: 2,779,246,476,224

Text Te1426146583
Description: Example 2:  Renaming an Excel worksheet.
NodeLocation: 288,32,-1
NodeSize: 280,16
NodeInfo: 1,0,0,1,0,1,1,,0,,0
WindState: 2,102,83,476,224
NodeFont: Arial,24

Variable Workbook2
Title: Workbook2
Description: When this variable is evaluated you will be prompted to select a spreadsheet file, so select any Excel workbook to use.~
~
This variable calls Analytica's SpreadsheetOpen() function which returns a Workbook COM object. This object can be used with COM functions as well as other built-in spreadsheet functions.~
~
<a href="https://msdn.microsoft.com/en-us/library/office/ff835568.aspx">MSDN reference for the Excel Workbook Object</a>
Definition: SpreadsheetOpen("",showDialog:True)
NodeLocation: 64,104,1
NodeSize: 56,24
WindState: 2,102,83,476,288

Variable Rename_first_Sheet
Title: Rename first Sheet
Description: Once we have the COM object for the worksheet that we want to name, we can set the 'Name' property of the worksheet. We'll rename it with today's date and time.~
~
The definition of this node is:~
First_Worksheet->Name := Date_and_time~
~
Which is the same as COMPutProperty(First_Worksheet,"Name", Date_and_time)~
~
<a href="https://msdn.microsoft.com/en-us/library/office/ff194464.aspx">MSDN reference for the Worksheet Object</a>
Definition: First_Worksheet->Name := Date_and_time
NodeLocation: 512,104,1
NodeSize: 56,24
WindState: 2,688,171,544,483
ValueState: 2,248,251,416,303,0,MIDM

Variable Worksheets2
Title: Worksheets2
Description: In order to add a new worksheet, first we need to get the 'Worksheets' COM object, which is a property of the Excel 'Workbook' COM object, and returns an collection of 'Worksheet' COM objects.~
~
The definition of this object is:~
Workbook2->Worksheets~
~
We could also have written this definition as:~
COMGetProperty(Workbook2, "Worksheets")~
~
<a href="https://msdn.microsoft.com/EN-US/library/office/ff821537.aspx">MSDN reference for the Worksheets Object</a>
Definition: Workbook2->Worksheets
NodeLocation: 214,104,1
NodeSize: 56,24
WindState: 2,124,283,476,224

Variable First_Worksheet
Title: First Worksheet.
Description: Let's get the first Worksheet of the workbook so that we can rename it. To do that we need to get it's COM object. ~
~
The Worksheets object is a collection of 'Worksheet' objects, so we need to index this collection to pull the Worksheet that we want.~
~
The 'Workheets' object has a property 'item' with a single required parameter (index). Here we use the index number - 1 - to get the first Worksheet.~
~
The definition of this variable is:~
Worksheets2 -> item(1)~
~
We could have written this definition as ~
COMGetProperty(Worksheets2, "item", 1)~
~
<a href="https://msdn.microsoft.com/en-us/library/office/ff821537.aspx">MSDN reference for the Worksheets Object</a>
Definition: Worksheets2 -> item(1)
NodeLocation: 364,104,1
NodeSize: 56,24
WindState: 2,486,76,476,420

Variable First_Worksheet__Met
Title: First Worksheet: Method 2
Description: The Worksheets COM Object is a collection of Worksheet Objects, which accepts an object qualifier, so in this case we can also get the first Worksheet's COM Object by passing (1) directly to the 'Worksheets' object, without calling the 'Item' Property.~
~
Then we could use this object the same as we did 'First_Worksheet'.~
~
<a href="https://msdn.microsoft.com/EN-US/library/office/ff821537.aspx">MSDN reference for the Worksheets Object</a>
Definition: Workbook2 -> Worksheets(1)
NodeLocation: 216,208,1
NodeSize: 56,32
WindState: 2,444,147,588,511

Variable Date_and_time
Title: Date and time
Description: Today's date and time.
Definition: TextReplace( Today(True),":","-",All:True)
NodeLocation: 512,216,1
NodeSize: 56,24
NodeInfo: 1,1,0,1,1,1,0,,0,,0
WindState: 2,239,447,476,224
NumberFormat: 3,D,4,2,0,0,4,0,$,0,"d-MMM-yyyy h:mm:ss tt",0,"DATETIME",,0,0

Variable Spreadsheet_Save
Title: Spreadsheet Save
Description: You need to save the worksheet if you want to view it in Excel.
Definition: SpreadsheetSave(Workbook2)
NodeLocation: 64,216,1
NodeSize: 56,24
WindState: 2,397,135,476,224
ValueState: 2,184,181,694,303,0,MIDM

Close Renaming_a_worksheet

Module Creating_a_new_Workb
Title: Creating a new Workbook
Description: This example illustrates using Analytica's COM functionality to create a new Excel Workbook, using the Excel.Application COM object, called by Analytica's COMCreateObject(...) function. ~
~
Once the Workbook is created then you can use the built in Analytica spreadsheet functions with the 'ExcelWorkbook' COM object created.~
~
We add a range with a Colored border around it to the active worksheet, and set it's cells to a different interior color.
Author: Dave
Date: Thu, Mar 12, 2015 8:32 PM
NodeLocation: 648,240,1
NodeSize: 64,32
DiagState: 2,17,10,1016,628,17
WindState: 2,610,62,607,399

Text Te1426161008
Description: Example 3:  Create a new Excel workbook. Add a range with a border around it and with colored cell interiors.
NodeLocation: 368,44,-1
NodeSize: 328,28
NodeInfo: 1,0,0,1,0,1,1,,0,,0
WindState: 2,102,83,476,224
NodeFont: Arial,24

Variable Excel_Application
Title: Excel Application
Description: The Excel.Application COM object Represents the entire Microsoft Excel application.~
~
~
<a href="https://msdn.microsoft.com/EN-US/library/office/ff194565.aspx">MSDN reference for the Excel.Application Object</a>~

Definition: COMCreateObject("Excel.Application")
NodeLocation: 96,112,1
NodeSize: 56,24
WindState: 2,102,83,476,224

Variable Open_Excel_Workbooks
Title: Open Excel Workbooks
Description: The Excel.Application COM Object has a 'Workbooks' Property which returns a collection of all the open workbooks.~
~
The definition of this variable is 'Excel_Application->Workbooks'~
~
We could also have written:~
COMGetProperty(Excel_Application,"Workbooks")~
~
<a href="https://msdn.microsoft.com/EN-US/library/office/ff820765.aspx">MSDN reference for the Workbooks Property</a>
Definition: Excel_Application->Workbooks
NodeLocation: 96,200,1
NodeSize: 56,24
WindState: 2,93,245,476,450

Variable Add_a_workbook
Title: Add a workbook
Description: The 'Workbooks' object contains a method 'Add' which adds a new workbook to the Workbooks collection. The workbook added becomes the active workbook.~
~
If you evaluate this variable, it returns an 'ExcelWorkbook', which can be used by Analytica's built in spreadsheet functions. ~
~
The definition of this variable is:~
Open_Excel_Workbooks->Add~
~
Which is the same as:~
COMCallMethod(Open_Excel_Workbooks,"Add")~
~
<a href="https://msdn.microsoft.com/EN-US/library/office/ff841074.aspx">MSDN reference for the Workbooks Object</a>
Definition: Open_Excel_Workbooks -> Add
NodeLocation: 96,288,1
NodeSize: 56,24
WindState: 2,152,225,476,441
ValueState: 2,40,50,400,300,0,MIDM

Variable SpreadsheetSetRange_
Title: SpreadsheetSetRange into the new workbook
Description: Shows how we can use the Excel Workbook generated by COMCreateObject(...) with the Analytica built in functions SpreadsheetSetRange(...), and SpreadsheetInfo(...).~
~

Definition: {Add some data to the active sheet of the new workbook,~
using SpreadsheetSetRange. 'Value' parameter is an Analytica variable, and the 'Sheet' parameter is fetched from the new workbook using SpreadSheetInfo(...) with the  'ActiveSheet' parameter.}~
~
SpreadsheetSetRange(Add_a_workbook, range:'b2:f6', value:Some_data, colIndex:Vertical, rowIndex:Horizontal, sheet:SpreadsheetInfo(Add_a_workbook,item:'ActiveSheet'))
NodeLocation: 272,288,1
NodeSize: 96,32
WindState: 2,120,123,582,224
ValueState: 2,136,139,416,303,0,MIDM
Att__TotalsIndex: []

Variable SpreadsheetSave1
Title: SpreadsheetSave
Description: Save the newly created Worksheet, in this case since we provided no file name it will use the Excel default name and will save to your default documents folder.
Definition: SpreadsheetSave(Add_a_workbook)
NodeLocation: 456,288,1
NodeSize: 56,32
WindState: 2,532,206,476,224
ValueState: 2,40,50,400,300,0,MIDM

Module Internal
Title: Internal
Description: Generate something to add to a spreadsheet.
Author: Dave
Date: Thu, Mar 12, 2015 9:42 PM
NodeLocation: 96,560,1
NodeSize: 48,24
NodeInfo: 1,0,0,1,1,1,0,,0,,0
DiagState: 2,113,244,960,520,17
WindState: 2,212,202,476,224

Variable Some_data
Title: Some data
Definition: LogTen(Horizontal*Vertical)
NodeLocation: 96,48,1
NodeSize: 48,24
WindState: 2,102,83,476,224
ValueState: 2,305,195,416,303,0,MIDM

Index Horizontal
Title: Horizontal
Definition: [1,2,3,4,5]
NodeLocation: 216,48,1
NodeSize: 48,24

Index Vertical
Title: Vertical
Definition: [1,2,3,4,5]
NodeLocation: 216,120,1
NodeSize: 48,24

{!40404|FreePassObjectCount sl8DIPpPhDHRUZH6sMsFlwynw9pSGZntGndLPwSzcNTRlcKF8DZ6gjhXgxX$pHTqDvjimJn7mbh_I3rcVUuHryw5EgEzn2Kb_TNGK2U5kgkzH8sngl5eCQMTcrN5V9Ldo$rogIwR3JYnhsDrg5Vn$IUj8qjS0HZqCG2zC1PoTpAZyNoEh9e8eAiGqP$cEtWBsYFzjSD$naPE5yqjcXTQOMLMMORVafmt$9JUht5KZq5Oh$Kg1PnBc1TxOtNvRzX5gHuW9pUAsaI2oZL8yncTLD71yurponoqsw_39GOXhs1ESgv9Qi$IdzJg2SsHj9d6c6d9jHsR}
Library Base_conversion_lib
Title: Base Conversion Function Library
Description: A set of functions for converting between Binary, Octal, Decimal and Hexidecimal representations of numbers.  These functions are equivalent to the functions having the same names in Excel.
Author: Lonnie Chrisman, Ph.D.~
Lumina Decision Systems
Date: Tue, Mar 18, 2008 9:25 AM
SaveAuthor: Lonnie
SaveDate: Fri, Sep 7, 2012 2:08 PM
DefaultSize: 48,24
NodeLocation: 112,200,0
NodeSize: 64,40
NodeInfo: 1,1,1,1,1,1,0,0,0,0,
DiagState: 2,90,249,711,361,17
WindState: 2,102,90,476,224
FontStyle: Arial, 15
Att_PreLoadScript: {!40404|FreePassObjectCount sl8DIPpPhDHRUZH6sMsFlwynw9pSGZntGndLPwSzcNTRlcKF8DZ6gjhXgxX$pHTqDvjimJn7mbh_I3rcVUuHryw5EgEzn2Kb_TNGK2U5kgkzH8sngl5eCQMTcrN5V9Ldo$rogIwR3JYnhsDrg5Vn$IUj8qjS0HZqCG2zC1PoTpAZyNoEh9e8eAiGqP$cEtWBsYFzjSD$naPE5yqjcXTQOMLMMORVafmt$9JUht5KZq5Oh$Kg1PnBc1TxOtNvRzX5gHuW9pUAsaI2oZL8yncTLD71yurponoqsw_39GOXhs1ESgv9Qi$IdzJg2SsHj9d6c6d9jHsR}
{!40400|Att_clearTypeFonts: -1}

Function Bin2dec(x:atomic)
Title: Bin2Dec
Description: Converts a binary number to decimal.  X can be either text (with "0" and "1" characters only) or an integer which, when printed in base 10, contains only 0s and 1s.   If there are more than 10 binary digits, then the leftmost bit is the sign bit and the result in in twos complement.
Definition: index digits := Split(""&x,"");~
if digits<>"1" and digits<>"0" ~
    then error("Bin2Dec("&x&") contains digits other than 0 and 1");~
var placeValue := (if size(digits)>=10 and @digits=1 then -1 else 1) * 2^(size(digits)-@digits);~
sum(placeValue * (digits="1"), digits)
NodeLocation: 80,156,1
NodeSize: 48,24
WindState: 2,350,336,513,273
NumberFormat: 2,I,4,2,0,0,4,0,$,0,"ABBREV",0

Function Dec2bin(x:coerce atom number; places : optional positive atom=1)
Title: Dec2Bin
Description: Converts a decimal number to binary.  The result is a text string containing 0s and 1s.  ~
~
If x>255 then the result is preceded with a 0, so that it isn't confused with a 2s complement negative number.~
If x<0, then the first bit is 1 and a minimum of 10 digits is returned in 2's complement notation.  When -512<=x<0 then exactly 10 digits is returned.
Definition: var n:=if x<0 then 9 else places;~
var y := if x<0 then -x-1 else x;~
while 2^n-1<y do n:=n+1;~
if x>255  or x<0 then n:=n+1;~
~
index J := n-1..0;~
var placeValue := if x<0 and @J=1 then -2^J else 2^J;~
Join(~
    for i:=placeValue do (~
       if x>=i then (x:=x-i ; "1") else "0";~
    ), J )
NodeLocation: 292,40,1
NodeSize: 48,24
WindState: 2,408,446,476,490

Function Dec2hex(x : coerce scalar ; places : optional atomic positive=1)
Title: Dec2Hex
Description: Converts a decimal number to hexidecimal.  The result is text.~
~
When x is positive and places is not specified, uses the minimum number of digits to represent the number.  When x is negative, then it returns 10 character hexidecimal number where the most significant bit is the sign bit and the number is in two's complement notation.~

Definition: if x<-2^39 or x>=2^39 then NaN { out-of-range }~
else begin~
  if x<0 then x := x + 2^40;~
  {x is now positive }~
  index placeValue := 16^(9..0);~
  var full := Join(Slice(hexDigits,Mod(floor(x/placeValue),16)+1),placeValue);~
  var r := TextTrim(full, leftOnly:true, trimChars:"0");~
  if x>=0 and TextLength(r)<places then ~
     r := SelectText(full,max([1,TextLength(full)-places+1]));~
  r  ~
end
NodeLocation: 288,216,1
NodeSize: 48,24
WindState: 2,431,108,514,500

Function Hex2dec(hexNum : coerce atom text)
Title: Hex2Dec
Description: Converts a hexidecimal number (a text value) to a decimal value.  The result is numeric.  The hex number cannot have more than 10 digits.  If it has 10 digits, then the most significant bit is the sign bit and the number is assumed to be in 2's complement notation.
Definition: hexNum := TextUpperCase(hexNum);~
if TextTrim(hexNum,trimchars:"0123456789ABCDEF")<>"" ~
   Then Error("Non-hex digit to Hex2Dec: "&hexNum);~
if TextLength(hexNum)>10 ~
   then Error("A most 10 digits can be passed to Hex2Dec");~
~
index digits := split(hexNum,"");~
var placeValue := 16^(size(digits)-@digits);~
var digitValue := @[hexDigits=digits]-1;~
var r := sum(digitValue * placeValue,digits);~
if TextLength(hexNum)>=10 and r>=2^39 then r:=r-2^40;~
r
NodeLocation: 400,156,1
NodeSize: 48,24
WindState: 2,77,451,476,389

Function Dec2oct(x : coerce scalar ; places : optional atomic positive=1)
Title: Dec2Oct
Description: Converts a decimal number to an octal representation.  The result is text.~
~
When x is positive and places is not specified, uses the minimum number of digits to represent the number.  When x is negative, then it returns 10 character (30-bit) octal number where the most significant bit is the sign bit and the number is in two's complement notation.~

Definition: if x<-2^29 or x>=2^29 then NaN { out-of-range }~
else begin~
  if x<0 then x := x + 2^30;~
  {x is now positive }~
  index placeValue := 8^(9..0);~
  var full := Join(Mod(floor(x/placeValue),8),placeValue);~
  var r := TextTrim(full, leftOnly:true, trimChars:"0");~
  if x>=0 and TextLength(r)<places then ~
     r := SelectText(full,max([1,TextLength(full)-places+1]));~
  r  ~
end
NodeLocation: 292,98,1
NodeSize: 48,24
WindState: 2,431,108,514,500

Function Oct2dec(octal : atom)
Title: Oct2Dec
Description: Converts an octal number to a decimal value.  The result is numeric.  The octal number cannot have more than 10 digits (30-bits).  If it has 10 digits, then the most significant bit is the sign bit and the number is assumed to be in 2's complement notation.
Definition: octal := ""&octal;~
if TextTrim(octal,trimchars:"0123456789")<>"" ~
   Then Error("Parameter to Oct2Dec had a digit that was not between 0 and 7: "&octal);~
if TextLength(octal)>10 ~
   then Error("A most 10 digits can be passed to Oct2Dec");~
~
index digits := split(octal,"");~
var placeValue := 8^(size(digits)-@digits);~
var digitValue := @[hexDigits=digits]-1;~
var r := sum( digitValue * placeValue,digits);~
if TextLength(octal)>=10 and r>=2^29 then r:=r-2^30;~
r
NodeLocation: 186,156,1
NodeSize: 48,24
WindState: 2,77,451,707,389
NumberFormat: 2,I,4,2,0,0,4,0,$,0,"ABBREV",0

Function Hex2bin(x : atomic text ; places : optional positive atom=1)
Title: Hex2Bin
Description: Converts a hexidecimal number to binary.  The result is text.
Definition: x := TextUpperCase(x);~
index digits :=split(x,"");~
var binary := Join(hexToBin_Map[hexDigits=digits],digits);~
var isNeg := TextLength(binary)>=10 and @[hexDigits=SelectText(x,1,1)]>8;~
binary := TextTrim(binary,leftOnly:true,trimChars:if isNeg then "1" else "0");~
if isNeg then binary := "1" & binary;~
if isNeg then places := max([places,10]);~
if TextLength(binary)<places then (~
  binary := SelectText( if isNeg then "111111111111111111111111" else "0000000000000000000000000",~
                                    1, max([0,places-TextLength(binary)])) & binary~
);~
binary
NodeLocation: 400,40,1
NodeSize: 48,24
WindState: 2,231,400,685,359

Function Bin2hex(binNum : atomic ; places : optional atomic positive=1)
Title: Bin2Hex
Description: Converts a binary number (all 1s and 0s, text or numeric) to a hexidecimal number (text).   If the number has 10 or more bits, then the top-most bit is interpreted as a sign bit and the number is taken to be in 2's complement.
Definition: Dec2Hex(Bin2Dec(binNum),places)
NodeLocation: 80,216,1
NodeSize: 48,24
WindState: 2,364,154,476,224

Function Oct2bin(x : atomic  ; places : optional positive atom=1)
Title: Oct2Bin
Description: Converts a hexidecimal number to binary.  The result is text.
Definition: index digits :=split(""&x,"");~
var binary := Join(SelectText(hexToBin_Map[hexDigits=digits],2,4),digits);~
var isNeg := TextLength(binary)>=10 and SelectText(x,1,1)>'3';~
binary := TextTrim(binary,leftOnly:true,trimChars:if isNeg then "1" else "0");~
if isNeg then binary := "1" & binary;~
if isNeg then places := max([places,10]);~
if TextLength(binary)<places then (~
  binary := SelectText( if isNeg then "111111111111111111111111" else "0000000000000000000000000",~
                                    1, max([0,places-TextLength(binary)])) & binary~
);~
binary
NodeLocation: 186,40,1
NodeSize: 48,24
WindState: 2,88,141,685,359
NumberFormat: 2,I,4,2,0,0,4,0,$,0,"ABBREV",0

Function Bin2oct(binNum : atomic ; places : optional atomic positive=1)
Title: Bin2Oct
Description: Converts a binary number (all 1s and 0s, text or numeric) to a octal number (in text).   If the number has 10 or more bits, then the top-most bit is interpreted as a sign bit and the number is taken to be in 2's complement.
Definition: Dec2Oct(Bin2Dec(binNum),places)
NodeLocation: 80,98,1
NodeSize: 48,24
WindState: 2,364,154,476,224

Function Oct2hex(x : atomic  ; places : optional positive atom=1)
Title: Oct2Hex
Description: Converts an octal number to hex.  If x has 10 digits, it is interpreted as a 2's complement number, so that the most significant bit determines whether it is negative.
Definition: Dec2Hex(Oct2Dec(x),places)
NodeLocation: 186,216,1
NodeSize: 48,24

Function Hex2oct(x : atomic text ; places : optional positive atom=1)
Title: Hex2Oct
Description: Converts a hexidecimal number to an octal number (in text).  If x has 10 or more digits, it is interpreted as a negative number in 2's complement.
Definition: Dec2Oct(Hex2Dec(x),places)
NodeLocation: 400,98,1
NodeSize: 48,24

Module Base_conversion_inte
Title: Base conversion internals
Author: Lonnie
Date: Thu, May 26, 2011 9:09 AM
DefaultSize: 48,24
NodeLocation: 552,272,1
NodeSize: 56,32

Constant Hexdigits
{!40000|Att_PrevIndexValue: ['0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F']}
Title: hexDigits
Definition: ['0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F']
NodeLocation: 96,40,1
NodeSize: 48,24

Constant Hextobin_map
Title: hextoBin Map
Definition: Table(Hexdigits)(~
'0000','0001','0010','0011','0100','0101','0110','0111','1000','1001','1010','1011','1100','1101','1110','1111')
NodeLocation: 96,104,1
NodeSize: 48,24
DefnState: 2,216,226,386,448,0,MIDM

Close Base_conversion_inte

Close Base_conversion_lib

Variable RGB_Value
Title: RGB Value
Definition: Table(RGB)(204,238,255)
NodeLocation: 320,120,1
NodeSize: 48,24
DefnState: 2,168,374,416,303,0,MIDM
Aliases: FormNode RGB_Value1426715626

Index RGB
{!40000|Att_PrevIndexValue: ['Red','Green','Blue']}
Title: RGB
Definition: ['Red','Green','Blue']
NodeLocation: 328,48,1
NodeSize: 48,24

Close Internal

Variable ActiveSheet
Title: ActiveSheet
Description: The Workbook COM object contains a property 'ActiveSheet' which returns a Worksheet object - the active worksheet.~
~
The definition of this variable is:~
Add_a_workbook->ActiveSheet~
~
Which is the same as:~
COMGetProperty(Add_a_workbook,"ActiveSheet")~
~
<a href="https://msdn.microsoft.com/EN-US/library/office/dn301167.aspx">MSDN reference for the Workbook Object's Properties</a>
Definition: Add_a_workbook->ActiveSheet
NodeLocation: 96,376,1
NodeSize: 56,24
WindState: 2,102,77,476,224

Variable ActiveSheet__Range
Title: ActiveSheet->Range
Description: The Worksheet COM object contains a property 'Range' which returns a 'Range' Object. The Range object represents a cell or range of cells.~
~
Here we select a range of cells within the ActiveSheet worksheet that we want to modify.~
~
The definition of this variable is:~
ActiveSheet->Range("B2:F6")~
~
Which we could have written as:~
COMGetProperty(ActiveSheet,"Range","B2:F6")~
~
<a href="https://msdn.microsoft.com/EN-US/library/office/ff836512.aspx">MSDN reference for the Worksheet->Range Property</a>~

Definition: ActiveSheet->Range("B2:F6")
NodeLocation: 96,464,1
NodeSize: 88,24
WindState: 2,102,77,476,224
ValueState: 2,11,138,416,303,0,MIDM

Variable Close_Workbook
Title: Close Workbook
Description: Closes the Excel workbook. Some versions of Excel will remain open after closing Analytica.~
~
The definition of this variable:~
Add_a_workbook->Close~
Is the same as :~
COMCallMethod(Add_a_workbook,"Close")~
~
<a href="https://msdn.microsoft.com/EN-US/library/office/dn301170.aspx">MSDN reference for the Workbook Object</a>~

Definition: Add_a_workbook->Close
NodeLocation: 600,288,1
NodeSize: 48,24
WindState: 2,102,77,476,224
ValueState: 2,264,261,416,303,0,MIDM

Variable Cell_Interior
Title: Cell Interior
Description: The 'Range' Object has an 'Interior' property which returns an 'Interior' Object and represents the cell interior. So we need to get this Object to change the cell background color.~
~
The definition of this variable is:~
ActiveSheet__Range->Interior~
~
Which is the same as  :~
COMGetProperty(ActiveSheet__Range,"Interior")~
~
<a href="https://msdn.microsoft.com/EN-US/library/office/ff197454.aspx">MSDN reference for the Range Object's members</a>~

Definition: ActiveSheet__Range->Interior
NodeLocation: 408,464,1
NodeSize: 48,24
ValueState: 2,136,133,416,303,0,MIDM

Variable Set_Interior_Color
Title: Set Interior Color - RGB
Description: The Interior Object has a Property 'Color' which can be used to set the Cell interior color. This Property is set using RGB values. We need to pass the color value to Excel as a decimal integer. Excel will assign this to a 4 byte Long Int with Red as the least significant byte. Essentially this means that the RGB hex values appear inverted.~
~
Basically you take the Hex numbers for the RGB values and invert them. So for an RGB of 204,238,255 - a light blue green - (CC,EE,FF in Hex) you would invert the 3 Hex numbers to get the hex number FFEECC.~
~
Then convert this to decimal - 16772812.,  for instance using the Analytica Hex2dec function in the base conversion library.~
~
<a href="http://stackoverflow.com/questions/24132665/">A page explaining Excel and RGB.</a>~
~
<a href="https://msdn.microsoft.com/EN-US/library/office/dn300869.aspx">MSDN reference for the Interior Object Properties</a>
Definition: Cell_Interior->Color:=16772812~
{Or like this:}~
{Cell_interior->Color:=Hex2dec('FFEECC')}
NodeLocation: 672,464,1
NodeSize: 48,40
WindState: 2,72,9,486,425
ValueState: 2,152,149,416,303,0,MIDM
NumberFormat: 2,I,4,2,0,0,4,0,$,0,"ABBREV",0,,,0,0

Variable Set_Interior_Color__
Title: Set Interior Color - ColorIndex
Description: One way to set the cell interior color of an Excel Range:~
~
The 'Interior' Object contains a Property 'ColorIndex' Which returns a variant that represents a color on the Excel color palette. ~
~
So if we want to set the color of the cell interiors to say, light yellow, we can do that by assigning the ColorIndex:=19~
<a href="https://msdn.microsoft.com/en-us/library/cc296089%28v=office.12%29.aspx">MSDN reference Adding color to Excel</a>~
~
The Definition of this variable is Cell_interior->ColorIndex:=19:~
Which is the same as COMPutProperty(Cell_Interior,"ColorIndex,19)~
~
<a href="https://msdn.microsoft.com/EN-US/library/office/dn300869.aspx">MSDN reference for the Interior Object Properties</a>
Definition: Cell_Interior->ColorIndex:=19
NodeLocation: 552,464,1
NodeSize: 48,40
WindState: 2,102,77,476,357
ValueState: 2,152,149,416,303,0,MIDM
NumberFormat: 2,I,4,2,0,0,4,0,$,0,"ABBREV",0,,,0,0

FormNode RGB_Value1426715626
Title: RGB Value
Definition: 0
NodeLocation: 748,560,1
NodeSize: 100,16
NodeInfo: 1,0,0,1,0,0,0,78,0,,0
Original: RGB_Value

Variable RGB_to_Excel_Color
Title: RGB to Excel Color
Definition: Hex2Dec(~
Dec2hex(RGB_Value[@RGB=3]) & ~
Dec2hex(RGB_Value[@RGB=2]) & ~
Dec2hex(RGB_Value[@RGB=1])~
)
NodeLocation: 912,464,1
NodeSize: 48,32
WindState: 2,482,166,476,344
ValueState: 2,152,149,416,303,0,MIDM
NumberFormat: 2,I,4,2,0,0,4,0,$,0,"ABBREV",0,,,0,0

Variable Convert_RGB_and_Set_
Title: Convert RGB and Set Color
Description: Convert RGB to Excel color using Base Conversion library
Definition: Cell_Interior->Color:=RGB_to_Excel_Color
NodeLocation: 792,464,1
NodeSize: 48,40
NodeInfo: 1,1,1,1,1,1,0,,0,,0
WindState: 2,102,77,476,447

Variable BorderAround
Title: BorderAround
Description: The 'Range' Object contains a method 'BorderAround' which draws a border around a range of cells. We'll use it here to draw a red border with a dotted line style around the Range of cells.~
~
Set the first parameter - LineStyle Variant - to '-4118' which is dotted. The 3rd parameter is the ColorIndex which we'll set to 3 which on the Excel color Palette is Red.~
~
The 4th Parameter - Color Variant - can be used but you need to convert the RGB color first. Refer to the description for variable Set_Interior_Color for a brief explanation.~
~
<a href="https://msdn.microsoft.com/en-us/library/cc296089%28v=office.12%29.aspx">MSDN reference Adding color to Excel</a>~
~
<a href="https://msdn.microsoft.com/EN-US/library/office/ff197210.aspx">MSDN reference for the BorderAround Method</a>~

Definition: ActiveSheet__Range->BorderAround(-4118,null,3)
NodeLocation: 272,464,1
NodeSize: 64,24
WindState: 2,102,77,476,224
ValueState: 2,248,245,416,303,0,MIDM

Close Creating_a_new_Workb

Close COM_with_Excel_workb