Technology Tales

Office VBA Reference

Visual Basic for Applications is a programming language designed to extend Office applications by enabling automation of repetitive tasks and the creation of custom functionalities. It allows users to perform actions typically done manually through the interface, such as formatting documents or interacting with users, and can be used to enhance Office applications with tailored features that meet specific business needs. This resource is aimed at experienced users seeking to leverage VBA for improving efficiency and customising Office tools through code.

SAS Usage Note 49421: A "Cannot write image" error might occur with ODS Graphics and the SAS/GRAPH® Statistical Graphics (SG) procedures

A "Cannot write image" error may occur in SAS when generating graphics using ODS Graphics or SG procedures, typically due to insufficient disk permissions, unauthorised access to a directory, or conflicts between ODS destinations. This commonly arises when attempting to write graphics to a specific ODS destination while the listing destination remains open, which can be resolved by closing the listing destination with ods listing close; or specifying a valid directory using the GPATH= option in the ods listing statement. Alternatively, wrapping procedures in ods html statements with the PATH= option to define an accessible output directory may also address the issue. The error is relevant across multiple operating systems, including Windows, Linux and various Unix variants, and applies to SAS 9.4M2 and later versions.

SAS Usage Note 39705: Troubleshooting "Sort initialisation failure" and "Sort execution failure" errors in Windows and UNIX environments

Errors encountered during SAS SORT and SQL procedures, such as "Sort initialisation failure" and "Sort execution failure," often stem from insufficient disk space, memory, or system resource limits, incorrect SAS or operating system configurations, or inadequate permissions for temporary directories. Troubleshooting involves reviewing SAS log details using commands like proc options to assess memory and utility settings, verifying user permissions for directories referenced by the WORK and UTILLOC options, checking system-imposed limits with tools like ulimit -a and ensuring sufficient space in temporary storage locations. Adjustments to system parameters, such as increasing MEMSIZE or SORTSIZE based on memory usage statistics, may be necessary. These issues can occur across a wide range of operating systems, requiring careful monitoring of resource allocation and temporary file management to resolve failures effectively.

Announcing LAMBDA: Turn Excel formulas into custom functions LAMBDA function

Microsoft Excel has introduced a new function called LAMBDA, which allows users to create their own reusable custom functions using Excel's native formula language, without the need for VBA, macros or JavaScript. Previously, creating custom functions required writing code in a separate language such as JavaScript, but LAMBDA removes that barrier, making the capability accessible to non-programmers.

A LAMBDA function accepts up to 253 parameters and a calculation as its final argument, and once named via the Name Manager under the Formulas tab, it can be called anywhere within a workbook just like any built-in Excel function. This brings significant practical benefits, such as reducing repetitive copy-and-pasting of complex formulas, making spreadsheets easier to read and maintain, and allowing errors to be corrected in a single place rather than across multiple cells.

Notably, LAMBDA also supports recursion, meaning a function can call itself, which enables looping behaviour that was previously only achievable through scripting. The function is also compatible with dynamic arrays and rich data types, expanding the range of calculations that can be performed, such as computing distances between cities using geographic coordinates. If an incorrect number of arguments is passed or the function is entered in a cell without being called, Excel will return an error, so following standard formula best practices is advisable.

How to Use Parameters in PowerShell Part I

How to Use Parameters in PowerShell Part II

PowerShell offers two approaches to passing values into scripts: unnamed arguments via the $args array, and named parameters defined using a param() block. Named parameters are generally preferred as they support type enforcement, default values, mandatory fields and validation sets that restrict inputs to predefined options.

Boolean and switch datatypes allow flag-style parameters, with switch being simpler as it requires no assigned value. Parameters can also accept arrays, enabling a single named parameter to handle multiple values at once. For pipeline-based workflows, the ValueFromPipeline attribute allows values to be passed via the pipe operator, with begin, process and end blocks controlling how each piped value is handled.

Where multiple properties need to be piped simultaneously, ValueFromPipelineByPropertyName allows a script to receive named properties from the output of other cmdlets. It is also possible to combine pipelined and directly passed parameters within the same script, provided parameter ordering is managed carefully to avoid unintended value assignment. Together, these techniques make PowerShell scripts considerably more flexible, portable and maintainable across different environments.

How to get rid of page numbers in TeX/LaTeX

Removing page numbers in LaTeX documents can be achieved through several methods depending on the document structure and class used. The nopageno package allows \pagestyle{plain} to function like \pagestyle{empty}, suppressing numbering in simple cases, though it does not affect documents using non-plain styles.

For targeted suppression, \pagestyle{empty} or \thispagestyle{empty} can be applied to specific sections or pages, though these may not override page styles set by commands like \maketitle or \chapter. Custom solutions exist for classes such as KOMA-script and memoir, which offer dedicated page style configurations for special pages. The \pagenumbering{gobble} command prevents page numbers from being printed entirely but resets the counter, making it suitable only for initial use. Additionally, the scrpage2 package provides a method to suppress numbers by redefining \pagemark, though this requires the use of \pagestyle{plain} to be effective.

Debugging SASUSER issues when you use SAS software

Users of SAS software may occasionally encounter problems with the SASUSER directory, typically caused by corrupted or outdated catalogs and item stores. Common issues include warning messages about SASUSER.TEMPLAT not being a valid item store, SAS being unable to open the SASUSER.PROFILE catalog or SASUSER.REGSTRY item store, errors when using the Output Delivery System or generating graphics output and read-only access to the SASUSER directory.

Many of these problems can be resolved by first identifying the SASUSER directory location using the PROC OPTIONS command, closing all active SAS sessions and then renaming the problematic files with an unrecognised file extension, prompting SAS to generate fresh, uncorrupted versions upon restart. Where multiple sessions are running simultaneously, only the first session will have update access, so consolidating to a single session is advisable before attempting further troubleshooting.

In multi-user or grid computing environments, the RSASUSER system option may be enforcing read-only access by policy, in which case programmes and processes should be adjusted to avoid relying on SASUSER for personal content, though in local or private environments the option can be changed to NORSASUSER within the SAS configuration file.

Fedora Packages of R Software

R can be installed on Fedora, CentOS and RHEL systems through a meta-package that pulls in several components covering core runtime files, development headers and Java support, with EPEL providing compatible packages for enterprise Linux distributions. The installation is organised across multiple directories separating binaries, libraries and documentation, and users can install additional packages either from official repositories or through the cran2copr project, which maintains automated daily synchronisations of over 20,000 CRAN packages via Fedora Copr.

Since Fedora 33, R has been linked against FlexiBLAS, a wrapper library that allows runtime switching between optimised BLAS and LAPACK backends without leaving an R session, with OpenBLAS set as the default. Development environments including RStudio Desktop and Server are available through a dedicated Copr repository, alongside graphical front-ends and Emacs integration from official repositories.

Containerised development is supported through both official Fedora Docker images and Toolbox, the latter allowing rootless environments, useful for testing new R releases or keeping development separate from the base system. Issues with packages can be reported through the R-SIG-Fedora mailing list or the relevant GitHub repositories, and the overall Fedora R stack is maintained by a number of contributors.

Advanced ODS Graphics: A deeper dive into item stores

Modifying ODS templates in SAS is a task that involves exporting the original code to a file, editing it to suit specific needs and recompiling it. This process allows users to customise graphical outputs without altering the original templates stored in SAS libraries. For instance, adjusting a Kaplan-Meier plot to display percentages instead of proportions requires deleting the existing template, exporting its source code and modifying parameters such as axis labels, tick values and expressions within the template. Temporary files are often used to store edited templates, ensuring that changes are isolated and do not interfere with the original system templates. This approach enables precise control over visual elements while maintaining the integrity of the underlying data and procedures. The key to successful modification lies in thoroughly reviewing the original template code before making changes, ensuring that edits align with the syntax and structure of the template language.

Code Maven: Groovy

A collection of Groovy programming resources has been migrated from Code Maven, covering a broad range of topics for those learning or working with the language. The material spans foundational concepts such as variables, functions, scope and classes, as well as more practical subjects including file handling, regular expressions, JSON processing, date and time management, exception handling and working with maps and lists. Supplementary slide-based content is also available, addressing reasons to use Groovy, available resources, installation and core language features such as input and output operations.