Martin Chan martinctc

## normalise-replace.vbs
Sub normalise()

Dim rng, cel As Range
Set rng = Application.InputBox("Please select source range area (exclude headers)", "Source data", Default:="=$A$3:$G$10", Type:=8)
Dim max, min As Long

max = Application.max(rng)
min = Application.min(rng)
Debug.Print max
Debug.Print min

## CombineRowsFromSheets.vbs
Sub Combine()

Dim Acc_Name As Variant
Dim i As Long
Dim j As Integer
Dim newsheet As Worksheet

On Error GoTo Errorcatch

Acc_Name = Array("CLloyds", "NFQ", "SSaver", "CISA", "CLMSaver")

## complete case.r
#readClipboard()
#Read copied folder path as something that R can read in properly

library(readxl)
library(tidyverse)
library(stringr)
library(forcats)

setwd("C:\\Users\\Martin.Chan\\Desktop\\R workdesk\\missing values")
add.dt<-read_excel("ORD-272769-F4S6  - Additional info for UK.xlsx",sheet="Additional info",na="NA")

## Format a chart.vbs
Sub Format_a_Chart()

'r converts cm to points
r = 28.3464567


Dim shp1 As Shape
Dim i, j, k As Long

Set shp1 = ActiveWindow.Selection.ShapeRange(1)

## String Replace nth.r
str_replace_n <- function(x, pattern, replace, n){
  g <- gregexpr(pattern,x)[[1]][n]
  output <- paste0(substr(x,1,g-1),replace,substr(x,g+1,nchar(x)))
  output
}

str_replace_n("ALLAHABAD","A","U",3)
#[1] "ALLAHUBAD"

str_replace_nth <- function(x, pattern, replacement, n) {

## 3-Table Tinkerer.vbs
Sub table_tinkerer_threetabs()


'Table tinkerer tool created for three tables iterated across many slides


'Alt+F11 to start up code module
'Insert New Module >>
'Select Table >> Run

## Tweak Scatter Labels for PPT.vbs
Sub ScatterLabelsTweak()

Dim sld As Slide
Dim shp As Shape
Dim sr As Series
Dim chrt As Chart
Dim i, j, k, m As Long

    For Each sld In ActivePresentation.Slides
        For Each shp In sld.Shapes

## match_counter.R
match_counter <- function(df,target){

  arguments <- as.list(match.call())
  targ <- deparse(substitute(target)) #variable to string

#Produce a data frame of all columns on whether they match values of target column
  df %>%
    mutate_all(funs(.==eval(parse(text=targ)))) %>%
    colSums(na.rm = TRUE) %>% data.frame() %>%
    rownames_to_column() -> matches

## Time Stamp.vbs
Sub TimeStamp()

time_stamp = Format(DateTime.Now, "yyyyMMdd_hhmmss")

Debug.Print time_stamp

End Sub

## Change Data Type.R
Q12 <- data.frame(num=1:2000,type=sample(c("Sandwich","Porridge","Muesli/cereal","Other","No breakfast"),2000,
              replace=TRUE,prob=c(0.2,0.15,0.25,0.15,.25)))

glimpse(Q12)

#All columns as character
Q12 %>%
  mutate_all(funs(as.character)) %>%
  glimpse()
	Sub Combine()

	Dim Acc_Name As Variant
	Dim i As Long
	Dim j As Integer
	Dim newsheet As Worksheet

	On Error GoTo Errorcatch

	Acc_Name = Array("CLloyds", "NFQ", "SSaver", "CISA", "CLMSaver")
	#readClipboard()
	#Read copied folder path as something that R can read in properly

	library(readxl)
	library(tidyverse)
	library(stringr)
	library(forcats)

	setwd("C:\\Users\\Martin.Chan\\Desktop\\R workdesk\\missing values")
	add.dt<-read_excel("ORD-272769-F4S6 - Additional info for UK.xlsx",sheet="Additional info",na="NA")
	Sub Format_a_Chart()

	'r converts cm to points
	r = 28.3464567


	Dim shp1 As Shape
	Dim i, j, k As Long

	Set shp1 = ActiveWindow.Selection.ShapeRange(1)
	Sub table_tinkerer_threetabs()


	'Table tinkerer tool created for three tables iterated across many slides


	'Alt+F11 to start up code module
	'Insert New Module >>
	'Select Table >> Run
	Sub ScatterLabelsTweak()

	Dim sld As Slide
	Dim shp As Shape
	Dim sr As Series
	Dim chrt As Chart
	Dim i, j, k, m As Long

	For Each sld In ActivePresentation.Slides
	For Each shp In sld.Shapes
	match_counter <- function(df,target){

	arguments <- as.list(match.call())
	targ <- deparse(substitute(target)) #variable to string

	#Produce a data frame of all columns on whether they match values of target column
	df %>%
	mutate_all(funs(.==eval(parse(text=targ)))) %>%
	colSums(na.rm = TRUE) %>% data.frame() %>%
	rownames_to_column() -> matches
	Sub TimeStamp()

	time_stamp = Format(DateTime.Now, "yyyyMMdd_hhmmss")

	Debug.Print time_stamp

	End Sub
	Q12 <- data.frame(num=1:2000,type=sample(c("Sandwich","Porridge","Muesli/cereal","Other","No breakfast"),2000,
	replace=TRUE,prob=c(0.2,0.15,0.25,0.15,.25)))

	glimpse(Q12)

	#All columns as character
	Q12 %>%
	mutate_all(funs(as.character)) %>%
	glimpse()