VBA access worksheet functions in Excel

Q201. In Excel, how can worksheet functions be accessed in VBA?

For example, I understand that the TEXT function I use in Worksheet to display a number in specific format is not available in VBA. Is there any way I can still use this function in VBA?

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub AccessWorksheetFunction()
  3. MsgBox Application.WorksheetFunction.Text("758", "00000")
  4. End Sub

Description:

a) Line 3 - Worksheet functions can be accessed from VBA by prefixing the actual function with "Application.WorksheetFunction."

Result after Macro execution:

excel vba access worksheet functions

 

You can find similar Excel Questions and Answer hereunder

1) vba copy rename worksheet in Excel

2) remove outline worksheet in Excel

3) Vba list all files in a folder in Excel

4) vba turn off alerts in Excel

5) vba shade alternative rows in Excel

6) vba sort columns in Excel

7) vba add shape to worksheet in Excel

8) add back ground image worksheet in Excel

9) Import txt file in Excel

10) find data validation cell in worksheet in Excel

 

Question


vba copy rename worksheet in Excel

Q168. In Excel, how can I copy and rename a WorkSheet using VBA?

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub CopyAndNameWorksheet()
  3. ActiveSheet.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
  4. ActiveSheet.Name = "Copied Sheet"
  5. End Sub

Description:

a) Line 3 - ThisWorkbook.Sheets.Count represents the last sheet. ActiveSheet is copied and moved to end of the workbook.

b) Line 4 - Since by default after copying sheet, the copied sheet is activated, ActiveSheet object could be used to rename the copied WorkSheet.

 

Question


remove outline worksheet in Excel

Q117. In Excel, how can I remove Outline from my WorkSheet?

For example, I have a WorkSheet that has "Outline" set up as shown below. I would like to remove the same.

excel remove outline worksheet

To do it in Excel, here is the answer:

a) Click on "Ungroup" under "Data". Click on "Clear Outline".

excel excel remove outline worksheet

b) Outline is cleared and the Table is shown in its original form without any rows hidden.

excel excel excel remove outline worksheet

 

Question


Vba list all files in a folder in Excel

I want to run a macro to list the names of all files in a folder in a worksheet. I want the file names along with extensions. Please help

Answer:

Use the VBA commande Application.Filesearch. See some good examples in the following link.

(for formulas, depending on your country, you might have to change ; with , or the opposite

 Other excel answers

 

 

Question


vba turn off alerts in Excel

Q197. In Excel, how can I turn off Alerts using VBA?

For example, I have written a macro to automate a set of routine tasks so that no user intervention is required. However, since my automation involves deletion of WorkSheets (so, whenever code is executed to delete

WorkSheet, pop up message "Data may exist in sheet selectedů..") as well as file overwriting (pop up message confirming if file can be overwritten), user intervention is required. Is there a way to suppress these messages?

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub SubRoutineSample()
  3. Application.DisplayAlerts = False
  4. ' complex
  5. ' macro
  6. ' function
  7. ' goes here
  8. Application.DisplayAlerts = True
  9. End Sub

Description:

a) Line 3 - Turn OFF Display Alerts when macro is running. This suppresses pop ups. This can be placed at the beginning of code or just before actions that induce pop up messages like WorkSheet deletion, file save etc.,

c) Line 8 - Turn ON Display Alerts after macro is executed.

 

Question


vba shade alternative rows in Excel

Q171. In Excel, how can I shade alternate rows in color using VBA to make it easier to read voluminous data running into hundreds of rows?

For example, in the table below, I would like to shade alternate rows.

excel vba shade alternative rows

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub ShadeAlternateRows()
  3. Dim i As Integer
  4. ''
  5. For i = 5 To 12
  6. If i Mod 2 = 0 Then
  7. ActiveSheet.Range(ActiveSheet.Cells(i, 2), ActiveSheet.Cells(i, 8)).Select
  8. With Selection.Interior
  9. .Pattern = xlSolid
  10. .PatternColorIndex = xlAutomatic
  11. .ThemeColor = xlThemeColorAccent6
  12. .TintAndShade = 0.799981688894314
  13. .PatternTintAndShade = 0
  14. End With
  15. End If
  16. Next i
  17. End Sub

Description:

a) The code loops through the table range. Line 6 "i Mod 2 =0" is the criteria to apply formatting - basically even numbered rows have shading. When this criteria is met, the cell range is shaded.

 

Question


vba sort columns in Excel

Q158. In Excel, how can I sort data using VBA?

For example, I have Sales Data for Q1-2016 for different Salespersons as shown below. I would like to sort them in decreasing order of Sales using VBA.

excel vba sort columns

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub SortData()
  3. ActiveSheet.Sort.SortFields.Clear
  4. ActiveSheet.Sort.SortFields.Add Key:=Range("CY5:CY14"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  5. With ActiveSheet.Sort
  6. .SetRange Range("CV4:CY14")
  7. .Header = xlYes
  8. .MatchCase = False
  9. .Orientation = xlTopToBottom
  10. .SortMethod = xlPinYin
  11. .Apply
  12. End With
  13. End Sub

Description:

a) Line 3 - Clear any existing sorting in ActiveSheet.

b) Line 4 - Define the Sort field (Sales value in Column CY) and Sort Order.

c) Line 6 - Define the Data range that needs to be sorted.

d) Line 7 - Since the Data range selected includes header, set ".Header" property to xlYes.

Result after Macro execution:

excel excel vba sort columns

 

Question


vba add shape to worksheet in Excel

Q188. In Excel, how do I add a shape using VBA?

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub AddShapes()
  3. ''
  4. 'Add a Rectangle.
  5. ActiveSheet.Shapes.AddShape msoShapeRectangle, 20, 20, 50, 50
  6. ''
  7. 'Add a Rounded Rectangle.
  8. ActiveSheet.Shapes.AddShape msoShapeRoundedRectangle, 20, 120, 50, 50
  9. ''
  10. 'Add a 4-point star.
  11. ActiveSheet.Shapes.AddShape msoShape4pointStar, 20, 220, 50, 50
  12. ''
  13. End Sub

Description:

a) Line 5, 8, 11 - the 4 numbered arguments after the Shape type are as follows -

1) Position of Shape from Left Edge of the WorkSheet

2) Position of Shape from Top Edge of the WorkSheet

3) Shape Width

4) Shape Height

b) For getting supported Shape Types, consult MSDN, potential source -

Result after Macro execution:

excel vba add shape to worksheet

 

Question


add back ground image worksheet in Excel

Q6. In Excel how can I add a background image to a worksheet?

For example, if in Excel, you want to create a "Web" or "App" feel for your worksheet, an image can be set as background.

This is very useful if you want to differentiate between different Excel pages that look similar in the content but actual relate to different subjects. Changing the background in Excel is not used enough.

To do it in Excel, here is the answer:

To add a background image to an Excel worksheet,

a) Click on "Background" option in "Page Setup" section in "Page Layout" tab.

excel add back ground image worksheet

b) Select the image (to be set as background) and then click "Insert".

excel excel add back ground image worksheet

c) Excel tiles the selected image across the worksheet as shown in Screenshot below.

excel excel excel add back ground image worksheet

 

Question


Import txt file in Excel

Hi
I am new to macro ad want to import a text file into excel

1. Text file = test.txt
2. Want to import text file to a certain worksheet (sheet1) start at row 0 and column A
3. All data from txt file must be imported.

Answer

Go to the Data Menu and Import text file

Importing text file into excel sheet

(for formulas, depending on your country, you might have to change ; with , or the opposite

Other excel answers

 

 

Question


find data validation cell in worksheet in Excel

Q103. In Excel, how can I identify all cells with Data Validation in my WorkSheet?

Note: This would be very helpful if you have inherited a complex WorkSheet and want to understand how the WorkSheet has been set up.

For example, in the table below, I would like to quickly identify the cells that have Data Validation.

excel find data validation cell in worksheet

To do it in Excel, here is the answer:

a) Press F5 and in the "Go To" dialog that pops up, click on "Special" (Alternatively, click on "Find & Select" under "Home". Click on "Go To Special").

excel excel find data validation cell in worksheet

b) In the "Go To Special" dialog box, click on "Data validation", click OK.

excel excel excel find data validation cell in worksheet

c) All the cells with "Data validation" are selected / highlighted as shown below.

excel excel excel excel find data validation cell in worksheet