One of the most common action you’ll need to learn is copying and pasting a range of data. It’s very easy to do this manually. In normal data, we use either CTRL + C to copy a selection of data and then use CTRL + V to paste the selected…
When you drag any cell with data and drop it on any other cell with data, you will get the warning, “There’s already data here. Do you want to replace it?”. How to disable it? Warning Message Disable “There’s already data here.” warning by built-in Settings Go to File > Options…
Suppose you want to send a demo file for users to check, but you don’t want it to be used more than a certain number of times. There are many possible ways to do this, but here I’ll show you some simple VBA statements to use, called CustomDocumentProperties. Limit The…
You may get warning message about the Document Inspector when you want to save Excel file. Is there a script can remove this warning? Warning Message Be careful! Parts of your document may include personal information that can’t be removed by the Document Inspector. Remove Document Inspector Warning Go to…
The Excel VBA NOW function is a built-in function in Excel that return the current system date and time, but not contains milliseconds. So, we can’t use NOW to convert ISO 8601 UTC time stamp. With Milliseconds No Milliseconds If you don’t need milliseconds(milliseconds will be display 000), try this…
VBA Base64 HMAC SHA256 and SHA1 To use this code, you need do this: Inside the VBE, Go to Tools -> References, then Select Microsoft XML, v6.0 (or whatever your latest is. This will give you access to the XML Object Library.) Test Base64 HMAC SHA256 debug.Print Base64_HMACSHA256(“abc”,”1234567890″) hgVRhIBbSkZqe+OY/0pxWfkFXqfu8zn8lNzsbxZYmLo= Base64…
In VBA, strings assigned to variables or properties are enclosed in double quotes “”, Sometimes you might want to place Double Quotes “” in a string in Excel VBA. For example: A1 =IF(B1=”Excel”,B1,””) In VBE, you can replace ” with “”, and then enclose the entire string =IF(B1=””Excel””,B1,””””) in double quotes…
The following Excel VBA code can help you to list all files in folder and sub-folders into a worksheet. VBA Code Cloumn A: Directory, Cloumn B: File Name Line 49 Replace AllFiles.Add (Key & MyFileName), “” To AllFiles.Add (MyFileName), Key Line 69 Replace Sheets(“Files”).[A1].Resize(AllFiles.Count, 1) = WorksheetFunction.Transpose(AllFiles.keys) To Sheets(“Files”).[A1].Resize(AllFiles.Count, 1)…