You can try this direct VBA
approach which doesn't require HEX editing. It will work for any files (*.xls, *.xlsm, *.xlam ...).
Tested and works on:
Excel 2007
Excel 2010
Excel 2013 - 32 bit version
Excel 2016 - 32 bit version
Looking for 64 bit version? See this answer
How it works
I will try my best to explain how it works - please excuse my English.
- The VBE will call a system function to create the password dialog box.
- If user enters the right password and click OK, this function returns 1. If user enters the wrong password or click Cancel, this function returns 0.
- After the dialog box is closed, the VBE checks the returned value of the system function
- if this value is 1, the VBE will "think" that the password is right, hence the locked VBA project will be opened.
- The code below swaps the memory of the original function used to display the password dialog with a user defined function that will always return 1 when being called.
Using the code
Please backup your files first!
- Open the file(s) that contain your locked VBA Projects
Create a new xlsm file and store this code in Module1
code credited to Siwtom (nick name), a Vietnamese developer
Option Explicit
Private Const PAGE_EXECUTE_READWRITE = &H40
Private Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" _
(Destination As Long, Source As Long, ByVal Length As Long)
Private Declare Function VirtualProtect Lib "kernel32" (lpAddress As Long, _
ByVal dwSize As Long, ByVal flNewProtect As Long, lpflOldProtect As Long) As Long
Private Declare Function GetModuleHandleA Lib "kernel32" (ByVal lpModuleName As String) As Long
Private Declare Function GetProcAddress Lib "kernel32" (ByVal hModule As Long, _
ByVal lpProcName As String) As Long
Private Declare Function DialogBoxParam Lib "user32" Alias "DialogBoxParamA" (ByVal hInstance As Long, _
ByVal pTemplateName As Long, ByVal hWndParent As Long, _
ByVal lpDialogFunc As Long, ByVal dwInitParam As Long) As Integer
Dim HookBytes(0 To 5) As Byte
Dim OriginBytes(0 To 5) As Byte
Dim pFunc As Long
Dim Flag As Boolean
Private Function GetPtr(ByVal Value As Long) As Long
GetPtr = Value
End Function
Public Sub RecoverBytes()
If Flag Then MoveMemory ByVal pFunc, ByVal VarPtr(OriginBytes(0)), 6
End Sub
Public Function Hook() As Boolean
Dim TmpBytes(0 To 5) As Byte
Dim p As Long
Dim OriginProtect As Long
Hook = False
pFunc = GetProcAddress(GetModuleHandleA("user32.dll"), "DialogBoxParamA")
If VirtualProtect(ByVal pFunc, 6, PAGE_EXECUTE_READWRITE, OriginProtect) <> 0 Then
MoveMemory ByVal VarPtr(TmpBytes(0)), ByVal pFunc, 6
If TmpBytes(0) <> &H68 Then
MoveMemory ByVal VarPtr(OriginBytes(0)), ByVal pFunc, 6
p = GetPtr(AddressOf MyDialogBoxParam)
HookBytes(0) = &H68
MoveMemory ByVal VarPtr(HookBytes(1)), ByVal VarPtr(p), 4
HookBytes(5) = &HC3
MoveMemory ByVal pFunc, ByVal VarPtr(HookBytes(0)), 6
Flag = True
Hook = True
End If
End If
End Function
Private Function MyDialogBoxParam(ByVal hInstance As Long, _
ByVal pTemplateName As Long, ByVal hWndParent As Long, _
ByVal lpDialogFunc As Long, ByVal dwInitParam As Long) As Integer
If pTemplateName = 4070 Then
MyDialogBoxParam = 1
Else
RecoverBytes
MyDialogBoxParam = DialogBoxParam(hInstance, pTemplateName, _
hWndParent, lpDialogFunc, dwInitParam)
Hook
End If
End Function
Paste this code under the above code in Module1 and run it
Sub unprotected()
If Hook Then
MsgBox "VBA Project is unprotected!", vbInformation, "*****"
End If
End Sub
Come back to your VBA Projects and enjoy.
Ages ago, in November 2005, AlistApart.com published an article on how they published a book using nothing but HTML and CSS. See: http://alistapart.com/article/boom
Here's an excerpt of that article:
CSS2 has a notion of paged media (think sheets of paper), as opposed to continuous media (think scrollbars). Style sheets can set the size of pages and their margins. Page templates can be given names and elements can state which named page they want to be printed on. Also, elements in the source document can force page breaks. Here is a snippet from the style sheet we used:
@page {
size: 7in 9.25in;
margin: 27mm 16mm 27mm 16mm;
}
Having a US-based publisher, we were given the page size in inches. We, being Europeans, continued with metric measurements. CSS accepts both.
After setting the up the page size and margin, we needed to make sure there are page breaks in the right places. The following excerpt shows how page breaks are generated after chapters and appendices:
div.chapter, div.appendix {
page-break-after: always;
}
Also, we used CSS2 to declare named pages:
div.titlepage {
page: blank;
}
That is, the title page is to be printed on pages with the name “blank.” CSS2 described the concept of named pages, but their value only becomes apparent when headers and footers are available.
Anyway…
Since you want to print A4, you'll need different dimensions of course:
@page {
size: 21cm 29.7cm;
margin: 30mm 45mm 30mm 45mm;
/* change the margins as you want them to be. */
}
The article dives into things like setting page-breaks, etc. so you might want to read that completely.
In your case, the trick is to create the print CSS first. Most modern browsers (>2005) support zooming and will already be able to display a website based on the print CSS.
Now, you'll want to make the web display look a bit different and adapt the whole design to fit most browsers too (including the old, pre 2005 ones). For that, you'll have to create a web CSS file or override some parts of your print CSS. When creating CSS for web display, remember that a browser can have ANY size (think: “mobile” up to “big-screen TVs”). Meaning: for the web CSS your page-width and image-width is best set using a variable width (%) to support as many display devices and web-browsing clients as possible.
EDIT (26-02-2015)
Today, I happened to stumble upon another, more recent article at SmashingMagazine which also dives into designing for print with HTML and CSS… just in case you could use yet-another-tutorial.
EDIT (30-10-2018)
It has been brought to my attention in that size
is not valid CSS3, which is indeed correct — I merely repeated the code quoted in the article which (as noted) was good old CSS2 (which makes sense when you look at the year the article and this answer were first published). Anyway, here's the valid CSS3 code for your copy-and-paste convenience:
@media print {
body{
width: 21cm;
height: 29.7cm;
margin: 30mm 45mm 30mm 45mm;
/* change the margins as you want them to be. */
}
}
In case you think you really need pixels (you should actually avoid using pixels), you will have to take care of choosing the correct DPI for printing:
- 72 dpi (web) = 595 X 842 pixels
- 300 dpi (print) = 2480 X 3508 pixels
- 600 dpi (high quality print) = 4960 X 7016 pixels
Yet, I would avoid the hassle and simply use cm
(centimeters) or mm
(millimeters) for sizing as that avoids rendering glitches that can arise depending on which client you use.
Best Answer
Question 1
xlPaperUser
is a User-Defined paper size that is assigned a constant value of 256. If this has not been defined, it may throw an error.Question 2
There is no way to create custom paper sizes in Excel, however you can create custom paper sizes on many printers. Under Page Setup, click the Options button. This will bring up the printer properties dialog box. Change your paper size to a custom size using this dialog box and click OK.
Then in Excel run this:
MsgBox PageSetup.PaperSize
. This will give you the new constant value assigned to that paper size in Excel. Then change.PaperSize = xlPaperUser
in your macro to.PaperSize =
& whatever the constant is that you just found.Question 3
.PrintArea
takes a string input, not a range. Change your line toActiveSheet.PageSetup.PrintArea = Range("Img").Address
and it should work.