Category Archives: Excel

Removing the password of an Excel VBA project

‘ to remove the password of a excel with .xlam extension ‘ 1. use the Savexlam2xls subroutine to save a copy of xlam file to xls file ‘ 2. run the moveprotect subroutine to remove the password of the xls … Continue reading

Share
Posted in Excel, VBA/ASP | Tagged , , , | Leave a comment

Add a worksheet function to extract hyperlinks

There is no worksheet function to extract hyperlink in a given cell. We can use VBA to create a function to do this. The simple function is listed here. Function getlink(rng) getlink = rng.Hyperlinks(1).Address End Function In your worksheet, you … Continue reading

Share
Posted in Excel, Tips, VBA/ASP | Tagged , , | Leave a comment

Day length, sunrise and sunset calculation

There are a lot of situations we need compute day length, sunrise and sunset time. I collected a set of web pages that discuss how to compute them in different ways. I posted the information just for reference. Length of … Continue reading

Share
Posted in Excel, Programming, Tips | Tagged , , , , , , | Leave a comment

System API and file system access in VBA

Brief Introduction Microsoft Visual Basic for Application (VBA) is a powerful tool for Microsoft Office Suite. General users usually only use a small portion of functions Microsoft Office provides and never have a chance to touch VBA. However, Microsoft Excel … Continue reading

Share
Posted in Excel, VBA/ASP | Tagged , , , , , , , , , , , | Leave a comment

Return an array from a customized VBA function

Instruction You may use Visual Basic for Application for a while and wrote a lot of customized functions. I found majority of VBA users do not know how to use array as function arguments or return values in an array. … Continue reading

Share
Posted in Data analysis, Excel, VBA/ASP | Tagged , , , , , , | 1 Comment