Submit your Excel / VBA Question
|
Use the adjacent form to submit your Excel / VBA question and have it answered publicly by Excel Savants.
Please note that Excel Savants will not provide code for sophisticated VBA macros using this free channel but will provide the code for simple macros and address specific questions about what VBA arguments to use and why code is not functioning as intended. If you'd like to have Excel Savants code a custom macro, you may submit your request below and you can an expect a private response with a project bid within 1 business day. |
|
|
Please consider supporting the Excel Savants Answer Forum by visiting our advertisers:
|
|
How do I open up a password protected Excel 2010 workbook if I can’t recall the password?
09/23/2019
Before addressing your question it’s germane to understand the types of password protection options available in Excel 2010 and above. Password protection can be implemented at any or all of the following levels in Excel:
Worksheet-level:
A locked worksheet controls how users are able to interact with the worksheet. For example, worksheet protection can limit rows and columns from being added or deleted. Protection can also be extended to cells, ranges, formulas, and ActiveX or Form controls.
In order to unlock a worksheet in the absence of the password, first rename the extension of the file from *.xlsx to *.zip. Then, using file compression software such as WinZip, open this .zip file. In the directory of folders, locate the “xl” folder and then the “worksheets” folder. Within the “worksheets” folder, select the locked worksheet(s) such as “sheet1.xml”. Next open this .xml file in the Notepad application. Search for the line of code that begins with “<sheetProtection algorithmName=”SHA-512″ hashValue=“ and then delete this entire line of code. Save the changes to this .xml file in the “worksheets” folder. Finally rename the extension of the overarching file from *.zip back to *.xlsx. The worksheet(s) will then be unprotected. Worksheets can also be unlocked via a macro using a combination of the visual basic arguments “ActiveSheet.Unprotect Chr” and “ActiveSheet.ProtectContents=FALSE”
Workbook structure-level:
Locking the workbook structure prevents users without the password from viewing hidden worksheets, adding, moving, deleting, renaming or hiding worksheets. The visual basic code below can be used to unlock the workbook structure without a password:
ActiveWorkbook.Unprotect “YourPassword”, True, True
Excel file-level:
Locking the file structure encrypts the Excel file and prevents the opening of the file without the password. It is not possible to unlock a secured Excel file without the password. Microsoft utilizes an Advanced Encryption Standard (AES) and a 128-bit key so the only way to break the password is by using third-party password hacking software. Keep in mind that these software programs may harbor malicious algorithms and present a serious security risk.
- Worksheet level
- Workbook structure level
- File level
- Visual Basic editor
Worksheet-level:
A locked worksheet controls how users are able to interact with the worksheet. For example, worksheet protection can limit rows and columns from being added or deleted. Protection can also be extended to cells, ranges, formulas, and ActiveX or Form controls.
In order to unlock a worksheet in the absence of the password, first rename the extension of the file from *.xlsx to *.zip. Then, using file compression software such as WinZip, open this .zip file. In the directory of folders, locate the “xl” folder and then the “worksheets” folder. Within the “worksheets” folder, select the locked worksheet(s) such as “sheet1.xml”. Next open this .xml file in the Notepad application. Search for the line of code that begins with “<sheetProtection algorithmName=”SHA-512″ hashValue=“ and then delete this entire line of code. Save the changes to this .xml file in the “worksheets” folder. Finally rename the extension of the overarching file from *.zip back to *.xlsx. The worksheet(s) will then be unprotected. Worksheets can also be unlocked via a macro using a combination of the visual basic arguments “ActiveSheet.Unprotect Chr” and “ActiveSheet.ProtectContents=FALSE”
Workbook structure-level:
Locking the workbook structure prevents users without the password from viewing hidden worksheets, adding, moving, deleting, renaming or hiding worksheets. The visual basic code below can be used to unlock the workbook structure without a password:
ActiveWorkbook.Unprotect “YourPassword”, True, True
Excel file-level:
Locking the file structure encrypts the Excel file and prevents the opening of the file without the password. It is not possible to unlock a secured Excel file without the password. Microsoft utilizes an Advanced Encryption Standard (AES) and a 128-bit key so the only way to break the password is by using third-party password hacking software. Keep in mind that these software programs may harbor malicious algorithms and present a serious security risk.
Why can’t I import data from certain websites using Microsoft Excel?
7/31/2019
Excel versions 1997 - 2010 rely on a rudimentary native browser for external data queries which is unable to handle modern website features like Adobe flash and ad trackers. As a result, you’ll often encounter a litany of script errors when attempting to set up a link to a live external data source using this built-in web browser. While you may be able to click through these script errors and proceed with establishing the external data link, there will be times when an external data query link cannot be established due to these script errors and the Excel program may crash.
There is however an elegant solution to this problem. Beginning with the Excel 2016 build, Microsoft launched an enhanced data query tool called “Power Query” which is able to process data pulls from websites which earlier versions of Excel could not handle. If you are using Excel 2003 or Excel 2010, you can download the free Power Query Add-In from the Microsoft office support website.
Can I use conditional formatting in Excel 2010 to highlight cells according to a date found in another column?
7/19/2019
Conditionally highlighting cells in one column based data in another column can be easily accomplished using Excel’s Conditional Formatting functionality.
For example, assume you have a list of event dates in column ‘A’ and you want to highlight the corresponding event descriptions in column ‘B’ if the date in column ‘A’ occurs before 11/02/2019.
Since the conditional formatting formula requires that dates used directly in the formula be in the Excel “serial” date format (Excel stores dates as sequential serial numbers, with January 1, 1900 being serial number 1, because it’s more efficient for use in date calculations), you’ll want to convert 11/02/2019 to this format. To determine the Excel serial date of 11/02/2019, input this value into any cell in your worksheet and then change the format of that cell from ‘date’ to ‘number’. The Excel serial number 43771 will be displayed in this cell because it is 43770 days after January 1, 1900 which is serial number 1.
For example, assume you have a list of event dates in column ‘A’ and you want to highlight the corresponding event descriptions in column ‘B’ if the date in column ‘A’ occurs before 11/02/2019.
Since the conditional formatting formula requires that dates used directly in the formula be in the Excel “serial” date format (Excel stores dates as sequential serial numbers, with January 1, 1900 being serial number 1, because it’s more efficient for use in date calculations), you’ll want to convert 11/02/2019 to this format. To determine the Excel serial date of 11/02/2019, input this value into any cell in your worksheet and then change the format of that cell from ‘date’ to ‘number’. The Excel serial number 43771 will be displayed in this cell because it is 43770 days after January 1, 1900 which is serial number 1.
To create the conditional formatting formula, first highlight column ‘B’. Then navigate to the Excel Ribbon, select Conditional Formatting then “New Rule” then “Use a formula to determine which cells to format". In the formula box input =$A1<43771. Alternatively if the date 11/02/2019 is located in cell C2, the formula can be written as =$A1<$C$2. Next choose how you’d like to format cells that meet this criteria for example by filling the cells with a light green fill and displaying text in an italic font style.
The event descriptions in column ‘B’ with corresponding dates in column ‘A’ which occur before 11/02/2019 will now be formatted with a light green fill and an italic font style.
The event descriptions in column ‘B’ with corresponding dates in column ‘A’ which occur before 11/02/2019 will now be formatted with a light green fill and an italic font style.
How can you hide a row within Excel using a formula based on a condition?
7/11/2019
There is no native Excel formula which allows you to conditionally hide an entire row based on user-defined criteria. You can however create a simple macro which would leverage the VBA command “EntireRow.Hidden” to accomplish this.
For example, let’s assume you have a worksheet with daily sales figures in column B (column number 2) for a sole proprietorship brick and mortar retail business for the last 12 months and there’s one header row followed by 365 rows of data representing each day of the last year (for a total of 366 rows). However, while this single employee business is generally open 7 days a week, it is closed on various holidays and when the owner is on vacation. The sales data is directly exported from the business Point of Sale system which does not account for whether the business is open on any given day. Instead of manually hiding the rows of data corresponding to when the business is closed, you want Excel to automatically perform this task. You can insert an ‘Active X’ command button into the worksheet which could launch the following macro I have written for you at the click of a button. When executed, the macro examines each of the 365 rows of sales data in column B of the worksheet and then hides any row where the sales for that day is less than $.01. When the macro has completed its tasks, a dialog box appears detailing how many rows have been hidden. I have included a example file below available for download as well as the VBA code for this macro.
For example, let’s assume you have a worksheet with daily sales figures in column B (column number 2) for a sole proprietorship brick and mortar retail business for the last 12 months and there’s one header row followed by 365 rows of data representing each day of the last year (for a total of 366 rows). However, while this single employee business is generally open 7 days a week, it is closed on various holidays and when the owner is on vacation. The sales data is directly exported from the business Point of Sale system which does not account for whether the business is open on any given day. Instead of manually hiding the rows of data corresponding to when the business is closed, you want Excel to automatically perform this task. You can insert an ‘Active X’ command button into the worksheet which could launch the following macro I have written for you at the click of a button. When executed, the macro examines each of the 365 rows of sales data in column B of the worksheet and then hides any row where the sales for that day is less than $.01. When the macro has completed its tasks, a dialog box appears detailing how many rows have been hidden. I have included a example file below available for download as well as the VBA code for this macro.
| Excel Savants LLC Example File-Hide Row based on condition |
Sub HideRow()
Dim Hidecount As Integer
StartRow = 2
EndRow = 366
ColumnNum = 2
Hidecount = 0
For RowCnt = StartRow To EndRow
If Cells(RowCnt, ColumnNum).Value < .01 Then
Hidecount = Hidecount + 1
Cells(RowCnt, ColumnNum).EntireRow.Hidden = True
Else
Cells(RowCnt, ColumnNum).EntireRow.Hidden = False
End If
Next RowCnt
MsgBox Hidecount & " rows have been sucessfully hidden"
End Sub
Dim Hidecount As Integer
StartRow = 2
EndRow = 366
ColumnNum = 2
Hidecount = 0
For RowCnt = StartRow To EndRow
If Cells(RowCnt, ColumnNum).Value < .01 Then
Hidecount = Hidecount + 1
Cells(RowCnt, ColumnNum).EntireRow.Hidden = True
Else
Cells(RowCnt, ColumnNum).EntireRow.Hidden = False
End If
Next RowCnt
MsgBox Hidecount & " rows have been sucessfully hidden"
End Sub
What is the significance of the number of columns at 16,384 in Excel 2016? Why didn’t Microsoft go further, up to columun`ZZZ` (17,576) instead of stopping at `XFD` (16,384)?
7/8/2019
I would speculate that the number of columns at 16,384 was likely chosen to optimize allocation of computer memory resources. Excel is performing ongoing checks to see if its cell limit has been exceeded. Note that 16,384 = 2^14 which represents a 3-letter "bit" in computing terms. However 2^15 would enter 4-letter bit territory which requires significantly more memory allocation to process. By limiting the bounds of the columns to 2^14, Excel is maximizing computer memory resources.
How can I split a single Excel column of data into two columns by using RPA automation anywhere in an Excel sheet?
7/8/2019
It is not apparent what sort of rule-driven run book automation (RBA) you are envisioning or what you are specifically referring to by "splitting" a column of Excel data. If you have grouped data that needs to be divided into multiple columns, a visual basic macro could deftly perform this task. The macro would use the "Selection.Columns.Ungroup" command (and/ or the "EntireColumn.Insert" command depending on the task you are trying to accomplish). This macro could be set to run either at the click of a button or when some other user-specified event occurs.
If you'd like the code for creating a macro to split columns, then please submit a new Answer Forum request and include an explanation of what you mean by splitting columns of data and what, in your mind, constitutes RPA automation.
If you'd like the code for creating a macro to split columns, then please submit a new Answer Forum request and include an explanation of what you mean by splitting columns of data and what, in your mind, constitutes RPA automation.
Excel - How do I get a cell to return a value for background colors of a cell?
7/2/2019
The optimal way to return the background color in Excel is to:
1) Create a user-defined function (UDF) in Excel’s Visual Basic editor. In the example below, I have named the UDF “Get_Cell_Color”.
To create this UDF, insert a new module into Excel’s Visual Basic editor and then input this code:
Function Get_Cell_Color(FCell As Range) As String
'Returns the Cell color
Dim xColor As String
xColor = CStr(FCell.Interior.Color)
xColor = Right("000000" & Hex(xColor), 6)
Get_Cell_Color = Right(xColor, 2) & Mid(xColor, 3, 2) & Left(xColor, 2)
End Function
2) Call this UDF for the cell in which you want to evaluate the background color just as you would call any other Excel formula. For example, the formula to evaluate the background color of cell “A1” would be: "=Get_Cell_Color(A1)". If cell A1 is filled with Excel’s “standard” purple color then the UDF formula would return a cell fill color code value of “7030A0”.
1) Create a user-defined function (UDF) in Excel’s Visual Basic editor. In the example below, I have named the UDF “Get_Cell_Color”.
To create this UDF, insert a new module into Excel’s Visual Basic editor and then input this code:
Function Get_Cell_Color(FCell As Range) As String
'Returns the Cell color
Dim xColor As String
xColor = CStr(FCell.Interior.Color)
xColor = Right("000000" & Hex(xColor), 6)
Get_Cell_Color = Right(xColor, 2) & Mid(xColor, 3, 2) & Left(xColor, 2)
End Function
2) Call this UDF for the cell in which you want to evaluate the background color just as you would call any other Excel formula. For example, the formula to evaluate the background color of cell “A1” would be: "=Get_Cell_Color(A1)". If cell A1 is filled with Excel’s “standard” purple color then the UDF formula would return a cell fill color code value of “7030A0”.
Note that this example UDF is by default considered to have a “public” scope by Excel meaning that it is available for all sheets in the Workbook as well as all the procedures (Sub and Function) across every module in the Workbook. However, the UDF is not available for use in other Excel workbooks unless you:
a) Create an “Add-in” and install it on your Excel application.
and/or
b) Save the UDF in the Personal Macro Workbook (which is a hidden system file that opens with the Excel application).
If you are interested in learning more about creating an “Add-in” and/or the Personal Macro Workbook then please submit a new Answer Forum request.
a) Create an “Add-in” and install it on your Excel application.
and/or
b) Save the UDF in the Personal Macro Workbook (which is a hidden system file that opens with the Excel application).
If you are interested in learning more about creating an “Add-in” and/or the Personal Macro Workbook then please submit a new Answer Forum request.
What’s the most useful MS Excel trick you know?
5/26/2019
An often underappreciated capability of Excel is that it can be programmed to communicate with other Microsoft applications and even Adobe PDF viewer using Object Linking and Embedding (OLE) Automation. For example, you could create Visual Basic macros in Excel which at the click of a button would allow you to export an Excel chart into a PowerPoint slide or export an Excel form into a PDF or Microsoft Word file.
Is it possible to link an Excel planner to a Google calendar?
5/23/2019
You can link an event in your Excel based calendar to your Google Calendar by inserting a hyperlink in Excel to the unique URL for the corresponding event in your Google Calendar. However it is not possible to dynamically "link" an entire Excel based calendar to Google Calendar so that event updates in Excel will automatically be reflected in your Google Calendar.
You can however create a simple Excel based process to export new calendar events to a CSV file which you can then manually upload to Google Calendar:
1) Insert a worksheet into your Excel planner with row headers which exactly match the Google Calendar fields listed here: https://support.google.com/calendar/answer/37118?hl=en.
2) Have the fields in the worksheet reference the relevant event fields from elsewhere in your Excel planner. For example if the new worksheet is ‘Sheet 2’ and the event data source is on ‘Sheet 1’ then map the cells in ‘Sheet 2’ to the corresponding cells in ‘Sheet 1’
3) Insert an ‘Active X’ button on the new worksheet which links to a macro which first saves this worksheet as a separate CSV file to your desktop and then opens the following hyperlink: https://calendar.google.com/calendar/r/settings/export
4) Execute the macro by clicking the ‘Active X’ button. Once the Google Calendar import dialog opens, select the new CSV file on your desktop and import that file to your Google Calendar.
You can however create a simple Excel based process to export new calendar events to a CSV file which you can then manually upload to Google Calendar:
1) Insert a worksheet into your Excel planner with row headers which exactly match the Google Calendar fields listed here: https://support.google.com/calendar/answer/37118?hl=en.
2) Have the fields in the worksheet reference the relevant event fields from elsewhere in your Excel planner. For example if the new worksheet is ‘Sheet 2’ and the event data source is on ‘Sheet 1’ then map the cells in ‘Sheet 2’ to the corresponding cells in ‘Sheet 1’
3) Insert an ‘Active X’ button on the new worksheet which links to a macro which first saves this worksheet as a separate CSV file to your desktop and then opens the following hyperlink: https://calendar.google.com/calendar/r/settings/export
4) Execute the macro by clicking the ‘Active X’ button. Once the Google Calendar import dialog opens, select the new CSV file on your desktop and import that file to your Google Calendar.
What function should I input in Excel to implement this command, “If a cell in one sheet contains one word, then print a range of cells from another sheet right under it”?
5/13/2019
There is no built-in Excel function which would accomplish this. However, you can insert an ‘Active X’ command button and a simple macro into your Excel workbook which, at the click of a button, would search for your keyword in 'Sheet 1' and then if the keyword is found, a command to print a range of cells in 'Sheet 2' would execute. In the VBA code below, the word being searched in 'Sheet 1' is “Keyword” and the print range in 'Sheet 2' is cells “A2:G20”. If the word you are searching for is expected to change over time, then I’d suggest editing the macro to have it either prompt you for the search word upon execution or have the macro reference a static cell in 'Sheet 1' where you can input your search word.
Sub Print_from_Keyword()
Dim FindWord As String,
Dim Found As Range
FindWord = "Keyword"
Set Found = Sheets("Sheet1").Cells.Find(What:=FindWord, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Found Is Nothing Then
Sheet("Sheet2").Select
Range("A2:G20").Select
ActiveSheet.PageSetup.PrintArea = "A2:G20"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _:=True
Else
MsgBox "The Keyword was not found."
End If
End Sub
Sub Print_from_Keyword()
Dim FindWord As String,
Dim Found As Range
FindWord = "Keyword"
Set Found = Sheets("Sheet1").Cells.Find(What:=FindWord, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Found Is Nothing Then
Sheet("Sheet2").Select
Range("A2:G20").Select
ActiveSheet.PageSetup.PrintArea = "A2:G20"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _:=True
Else
MsgBox "The Keyword was not found."
End If
End Sub
How can I move data from one sheet onto another at a certain time?
4/10/2019
This is an excellent question. I'd advise creating/ recording a simple macro which copies data from Sheet 1 to Sheet 2. Next you'll want to edit this macro and insert the "Application.OnTime" VBA application object. This will allow you to schedule the macro to automatically run at your specified time and date in the future.
If this is a procedure you will be performing regularly, I'd suggest having the macro prompt you for variable inputs such as the date and time you want to run the module next. Note that the Excel workbook with the embedded macro must be open at the scheduled execution time in order for the macro to run.
For details on the syntax of this object and how to use it, I'd suggest visiting: https://docs.microsoft.com/en-us/office/vba/api/excel.application.ontime
If this is a procedure you will be performing regularly, I'd suggest having the macro prompt you for variable inputs such as the date and time you want to run the module next. Note that the Excel workbook with the embedded macro must be open at the scheduled execution time in order for the macro to run.
For details on the syntax of this object and how to use it, I'd suggest visiting: https://docs.microsoft.com/en-us/office/vba/api/excel.application.ontime
What is the formula in Excel for a Zigzag indicator?
4/10/2019
The so-called Zigzag oscillator is the output of several variables and depends on what dataset you are using and your percentage of price movement tolerance. There is no one universal formula for this. I'd suggest creating a simple model to determine the support and resistance areas in your asset price dataset. Once you have done that and if you have specific questions feel free to submit a new Answer Forum request and I will respond.
What is the benefit of using Select Case over If-Then in Excel VBA?
3/19/2019
When coding in VBA, SELECT CASE and IF THEN provide the same result. However, SELECT CASE is a better choice if you have numerous Cases/ Conditions. It is cleaner and easier code to read as indented CASE statements as opposed to a litany of IF-THEN-ELSE IF statements. Alternatively, if you have a very large number of Conditions/ Cases, an ARRAY is a better coding choice.
|
Please consider supporting the Excel Savants Answer Forum by visiting our advertisers:
|
|