dhanush's Profile

1688
Points

Questions
21

Answers
73

• Asked on January 11, 2019 in

Try this alternative solution :

```=CONCATENATE(
DEC2HEX(RANDBETWEEN(0;4294967295);8);"-";
DEC2HEX(RANDBETWEEN(0;42949);4);"-";
DEC2HEX(RANDBETWEEN(0;42949);4);"-";
DEC2HEX(RANDBETWEEN(0;42949);4);"-";
DEC2HEX(RANDBETWEEN(0;4294967295);8);
DEC2HEX(RANDBETWEEN(0;42949);4)
)
```
• 1466 views
• 4 answers
• 0 votes
• Asked on January 11, 2019 in

Use this following procedure On Excel 2010 :

1. Choose the cell and want to check in case that is used at some place in a formula.
2. On a Formula Auditing menu, go to Formulas -> Trace Dependents.
• 429 views
• 2 answers
• 0 votes
• Asked on January 11, 2019 in

Try this best solution :

consider the sheet name is “stock”, and consider infront of the cell.

```=COUNTIFS(stock!A:A,"M",stock!C:C,"Yes")
```
• 431 views
• 2 answers
• 0 votes
• Asked on January 11, 2019 in

This solution will explain about relative instead of Absolute paths in Excel VBA

```Workbooks.Open FileName:= ThisWorkbook.Path & "\TRICATEndurance Summary.html"
```
• 1007 views
• 3 answers
• 0 votes
• Asked on January 11, 2019 in

This command will explain about quickest way to clear all sheet contents VBA

```Sub clear_sht
Dim sht As Worksheet
Set sht = Worksheets(GENERATOR_SHT_NAME)
col_cnt = sht.UsedRange.Columns.count
If col_cnt = 0 Then
col_cnt = 1
End If

sht.Range(sht.Cells(1, 1), sht.Cells(sht.UsedRange.Rows.count, col_cnt)).Clear
End Sub
```
• 671 views
• 3 answers
• 0 votes
• Asked on January 11, 2019 in

The best solution is here :

```Option Explicit

//***** User defined type
Public Type MyType
MyInt As Integer
MyString As String
MyDoubleArr(2) As Double
End Type

//***** Testing MyType as single variable
Public Sub MyFirstSub()
Dim MyVar As MyType

MyVar.MyInt = 2
MyVar.MyString = "cool"
MyVar.MyDoubleArr(0) = 1
MyVar.MyDoubleArr(1) = 2
MyVar.MyDoubleArr(2) = 3

Debug.Print "MyVar: " & MyVar.MyInt & " " & MyVar.MyString & " " & MyVar.MyDoubleArr(0) & " " & MyVar.MyDoubleArr(1) & " " & MyVar.MyDoubleArr(2)
End Sub

//***** Testing MyType as an array
Public Sub MySecondSub()
Dim MyArr(2) As MyType
Dim i As Integer

MyArr(0).MyInt = 31
MyArr(0).MyString = "VBA"
MyArr(0).MyDoubleArr(0) = 1
MyArr(0).MyDoubleArr(1) = 2
MyArr(0).MyDoubleArr(2) = 3
MyArr(1).MyInt = 32
MyArr(1).MyString = "is"
MyArr(1).MyDoubleArr(0) = 11
MyArr(1).MyDoubleArr(1) = 22
MyArr(1).MyDoubleArr(2) = 33
MyArr(2).MyInt = 33
MyArr(2).MyString = "cool"
MyArr(2).MyDoubleArr(0) = 111
MyArr(2).MyDoubleArr(1) = 222
MyArr(2).MyDoubleArr(2) = 333

For i = LBound(MyArr) To UBound(MyArr)
Debug.Print "MyArr: " & MyArr(i).MyString & " " & MyArr(i).MyInt & " " & MyArr(i).MyDoubleArr(0) & " " & MyArr(i).MyDoubleArr(1) & " " & MyArr(i).MyDoubleArr(2)
Next
End Sub
```
• 486 views
• 2 answers
• 0 votes
• Asked on January 11, 2019 in

Use this alternative method :

In case to keep at utilizing a stream for example. Response.OutputStrea, By using the SaveAs(Stream OutputStream) method, so now can make an Excel Package along with an null constructor.

• 1254 views
• 3 answers
• 0 votes
• Asked on January 11, 2019 in

Try this alternative solution :

For the range of interest, by creating a unseen worksheet to keep the previous values .

```Private Sub Workbook_Open()

Dim hiddenSheet As Worksheet

Set hiddenSheet = Me.Worksheets.Add
hiddenSheet.Visible = xlSheetVeryHidden
hiddenSheet.Name = "HiddenSheet"

//Change Sheet1 to whatever sheet you're working with
Sheet1.UsedRange.Copy ThisWorkbook.Worksheets("HiddenSheet").Range(Sheet1.UsedRange.Address)
End Sub
```

After workbook is close, Delete it

```Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.DisplayAlerts = False
Me.Worksheets("HiddenSheet").Delete
Application.DisplayAlerts = True
End Sub
```

Can also changing Worksheet_Change event

```For Each cell In Target

If Not (Intersect(cell, Range("cell_of_interest")) Is Nothing) Then
new_value = cell.Value
// here's your "old" value...
old_value = ThisWorkbook.Worksheets("HiddenSheet").Range(cell.Address).Value
Call DoFoo(old_value, new_value)
End If

Next cell

// Update your "old" values...
ThisWorkbook.Worksheets("HiddenSheet").UsedRange.Clear
Me.UsedRange.Copy ThisWorkbook.Worksheets("HiddenSheet").Range(Me.UsedRange.Address)
```
• 683 views
• 3 answers
• 0 votes
• Asked on January 11, 2019 in

The following command is to get the whole row while ExcelRange after it can be either iterated or for LINQ:

```for (var rowNum = 1; rowNum <= sheet.Dimension.End.Row; rowNum++)
{
var row = sheet.Cells[string.Format("{0}:{0}", rowNum)];
// just an example, you want to know if all cells of this row are empty
bool allEmpty = row.All(c => string.IsNullOrWhiteSpace(c.Text));
if (allEmpty) continue; // skip this row
// ...
}
```
• 991 views
• 3 answers
• 0 votes
• Asked on January 11, 2019 in

UserForm to the VBA project, by using the Non Win32 otherwise by choice add a reference to Microsoft Forms 2 Object Library, now can simplify from sheet or module

```With New MSForms.DataObject
.SetText "http://zombo.com"
.PutInClipboard
End With
```

• 663 views
• 3 answers
• 0 votes