.value2 vba

Posted on 2018-10-20 | In vba

description: the difference between .text, .value and .value2

.text

The present of cell. May get ####

.value

Get the cell value formatted. May get truncate decimal number.

.value2

Get the underlying value. Actual store value.

webstorm require unresolved function or method

Posted on 2018-10-16 | Edited on 2018-10-17 | In JavaScript

description: You need to enable “Node.js Globals” entry.

solution

enable Coding assistance for Node.js

Microsoft Visual Studio Code insert date time variables into snippet template

Posted on 2018-09-27 | Edited on 2018-07-15 | In VS code

description: make a snippet with current datetime

open snippet file to edit

File -> preference –> user snippets

edit snippet like following

${CURRENT_DATE}/${CURRENT_MONTH}/${CURRENT_YEAR} ${CURRENT_HOUR}:${CURRENT_MINUTE}

{
	"post": {
		"prefix": ".post",
		"body": [
			"---\r",
			"title: ${1}\r",
			"date: ${CURRENT_DATE}/${CURRENT_MONTH}/${CURRENT_YEAR} ${CURRENT_HOUR}:${CURRENT_MINUTE}\r",
			"tags: \r",
			"keywords:\r",
			"categories: \r",
			"---\r",
			"description:\r",
			"\r",
			"\r",
			"\r",
			"\r",
			"---------------------------------------------\r",
			"more on [search4fan.github.io](https://search4fan.github.io/)\r",
			"\r",
			""
		],
		"description": "post template"
	}
}

other variables

TM_SELECTED_TEXT The currently selected text or the empty string
TM_LINE_NUMBER The one-index based line number
TM_FILENAME The filename of the current document
TM_FILENAME_BASE The filename of the current document without its extensions
TM_LINE_INDEX The zero-index based line number
TM_DIRECTORY The directory of the current document
TM_FILEPATH The full file path of the current document
CLIPBOARD The contents of your clipboard
TM_CURRENT_LINE The contents of the current line
TM_CURRENT_WORD The contents of the word under cursor or the empty string

CURRENT_DAY_NAME The name of day (example ‘Monday’)
CURRENT_DAY_NAME_SHORT The short name of the day (example ‘Mon’)
CURRENT_HOUR The current hour in 24-hour clock format
CURRENT_MINUTE The current minute
CURRENT_SECOND The current second

CURRENT_YEAR The current year
CURRENT_YEAR_SHORT The current year’s last two digits
CURRENT_MONTH The month as two digits (example ‘02’)
CURRENT_MONTH_NAME The full name of the month (example ‘July’)
CURRENT_MONTH_NAME_SHORT The short name of the month (example ‘Jul’)
CURRENT_DATE The day of the month

more on search4fan.github.io

get list of modules name in Excel vba

Posted on 2018-09-27 | Edited on 2018-07-18 | In vba

description: vba to get list of modules name

code of vba to get list of module names

Private Sub getAllModuleNames()

    Dim modName As String
    Dim wb As Workbook
    Dim l As Long
    
    Set wb = thisWorkbook
    
    For l = 1 To wb.VBProject.VBComponents.count
        With wb.VBProject.VBComponents(l)
            modName = modName & vbCr & .name
        End With
    Next
    
    'MsgBox "Module Names:" & vbCr & modName
    Cells(40, 1).value = modName
    
    Set wb = Nothing

End Sub

more on search4fan.github.io

VBA Difference between .text .value .value2

Posted on 2018-09-27 | Edited on 2018-07-30 | In vba

description: difference between .text .value .value2 in Excel

.text

get the screen displayed text

1 2	cells(1,1).text 'get ##### if the cell width too short

.value

can get formatted Currency and date

1 2	cells(1,1).value 'get 13/02/2016 12:52 if it is a date

.value2

get underlying value (include empty, string, error, number and boolean)

1 2	cells(1,1).value2 'get 2342432.12 if it is a date

ssms save changes not permitted

Posted on 2018-09-27 | Edited on 2018-07-18 | In sql server

description: Sql Server ‘Saving changes is not permitted’

problem description

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

solution

Tools -> Options
Designers -> Table and Database Designers -> Prevent saving changes that require table re-creation -> unclick

more on search4fan.github.io

display line number in sql server management studio

Posted on 2018-09-27 | Edited on 2018-07-18 | In sql server

description: display line number in mssql editor

tools -> options
Text Editor -> Transact-SQL -> line numbers

more on search4fan.github.io

basic frequent Sql commends in sql server

Posted on 2018-09-27 | Edited on 2018-07-18 | In sql server

description: sql comments for basic query

-- use 
use testdb
go 

select * from people
select top 1 * from people

-- insert into people values (1, 'lily', 'f', 12)
-- insert into people values (2, 'chao', 'm', 16)
-- insert into people values (3, 'alin', 'f',null)

select id, name from people

-- order by
select name, age from people 
order by age desc,id

-- order by number
select name, age from people 
order by 2 --2 means age 

-- isnull()
select name, ISNULL (age, '') from people

--alias
select name, ISNULL (age, '') as a from people

-- concat strings 
select 'people ' + TRIM (name) + ' is age of ' + convert(varchar, isnull(age, '')) as [description] 
from people

-- round()
select age, age * 10 as 'age10',
round(convert(float,age)/8, 1) as 'ageD8',
round(age/3, 0) as 'ageD3'
from people

-- where 
select * from people 
where age <> 12

-- and or 
-- select date from time where data between '2005-10-10' and '1/1/2006'
select * from people where id = 2 or id = 3
select * from people where name = 'chao' and age =16

-- wildcard % like 
select * from people where name like '%h%' 

-- _ single character 
select * from people where name like '_hao%'

-- in 
select * from people
where age in (14,16)

-- not in 
select * from people 
where age not in (16)

-- is null
select * from people 
where age is null

-- is not null 
select * from people 
where age is not null

more on search4fan.github.io

R language to build Decision Tree for wine dataset

Posted on 2018-09-27 | Edited on 2018-07-23 | In machine learning

description: build Decision Tree for wine dataset in R language

read data From File

wine<-read.table("E:\\wine.data",
                 sep=",",
                 col.names = c("Type","Alcohol","Malic","Ash","Alcalinity","Magnesium", "Phenols","Flavanoids", "Nonflavanoids", "Proanthcyanins", "Color", "Hue", "Dilution", "Proline"))
wine$Type=factor(wine$Type)

Data mining using J48 Decision Tree classifier

wine.scale <- cbind(wine[1], scale(wine[-1])) 
data.size <- nrow(wine.scale)
set.seed(1111)
samp <- c(sample(1:data.size, data.size * 0.7))
data.tr <- wine.scale[samp, ]
data.test <- wine.scale[-samp, ]

J48 Model

wine_dt=J48(Type~.,data=data.tr)
wine_dt
prediction_dt = predict(wine_dt, data.test[,2:14]) 
prediction_dt

rpart get tree rules

library(rpart)

res = rpart(Type ~., data = data.tr)
res

terminal_nodes = rownames(res$frame)[res$frame$var =="<leaf>"]
path.rpart(res ,nodes=terminal_nodes)
rules = path.rpart(res ,nodes=terminal_nodes)
listed_rules = unlist(rules)
sapply(rules,"[",-1)

frm <- res$frame
names <- row.names(frm)

prediction_dt = predict(res, data.test[,2:14], type="matrix")
prediction_dt

party

update.packages()
Sys.setenv(JAVA_HOME = "D:\\Program Files\\Java\\jdk1.8.0_151")
install.packages("zoo")
install.packages("party")
install.packages("sandwich")
library(party)
mparty = party(Type ~., data = data.tr)
mparty

party ctree()

p_ctree <- ctree(Type ~., data=data.tr)
ctree_class <- predict(p_ctree,data.test[,2:14], type="response")
test_ctree <- predict(p_ctree,data.test[,2:14], type="node")
test_ctree

r language to cluster iris dataset through k-means and hierarchical clustering

Posted on 2018-09-27 | Edited on 2018-07-24 | In machine learning

description: cluster iris data set by hierarchical clustering and k-means

iris data set

library(RWeka)
iris
#     Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
# 1            5.1         3.5          1.4         0.2     setosa
# 2            4.9         3.0          1.4         0.2     setosa
# 3            4.7         3.2          1.3         0.2     setosa
# 4            4.6         3.1          1.5         0.2     setosa
# 5            5.0         3.6          1.4         0.2     setosa
# 6            5.4         3.9          1.7         0.4     setosa
# 7            4.6         3.4          1.4         0.3     setosa
# 8            5.0         3.4          1.5         0.2     setosa
# 9            4.4         2.9          1.4         0.2     setosa
# 10           4.9         3.1          1.5         0.1     setosa
# 11           5.4         3.7          1.5         0.2     setosa

delete class column

datairis<-iris
datairis$Species<-NULL
datairis 
#     Sepal.Length Sepal.Width Petal.Length Petal.Width
# 1            5.1         3.5          1.4         0.2
# 2            4.9         3.0          1.4         0.2
# 3            4.7         3.2          1.3         0.2
# 4            4.6         3.1          1.5         0.2
# 5            5.0         3.6          1.4         0.2
# 6            5.4         3.9          1.7         0.4
# 7            4.6         3.4          1.4         0.3
# 8            5.0         3.4          1.5         0.2
# 9            4.4         2.9          1.4         0.2
# 10           4.9         3.1          1.5         0.1
# 11           5.4         3.7          1.5         0.2

partitional clustering: K means clustering

kcluster<-kmeans(datairis,3) # k as 3, divide into 3 species
kcluster
# K-means clustering with 3 clusters of sizes 38, 50, 62

# Cluster means:
#   Sepal.Length Sepal.Width Petal.Length Petal.Width
# 1     6.850000    3.073684     5.742105    2.071053
# 2     5.006000    3.428000     1.462000    0.246000
# 3     5.901613    2.748387     4.393548    1.433871

plot cluster with “Sepal.Width” and “Petal.Width”

1 2	plot(datairis[c("Sepal.Width","Petal.Width")],col=kcluster$cluster) points(kcluster$centers[,c("Sepal.Width","Petal.Width")], col=1:3, pch=8, cex=2)

plot cluster with “Sepal.Length” and “Petal.Length”

1 2	plot(datairis[c("Sepal.Length","Petal.Length")],col=kcluster$cluster) points(kcluster$centers[,c("Sepal.Length","Petal.Length")], col=1:3, pch=8, cex=2)

plot cluster with “Petal.Length” and “Petal.Length”

1 2	plot(datairis[c("Petal.Length","Petal.Length")],col=kcluster$cluster) points(kcluster$centers[,c("Petal.Length","Petal.Length")], col=1:3, pch=8, cex=2)

Check the result through a table

iris$Species
table(iris$Species,kcluster$cluster) 
#               1  2  3
#   setosa      0 50  0
#   versicolor  2  0 48
#   virginica  36  0 14

hierarchical clustering

dim(iris)[1] # dim:	It	retrieve	or	set	the	dimension	of	an	object
random = sample(1:dim(iris)[1],50) 
datairis = iris[random,]
datairis$Species=NULL
datairis
#     Sepal.Length Sepal.Width Petal.Length Petal.Width
# 137          6.3         3.4          5.6         2.4
# 5            5.0         3.6          1.4         0.2
# 122          5.6         2.8          4.9         2.0
# 27           5.0         3.4          1.6         0.4
# 40           5.1         3.4          1.5         0.2
# 140          6.9         3.1          5.4         2.1
# 12           4.8         3.4          1.6         0.2
# 116          6.4         3.2          5.3         2.3
# 66           6.7         3.1          4.4         1.4

plot hierarchical clustering

1
2
3

hcluster<-hclust(dist(datairis),method="ave")
hcluster
plot(hcluster,hang=-1) # hang:	The	fraction	of	the	plot	height	by	which	labels	should	hang	below	the	rest	of	the	plot.	A	negative	value	will	cause	the	labels	to	hang	down	from	0.

1 2	iris$Species plot(hcluster,hang=-1,labels=iris$Species[random])

killfun

Personal blog for collecting useful information

RSS